Keywords: input parameter, drill-down, drill down, filter, filtering, report customization
|Note: This is how to filter the drop-down list based on what was returned, based on other input parameters. Not to be confused with W2182, which describes how to add additional filters on the report before output on defined parameters.|
Report parameters can be used to filter other report parameters. The (Beta) Analytical Results Report supports custom parameters that are defined in the ST_REPORT_PARAMETER table. This article explains how to create a custom drill-down (or filter) parameter. For example, suppose your database contains thousands of locations. Selecting individual locations from a list of thousands of locations can be a tedious and time consuming process. Adding a custom filter parameter will allow you to pre-filter the list of locations to create a more manageable list.
1. Make sure the (Beta) Analytical Results Report is published to the database.
To publish a report to the database: Open the report window, 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, change to the system tables in the list at the left. Open the [ST_REPORT] system table.
3. In the REPORT_NAME and DISPLAY_NAME columns, temporarily rename both (Beta) Analytical Results and Analytical Results (Extra Fields) (e.g,. add _)
4. Publish (Beta) Analytical Results again to generate the customized report (following Step 1).
5. Open the [ST_REPORT] system table.
6. Find and select the row containing the (Beta) Analytical Results 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 (Beta) Analytical Results Report to open the child table.
8. It is recommended that the child table is 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 (appears below the grid in a tree-like manner after [ST_REPORT]).
10. 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 must begin with the "@" sign and should reflect the name of the parameter you want to filter (e.g. @SYS_LOC_CODE_PREFIX).
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.
e. DEFAULT_VALUE – Optional, enter a value, if desired, for the default.
f. CAPTION – Enter a value like “Location\Prefix:” 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 needs to be a back-slash “\”, the name of the parameter, followed by a colon “:”.
g. DISPLAY_TYPE – Enter "Text" so any user can type in the desired prefix.
h. REMARK – Enter the text that will show as a tool-tip; e.g., “Please enter a prefix before selecting from the list of individual locations (e.g. 'B' for boreholes).”
i. VISIBILITY – Enter “Y”
j. DATA_SOURCE – Leave this field blank
11. Click the [Save] button to save the parameter you have just added.
12. Find the record for the existing @SYS_LOC_CODES parameter.
13. Change the DATA_SOURCE so it will filter based on the new parameter, e.g.:
select sys_loc_code, loc_name from dt_location where sys_loc_code like coalesce(@SYS_LOC_CODE_PREFIX,'') + '%' and
facility_id in (select facility_id from dbo.fn_facility_group_members(@facility_id))
14. Click the [Save] button to save the parameter you have just modified.
15. Open ST_REPORT and in the REPORT_NAME and DISPLAY_NAME columns, rename the (Beta) Analytical Results and Analytical Results (Extra Fields) to your customized names.
16. Change the names of the reports that you renamed in Step 3, back to the original ones -(Beta) Analytical Results and Analytical Results (Extra Fields).
17. Now open a new instance of your customized report and the new parameter(s) should be visible and usable for filtering.