SQL Query Reports
Copyright © 2019 EarthSoft, Inc • Modified: 04 Mar 2019
The EQuIS API provides clients the ability to create custom reports as .NET classes. .NET class reports provide the most flexibility in computation and output (e.g. producing pre-formatted Excel workbooks). However, in many cases, you may just want to export some data as a table for further processing. As an alternative to writing a .NET class, you can (as of v6.3) publish a SQL query as a custom report. In order to publish the SQL query as a report, follow the instructions below.
1. Compose/test the query in a SQL Editor (e.g. SQL Server Management Studio or SQL Developer). The EQuIS SQL Form can be used to compose the query.
2. Save the query as a *.sql file somewhere on your local machine.
3. Use the Report Publisher to publish the *.sql file as a report. The report will show up as a "class" report (EQuIS uses the EarthSoft.Common.Reports.SqlQueryReport class to run the report). The related ST_MODULE.VERSION_NUMBER will be the EQuIS version, followed by the Modified date of the .sql file in the form of a two-digit year, followed by the day number (e.g., 22.214.171.12433).
Points to consider when composing a query to be used as a report include:
•Only database-level security (DLS) users or application-level security (ALS) administrators may publish reports. ALS users who are not administrators may not publish a report.
•The SQL query must use the appropriate syntax for the database you are using (Microsoft SQL Server). Much of the ANSI SQL Standard is supported, but some syntax is database-specific.
•The SQL query must be "safe", which means that it may only contain SELECT statements. It may not contain INSERT, UPDATE, DELETE, DROP, etc. If the query is unsafe, it will not execute when the report runs.
•Parameters in your query can be defined (i.e. "... sys_loc_code = @sys_loc_code ..."). The Report Publisher will automatically recognize parameters.
oCertain aspects of parameter metadata can be specified (i.e. data_source, visibility, etc.) within the Report Publisher or in the ST_REPORT_PARAMETER table (after the report is published).
oThe following parameters will be recognized and default metadata will be provided:
•When running the published report in EQuIS Professional, the parameter will not be shown to the user, but will be automatically set to the currently selected facility.
•When running the published report in EQuIS Enterprise, the parameter will be shown and the user will be prompted to select from the facilities that they have permission to.
▪@user_id (will be automatically set to the current user)
▪@start_date (will be automatically set as a date chooser with a default date of one year ago today)
▪@end_date (will be automatically set as a date chooser with a default date of today)
▪@cas_rn (will be automatically set as a drop-down showing CAS_RN and CHEMICAL_NAME from RT_ANALYTE
▪@sys_loc_code (will be automatically set as a drop-down showing SYS_LOC_CODE and LOC_NAME from DT_LOCATION)
Warning: *EQuIS will not automatically add a @facility_id parameter to your query. If you choose to write and publish a query that does not include a @facility_id parameter, your users may end up seeing data from facilities that they do not have permissions to. Always include a @facility_id parameter, unless you specifically do not want it.
•SQL query reports may be used like any other report (in Professional, in Enterprise, as an EIA, etc.)