Add Parameter Selection
Copyright © 2018 EarthSoft, Inc • Modified: 23 Nov 2018
This article demonstrates how to add a parameter to a report, and filter a report using a specific input parameter. While these instructions apply to the Analytical Results II* Report, they may also be applicable to other reports.
1.To filter by an additional field (i.e. include in parameters) in the Analytical Results II Report, make sure the report is published to the database (this will not work with the Analytical Results (Extra Fields) Report.)
•Open the report window and right-click in the white-space.
•Select Import Report(s) from the menu.
•Select the appropriate report in the file open dialog (i.e. [EarthSoft.Reports.Library.50943.dll]) and continue selecting the appropriate items.
•Close and reopen EQuIS for the application to recognize the new report.
2.Open the System Tables. Click on either the Reference Tables, Data Tables or Forms, then on the dialog box. Change to the system tables in the list at the left. Open the [ST_REPORT] system table.
3.In the DISPLAY_NAME and REPORT_NAME columns, temporarily rename both Analytical Results II (e.g. Analytical Results II _temp). Check that the new name of the report does not modify the beginning of the report name. After modifying, click Save.
4.Publish Analytical Results II again to generate the customized report (follow number 1 above).
5.Open the [ST_REPORT] system table.
6.Find and select the row containing Analytical Results II in the list of reports. Right-click anywhere in the row to open the menu and follow the selection [Related Tables] => [Child Tables] => [ST_REPORT_PARAMETER]. This will allow the child table to be viewed in a tree-like manner, i.e. a [+] button will be visible to the left of each row.
7.Click on the [+] button to the left of the row containing the Analytical Results II Report to open the child table.
8.It is recommended that the child table be sorted by [PARAM_ORDER].
9.Click on the [ST_REPORT_PARAMETER] to add a row to this child table. The [ST_REPORT_PARAMETER] item is displayed below the grid (in a tree-like manner after [ST_REPORT]).
10.Fill-in each of the columns as specified below.
a.REPORT_ID – Should default to the same value as all other records in this child table If not, include it.
b.PARAM_NAME – Set the parameter name. It should begin with the @ sign and should be the name of the field being selecting (e.g. @SUBFACILITY_CODE). For v5.6.4+, the @ prefix is used in SQL Databases. For v5.6.4 and later, @ and p_ in ST_REPORT_PARAMETER may not be changeable.
Note: Not all fields in the database will work when added using this option. For example, adding reference tables such as RT_GROUP may be problematic. An extra parameter field can only be added if in the field is already in the Analytical Results query.
The error below will appear if the field cannot be added.
The workaround is to put this field into the Extra Fields query instead.
c. PARAM_TYPE: Enter the data type of this particular field (should match the type in SQL – e.g. varchar).
d. PARAM_ORDER: Should be one more than the largest number already in that column. Do not attempt to reorder the already existing report
parameters by modifying their order.
e. DEFAULT_VALUE: This is optional. Enter a value if desired for the default.
f. CAPTION: Enter a value, Subfacility\Name: i.e., this is how it will appear in the parameter list, such that the first name will appear as the main item in
the list, then there must be a back-slash “\”, and the name of the parameter, followed by a colon “:”.
g. DISPLAY_TYPE: Enter either SingleSelect, MultiSelect, Text, Date, etc… (likely the first or second option). This field is case sensitive.
h. REMARK: Enter the text that will show as a tooltip; e.g., “Please select subfacility [DT_LOCATION.SUBFACILITY_CODE].” In order for the new parameter
to work properly, the REMARK field must contain the table name and field name within square brackets, otherwise the parameter will not work.
i. VISIBILITY: Enter “Y”.
j. DATA_SOURCE: Enter the SQL statement to return the values to display in the list, e.g. SELECT DISTINCT SUBFACILITY_CODE FROM
DT_LOCATION WHERE FACILITY_ID = @FACILITY_ID. We recommend copying existing queries in ST_REPORT_PARAMETER to use as a template for
new ones. If a new query needs to be created, test running the DATA_SOURCE query in SQL Server Management Studio, for troubleshooting.
11.Click Save to keep the parameter just added.
12.Open ST_REPORT. In the REPORT_NAME and DISPLAY_NAME columns, rename Analytical Results II to the customized name. Make sure the customized name does not modify the first part of the report name. (e.g. Analytical Results Validated, rather than Validated Analytical Result).
13.Change the names of the reports renamed in number three back to the original names (e.g. Analytical Results II).
14.Open a new instance of the customized report, and the new parameter(s) should be visible and available for filtering.
Note: Using the WHERE clause with the @FACILITY_ID, will limit the return set to those for the appropriate facility. In other words, when the list is retrieved, the @FACILITY_ID will be replaced with the current facility.
The Office Hour video Enhancing EQuIS Reports Series - Part 8 - Adding Report Parameter Selections can be viewed on YouTube.
*(previously called (Beta) Analytical Results Report)