A new reporting tool has been added to the system after release 1.00.17.052
It allows users to write SQL queries and return results from the database. These results can then be exported to MS excel for further analysis or manipulation.

 

Contents (Click an Item to Jump to that section)

How it works

Toolbar Icons

Creating GROUPS from a Query

EXAMPLES

1. List Terms Value

2. List Candidates linked to Matches

3. List Candidates inc. Names/Address (in London)

4. Example linking two table using DISTINCT

5. Count Candidates by Enq. Source

6. Count Candidates by Enq Source and looking Enq Source Description

Troubleshooting

How it Works

The SQL workbench can be found in the [Admin] section of the system.
Upon first selecting the workbench, for security, users will be prompted for the 'ADM' user password (unless they are already logged in as the ADM User).
Having entered the password correctly, the user will be allowed to continue to use this workbench whilst they remain in the option. Navigating away and then returning to the workbench will require the password to be re-entered.

Having opened the workbench users can choose a Table using the drop-down and then choose columns to display using the Column: drop-down.

If known, users can write an SQL query directly into the SQL: box or use the drop-down boxes to choose the relevant item(s) and then press the [SQL]  button to insert the chosen item into the SQL query.

Once run the results of the query will be displayed on-screen in the chosen format (Grid/JSON) and can be exported to MS Excel using the Excel icon in the toolbar. Columns relating to Unique_IDs for records, such as Candidates, Vacancies, Sites or Companies will be indicated with the
Navigator

and clicking on the field will drill-in and open the relevant record in the database.

 

Table	This drop-down allows users to select the data table they wish to report from. Having selected a table pressing the [SQL] button to the right will insert the chosen field into the SQL query box, using the syntax  Select * FROM xxxxxxxx   (where xxxxxxxx is the name of the chosen table.)
Column	This drop-down allows users to select fields from the selected table. Then can be inserted into the SQL query box by pressing the [SQL] button to the right. Repeated pressing of the button will insert sequential fields into the SQL query one-after-another.
[SQL]	The small SQL button next to each of the above drop-downs inserts the chosen item (Table or Column) into the SQL Query box.
[Paste]	This small button allows users to copy the chosen item (Table/Column) into the windows pasting buffer which can then be pasted put into 3rd-party applications.
Name	This is used to specify the name for your query. Once a name has been specified it is possible to save the query for future use with the SAVE icon at the top of the screen.
SQL	This is where you should write your SQL query. The query can contain all of the usual SQL syntax such as WHERE, SELECT, ORDER BY, LIKE. NB: It is not possible to use the SQL query tool to create, add, modify or amend records within the database - it is a "Read only" tool.
[Run]	This button (bottom left) will run the query. It is also possible to run the query using the Play button in the toolbar.
[  ]Max	This tick box will display all records on screen without applying the normal (1000 record) display limit.
( ) Grid	This will display the results of the SQL Query in a grid (i.e. on-screen) which can then be exported to MS Excel.
( ) JSON	This will display the results of the SQL query in JSON (a standard data format used by many web based systems )

 

 Back To Top

Toolbar Icons

The icons in the toolbar are mainly used for query management and will display their function with a hover-tip when the mouse pointer is left over the icon. In order (from left to right) the icons are:-

ADD	Add/Start a New Query
OPEN	Open a saved query (A list of queries will be displayed and can be chosen)
SAVE	Save the Current query using the name entered in the NAME: field on-screen. Note:  If you change the name of the query and press [Save] it will be re-saved with the new name, and does NOT create a new query
DELETE

This will delete the current query from the saved query list.

PLAY  This will RUN the currently open query.

EXCEL  This will export the current on-screen query information into MS Excel (Users will be prompted for the Excel export password)

HELP  The usual Influence help, with access to the support pages and Mikogo Screen sharing tool.

Example showing the OPEN box with a choice of saved queries.

Back To Top

Creating Groups from Query

From release version 1.00.18.186 or later, it is possible to create a GROUP based upon the results of an SQL Query.  (See below)

