SQL Query Reports

The EQuIS API provides clients the ability to create custom reports as .NET classes; these 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 data without concern for formatting. As an alternative to writing a .NET class, you can publish a SQL query as a custom report. SQL query reports may be used like any other report (in Professional, in Enterprise, as an EIA, etc.)

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) or the EQuIS SQL Form.

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. 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.

4.Check the parameters for the SQL report in ST_REPORT_PARAMETER after publishing. See Customize Report Parameters for more details.

The report will show up as a "grid" report in the EQuIS Professional Reports user interface, and lists a DLL (EarthSoft.Common.Reports.SqlQueryReport) used 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. 7.21.1.21100). To republish a SQL report on the same day, see Handling Multiple Copies of Same Report.

Points to consider when composing a query to be used as a report are detailed below.

Syntax

•The SQL query must use appropriate syntax for SQL Server. Much of the ANSI SQL Standard is supported, but some syntax is specific to the server version or database.

•The SQL query must be "safe", which means that it cannot alter any of the current database contents. Further information:

oSELECT statements are safe.

oThe query may not contain INSERT, CREATE, UPDATE, DELETE, DROP, ALTER, or similar statements pertaining to a database table. These commands (with the exception of ALTER, which may continue to be considered unsafe) can be applied to a temporary table set up in the query, e.g. “INSERT INTO #TEMPTABLE”, or “DROP Table #TEMPTABLE”.

oEXEC, EXECUTE, or TRUNCATE statements are not permitted.

oIf the query is unsafe, an “Unsafe SQL” error message would be returned when the report runs, and the code will not execute.

•Prior to build 7.22.2, remove all inline comments, e.g. "-- comment text", within a SQL statement, as VB.NET may interpret this as part of the SQL or treat everything after the "--" as a comment (even text on the following line that is intended to be SQL). For best results, consult the formatting rules for the EQuIS SQL Form.

•Remove variable declarations, such as the following, prior to publishing:
declare @location_group as varchar(80) =’Borehole Logs’

Parameters

•Parameters in your query can be defined (i.e. "... sys_loc_code = @sys_loc_code ..."). The Report Publisher will automatically recognize parameters. Special characters such as colons, “:”, may also be detected as parameters; these can be unchecked when publishing, deleted from ST_REPORT_PARAMETER after publishing, or simply ignored.

•Certain 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).

•If the report uses the @ebatch parameter, as required for EQuIS Information Agents, define an additional parameter for SQL Reports to function. When a user runs a report from EQuIS Professional, the program does not inherently know about an ebatch parameter (it is not a Known Parameter). To address that behavior, it sets the value to -1 as a default, then looks for data rows where ebatch = -1. The report then returns 0 results. For the ebatch parameter to function, follow the instructions below so that the ebatch value is not overwritten:

1.In the SQL query, define two separate parameters - one called "@ebatch" and the other called something else (e.g. "@e_batch") - with the following relationship:

... where s.ebatch = case when @ebatch is not null and @ebatch > 0 then @ebatch else @e_batch end

2.After publishing the report, set the @ebatch parameter to be hidden (ST_REPORT_PARAMETER.VISIBILITY = 'N'), so the user only sees the @e_batch parameter.

Known Parameters

The following parameters will automatically have the DATA_SOURCE and DEFAULT_VALUE populated within the Report Publisher when publishing a SQL report:

•@facility_id*

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.

oWhen 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.

oWhen 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 multi-select option showing CAS_RN and CHEMICAL_NAME from RT_ANALYTE

•@sys_loc_code (will be automatically set as a multi-select option showing SYS_LOC_CODE and LOC_NAME from DT_LOCATION)

You can review alternate DATA_SOURCEs for the above report parameters (such as a @start_date that automatically uses the first date in a certain table) by searching for the parameter in ST_REPORT_PARAMETER.PARAM_NAME.

Removal

To remove old or unwanted SQL reports that have been published to the database, the entry for the report can be deleted from ST_REPORT, followed by any entries corresponding to the report from ST_MODULE. Be aware that this will remove any user reports associated with the report, as well. Alternatively, these reports can be marked inactive with a STATUS_FLAG = 'R' in ST_REPORT to hide them from the Reports list. STATUS_FLAG = 'A' would be required to republish a report of the same name.

If the report is being removed to be able to republish it on the same day, see additional approaches on the Handling Multiple Copies of Same Report page.