How To Add Parameter Selection
Copyright © 2019 EarthSoft, Inc • Modified: 12 Nov 2019
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.
To filter by an additional field (i.e. include in parameters) in the Analytical Results II Report, complete the following:
1.Make sure the Analytical Results II Report* is published to the database. (will not work with Analytical Results (Extra Fields) Report).
•Open the report window and right-click in the white-space.
•Select Publish Report(s) from the menu.
•Select the appropriate report in the file open dialog (i.e. [EarthSoft.Reports.Library.50943.dll]); 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. 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). Make sure the new name of the report does not modify the beginning of the report name. After the modification is complete, click Save.
4.Publish Analytical Results II again to generate the customized report (following Step 1).
5.Open the [ST_REPORT] system table.
6.Find and select the row containing the 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. It is recommended that the child table be sorted by [PARAM_ORDER].
8.Click on the [ST_REPORT_PARAMETER] to add a row to this child table. The [ST_REPORT_PARAMETER] item is displayed below the grid (appears below the grid in a tree-like manner after [ST_REPORT]).
9.Fill in each of the columns as specified:
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 be the name of the field being selected (e.g. @SUBFACILITY_CODE). For v5.6.4 or after, the "@" prefix is used in SQL databases. For older than v5.6.4, @ 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.
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 – Optional, enter a value, if desired, for the default.
f.CAPTION – Enter a value like “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 “\”, 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).
h.REMARK – Enter the text that will show as a tool-tip; 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".
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.
10.Click the Save button to save the parameter just added.
11.Open ST_REPORT and in the REPORT_NAME and DISPLAY_NAME columns, rename Analytical Results II to a 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).
12.Change the names of the reports that were renamed in Step 3, back to the original names - Analytical Results II.
13.Now open a new instance of the customized report, and the new parameter(s) will be visible and available for filtering.
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)