<< Click to Display Table of Contents >> Navigation:  Professional > Reports > SQL Query Reports > Create Custom SQL Query Reports as .NET Classes

The EQuIS API provides 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 publish a SQL query as a custom report by following these instructions.

 

1.Compose and test the query in a SQL editor (e.g. SQL Server Management Studio or SQL Developer). Compose the query using the EQuIS SQL Form.

2.Save the query as a *.sql file on your local machine.

3.Publish the *.sql file as a report using the Report Publisher. The report will show up as a class report (EQuIS uses the EarthSoft.Common.Reports.SqlQueryReport class to run the report).

 

 

Here are additional points to consider when composing a query to be used as an EQuIS 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.

•The SQL query must use the appropriate syntax for the database you are using (Microsoft SQL Server).

•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 be executed when the report runs.

•Define parameters in your query (e.g. " ... sys_loc_code = @sys_loc_code ... "). The Report Publisher will automatically recognize parameters

oYou may specify parameter metadata (e.g. display type. data source, 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.

▪@facility_id**

•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 he or she has permission to.
**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 end users may end up seeing data from facilities they do not have permission to. You should always include a @facility_id parameter, unless you specifically do not want it.

▪@user_id (automatically set to the current user)

▪@start_date (automatically set as a date chooser with a default date of one year ago today)

▪@end_date (automatically set as a date chooser with a default date of today)

▪@cas_rn (automatically set as a drop-down showing CAS_RN and CHEMICAL_NAME from RT_ANALYTE

▪@sys_loc_code (automatically set as a drop-down showing SYS_LOC_CODE and LOC_NAME from DT_LOCATION)

•SQL query reports may be used like any other report (in Professional, in Enterprise, as an EIA, etc.)