MS SQL Technology

9. Data Extract

Data Extract allows you to retrieve data from the Avercast database using SQL query language instructions. Knowledge of the table and field name conventions of Avercast and the syntax of SQL are necessary to build the appropriate queries.  Your Avercast representative can assist you in building queries to respond to your requirements.

To open the Data Extract window, select Data Extract from the Reports menu.  The window contains three areas.  The top portion willdisplay results when a query is executed, the middle portion displays the actual SQL Query commands, and the bottom portion contains several buttons to control the execution of the extract.

When query statements have been entered or modified, this option allows you to save the query for future use. To facilitate the retrieval of queries, it is strongly recommended that one query folder be created in which all queries are stored.

Used to find a previously stored query and place it in the command area.

Executes the query commands and display the results at the top of the window.

Deletes all query commands from the command area.

Export the results of the query to an Excel spreadsheet or a PDF or Word document.

Close the Data Extract window. 

IMPORTANT:  It is possible to include query commands which will update fields in the database.  It is STRONGLY recommended that these commands NOT be used in Data Extract, because doing so will bypass the data validations which are necessary to maintain the integrity of the database.  If any kind of update is being performed using this function, a backup of the database MUST be done before the query is executed.

9.1 Enable standard filter fields

If you construct a query with the special keyword “%filter%”,the data extract will allow you to use the standard avercast filters to search your data.

9.2 Report Automation

The data extract program contains several command line input options that allow reports to be generated automatically. A batch file can be set up in the windows task scheduler to either save these reports to a location on the local system or network, or to email the report. Key parameters include/QueryName to specify the query file to load, /SaveTo to specify the save location, Email To to specify an address to email the report to, and/Create If Empty to specify whether or not to generate the report when the resultset is empty.

For example, the following in a batch file would save the report as an excel file every time it ran:

"%AVERCAST_INSTALL%\DataExtract.exe" <user> <datasource>/QueryName='C:\Avercast Queries\my report.sql' /Save To='C:\reports\my report'/FileType=xlsx /autorun

Copyright ©2019 Avercast, LLC. All Rights Reserved. Office:208-538-5380 | Fax:208-745-7456 | Email: info@avercast.com