NB: In order for this to work the output from the Query must contain the unique ID of the main object as the first column of the query results. For example,  if you have written a query to extract a list of candidates based upon certain criteria then the output, then the first column of the output must  be Candidate Unique ID in order to allow you to create a GROUP of candidates.

You can find out more about GROUPS from HERE.

Back To Top

EXAMPLES

Below are some examples of SQL queries to give users an idea of what is possible. When reporting from two (or more) tables, the format is;

Select Field1, Field2, Field3, Field4, from Table1,Table2, 
where table1.field_X=table2.field_Y and  field_z

Example1:
This will display a list of companies and the "Terms" Value, sorted into order of Terms Value

SELECT COMP_UNIQ_ID, COMP_AC_NAME, COMP_TERMS_TYPE, COMP_TERMS_VAL, COMP_AC_NAME 
FROM COMPTAB0001 
WHERE COMP_AC_NAME LIKE'BIG%' ORDER BY COMP_TERMS_VAL

Back To Top

Example2:
This will display a list of Match record ID's and links to the Candidate, then shows their name.

SELECT MATSTAB0001.MATS_UNIQ_ID, MATSTAB0001.MATS_SITE_UNIQ, MATSTAB0001.MATS_CAND_UNIQ, CANDTAB0001.CAND_UNIQ_ID, CAND_FORENAME, CAND_SURNAME 
FROM MATSTAB0001, CANDTAB0001 
WHERE MATSTAB0001.MATS_CAND_UNIQ=CANDTAB0001.CAND_UNIQ_ID

 

Back To Top

Example3:
This will display a list of Candidates and their name/address where their 'Geographic restriction' is set to 'London'

SELECT CAND_FORENAME, CAND_SURNAME, CAND_UNIQ_ID, CAND_EMAIL, CAND_GEOG_REST, CAND_ADDR_HSE_NO, CAND_ADDR_LINE_1, CAND_ADDR_LINE_2, CAND_ADDR_LINE_3, CAND_ADDR_LINE_4, CAND_ADDR_LINE_5, CAND_POSTCODE FROM CANDTAB0001 
WHERE CAND_GEOG_REST ='London'

Back To Top

Example4:
This will display a list of Compliance codes which are currently in use. It shows the use of the DISTINCT qualifier and also joining two tables. The REQINFO0001 table is linked to the REQDEF0001 table using the 'Requirement code' field from each table

SELECT DISTINCT RQIN_REQ_CODE, RQDF_DESC 
FROM REQINFO0001, REQDEF0001 
where RQDF_REQ_CODE = RQIN_REQ_CODE

Back To Top

Example5:
This will display a count of the number of candidates for each Enquiry source. Notice the use of the as 'Qty' to label this column in the output.

SELECT CAND_ENQ_SRC , COUNT(CAND_UNIQ_ID) as 'Qty' 
FROM CANDTAB0001
GROUP BY CAND_ENQ_SRC

Back To Top

Example6:
This will display count of the number of candidates for each Enquiry source, and lookup the Description of the Enq. Source from the CODX table.

SELECT CAND_ENQ_SRC, CODX_DESC , COUNT(CAND_UNIQ_ID) as'Qty' FROM CODXTAB0001, ENIDTAB0001
Where CODX_TAB_NO='913' and CODXTAB0001.CODX_CODE=ENIDTAB0001.CAND_ENQ_SRC
GROUP BY CAND_ENQ_SRC

Back To Top

Troubleshooting/Errors

If things don't work as expected or you have problems, we have listed some common issue below:

a) In incorrectly written Query using SUM can cause the system to crash. (After version 1.00.20.160 the system will display an error message as below)

Set_and_non-set_Mixture

This  query  will not work as you need to use GROUP BY  when using SUM:
SELECT SUM(TIME_INV_AMT) FROM TIMESHT0001       <<<<  This will not work

What  you want is a query more like this:  
SELECT TIME_TS_NO, SUM(TIME_INV_AMT) as'InvAmount' FROM TIMESHT0001
GROUP BY TIME_TS_NO

Back To Top