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)
TABLE OF CONTENTS
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 smallSQLbutton next to each of the above drop-downs inserts the chosen item (Table or Column) into theSQL 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 thenamefor your query. Once anamehas been specified it is possible to save the query forfutureuse with theSAVEicon 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.
| |
| [Run] | This button (bottom left) will run thequery. 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 ) |
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/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.
| |
| 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
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.
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: List Terms (Days) for Clients
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
Example2: List Candidates Linked to Matches
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
Example3: List Candidates when Area Restrict is London
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'
Example4: List Compliance Items
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. DISTINCT ensures that duplicate rows are eliminated from the result set.
SELECT DISTINCT RQIN_REQ_CODE, RQDF_DESC
FROM REQINFO0001, REQDEF0001
where RQDF_REQ_CODE = RQIN_REQ_CODE
Example5: Count Candidatess by Enquiry Source
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
Example6: Count Candidates by Enq. Source and lookup Description
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
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
b) There are No Tables listed in the SQL Query Tool
It is caused because the Catalogue file is empty. To fix theissue just go into Maintenance >Setup > Table Maintenance - Once done, log OUT/IN and try again - the catalogue should have been recreated and the tables should be available.
Attachments (10)
Was this article helpful?
That’s Great!
Thank you for your feedback
Sorry! We couldn't be helpful
Thank you for your feedback
Feedback sent
We appreciate your effort and will try to fix the article