Add Parameter Selection
Copyright © 2020 EarthSoft, Inc • Modified: 21 Feb 2020
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, first import a fresh copy of the report (if one already exists, use the report publisher to import a fresh copy, and give the report a unique display_name that reflects the custom nature of the report.
a.From the Reports dialog box, click the Publish button (top-right) and select the appropriate .dll file for the report you are copying/basing your new report on; then click Open. (If you are unsure which .dll file is associated with your target report, return to the Reports dialog and hover the cursor over any report name, a balloon will pop up containing the .dll file associated with the report). This will open the Report Publisher.
b.In the Report Publisher select the bottom report (if there are multiple reports shown) and change the New Report Display Name. Click Save.
2.Find the report in ST_REPORT and filter for that REPORT_ID in ST_REPORT_PARAMETER. Order the ST_REPORT_PARAMETER records by the PARAM_ORDER field.
3.Click on the ST_REPORT_PARAMETER grey button below the table to add a new row. Populate the values as follows:
a.REPORT_ID: Should be pre-populated with the same REPORT_ID; if it is not, populate it.
b.PARAM_NAME: Must start with '@' and should then contain or be identical to the database field name, e.g., 'task_type', so that the entire name = '@task_type'.
c.PARAM_TYPE: Should match the generic type of the field in the database (int, varchar, datetime, real).
d.PARAM_ORDER: Should increment the highest existing PARAM_ORDER value by 1 (do NOT change the PARAM_ORDER of existing records).
f.CAPTION: List a caption for the field. If desired, you can insert this into an existing section of the report parameters, e.g., 'Sample\Task Type(s):'.
g.DISPLAY_TYPE: Case sensitive! Choose from: Boolean, Date, MultiSelect, SingleSelect, Text.
h.REMARK: This field is essential to make the report code filter on the parameter. Enter:
'Select [Table.FieldName]' e.g., 'SELECT [DT_SAMPLE.SYS_SAMPLE_CODE]'.
Note: This field cannot point to a reference ('RT_') table; [this causes an error].
i.VISIBILITY: Set = 'Y' to allow the you to select a value or 'N' if you only want the default_value to be used.
j.DATA_SOURCE: Enter a SQL statement that will pull the data from the database.
Tip: you can use the other data_source strings as guidance, and it is recommended that you confirm this statement returns the desired values within SQL Server Management Studio. The following example adds a drop-down (assuming MultiSelect is selected in Step g above) to Analytical Results II where you can select SYS_SAMPLE_CODE from a drop-down under Sample>Sample_codes(s):
'Select SYS_SAMPLE_CODE from DT_SAMPLE s where s.FACILITY_ID in (select FACILITY_ID from dbo.fn_facility_group_members(@facility_id)) UNION select 'NULL/BLANK'.
k.REQUIRED_YN: Set = 'Y' to make this field required or 'N' to leave it as optional.
l.PARAM_SETTINGS: Not needed for this functionality.
m.EUID: Auto-populated by EQuIS.
4.Save changes in the ST_REPORT_PARAMETER table. Reconnect to the database, and open the custom report you imported in Step 1. You should see your new report parameter in the list of parameters, and be able to select a value or values by which to filter the report.
5.You may need to choose the same field from the "Additional Fields" report parameter if you wish to see the field in the report output.
The following restrictions apply in order to use this functionality:
1.Both the Extra Fields (@extra_fields) and the Extra Select (@extra_select) report parameters must be present and supported in the report already.
2.For the @extra_fields report parameter:
a.EQuIS users can add a table and its columns to this record's DATA_SOURCE column, and the report will add these new fields if they can be joined to the other tables in the report.
b.It is not possible to add a table that cannot be joined precisely with the existing tables. For example, DT_WATER_LEVEL cannot be added to the Analytical Results II Report, because there is no match between DT_SAMPLE.SAMPLE_DATE and DT_WATER_LEVEL.MEASUREMENT_DATE.
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)