Advanced Search window showing an example query run
Reports Menu Item -> Queries -> Advanced Search
The Advanced Search feature allows custom reports to be created from any data in the database. The queries are written in Entity SQL, which is similar to the 'standard' SQL. Entity SQL queries are outside the scope of this document, and are not covered by the patriot support assurance policy but there are many books and online references available on how to write them.
Entity SQL Queries can be created from Reports -> Queries -> Stored Queries, or directly from the query selector in the Advanced Search screen. Example queries are also available.
Once the query has been written, click run to show a preview of the output. This is a standard report, and can be saved, printed, or exported as required.
The advanced search window contains a default report output format, which shows the column headers and data from the query. The report format can be modified as required by selecting a new output format. Select Customise Output Style and choose the required output format. Only generic reports can be selected. See the Generic Reporting document for more information on how to create new generic reports.
To assist in writing ESQ that queries the clients (memalarm) table, the graphical tool Advanced Client Search can be used. To access this, open the client search menu and select the advanced search arrow next to the search input box.
From here, conditions can be added to build a query. First, select the field or property of the client that you want to test. Criteria have different options for how the value must match. For example, text fields can be set to 'Contains' which will match if the entered value is contained anywhere in the field on the client. It is also possible to test for greater or smaller values for numeric fields such as warranty months, or invert a condition by selecting the 'Not' checkbox. Once all conditions have been chosen, click the search button to run the query and show results. Clicking on a client in the result list will open the client for viewing or editing. Clients must meet all criteria before being shown, so queries with more criteria will filter more clients out of the results.
After creating a query using the advanced client search window, it is possible to save it to re-run later, or for use as a generic report. First, select the Export / Convert to Stored Query button. This will open the stored queries window where you can name and save the new query. Queries saved using this method can be re-run using the Stored Query selector on the advanced client search page, but cannot be manually edited or modified. Other than the read-only status, these queries can be used in any place a stored query is required, e.g. standard advanced search or generic reporting.
Reports Menu Item -> Queries -> Advanced Search 52
The Legacy Advanced Search feature allows operators to filter records in the Database Search window by using standard SQL queries. The results of the SQL query are then displayed in the bottom pane. SQL queries are beyond the scope of this manual, and are not covered by the patriot support assurance policy but there are many books and online references available on how to write SQL queries. Generally this window will be used to report on an unusual set of data which a standard report is not capable of.
Enter the SQL query in the top pane, click run query to view the results in the bottom pane.
To help build a new legacy client query:
To assist in writing a SQL query which queries the client table (memalarm), click on the Build Query button to build a new query. The Enter Search Criteria window will appear.
The Enter Search Criteria window provides a graphical “user-friendly” way of creating SQL queries. This window allows the operator to search particular fields of a client record by entering in criteria and clicking the Save button.
For example, to create a list of clients located in a particular city, simply select the city from the City drop-down box then click the Save button. This will then create and run the following SQL query:
Where IDCITIES = 6 references the city chosen from the drop-down box.
The results of the query are then shown in the bottom pane. To view a client’s record from this window, simply highlight the client and click the View Selected Client button.
By default the query will return the first 50 matching records. To alter this value, change the number of records to be returned in the Return First … Records field.
If the query contains a ClientNo or Client_No field, the View Selected Client button will become enabled, allowing quick access to the highlighted client’s details. Changes cannot be made to the client from here.
Clicking on the Print Search Results button prints the result of the query. The Report Preview window will appear allowing a preview of the report content before printing.
Once a query has been built, it can be saved by clicking on the Save Query button to the right of the Stored Queries drop-down box. An input box will appear asking you to enter a descriptive name for the query.
Type a suitable name in the Description field then click the OK button. A message box will appear reporting that the Query has been stored.
Once saved, the query is added to the Stored Queries drop-down box.
Note: You must close and then reopen the Enter Search Criteria window before you can select and run a newly saved query.
Once a query has been saved it can be run simply by clicking on the Stored Queries drop-down box and selected the required query using the left mouse button. The chosen SQL query will be displayed in the top pane, with the query results automatically displayed in the bottom pane.
To edit or delete a saved query click on the Edit saved Queries button. The Browse Stored Queries window will appear.
To edit a saved query simply highlight the stored query you wish to edit then click the Change button. The Changing a Queries Record window will appear. Make the required changes then click Save.
To delete a saved query, highlight the stored query you wish to delete then click the Delete button. A window will appear asking “Are you sure you wish to delete the highlighted record”. Click Yes to confirm, or click No to cancel.
The View button opens the Selected Query window displaying the content of the highlighted query.