How to Add Parameter Selection

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. Please note that these steps do not apply to EDDs published as reports.

An EQuIS Administrator can complete the following steps:

1.Ensure the Analytical Results II Report is published to the database; an entry should exist in ST_MODULE with the following:

oNAME = "Analytical Results Report Package - 50943.dll"

oVERSION_NUMBER = the build of the active database

Otherwise, follow the steps on the Publish a Report - Quick-start Guide page.

A user may wish to add the additional report parameter to a separate copy of the report, rather than the original. Consult the Handling Multiple Copies of Same Report page for setup instructions.

2.In the ST_REPORT system table, locate the desired report by filtering the DISPLAY_NAME column (e.g. for "Analytical Results II").

3.Right-click anywhere in that row and select [Related Tables] => [Child Tables] => [ST_REPORT_PARAMETER].

4.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].

5.Click on the [ST_REPORT_PARAMETER] button at the bottom of the screen 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]).

6.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 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 – Enter the data type of this particular field in the database (should match the type in SQL – e.g. varchar, int, datetime, real).

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 – 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", 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, including the table and field reference in square brackets; e.g., “Please select subfacility [DT_LOCATION.SUBFACILITY_CODE].”

Important: 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.

Not all fields in the database will work when added using this option. For example, adding reference tables (RT_) such as RT_GROUP may be problematic.

i.VISIBILITY – Set = 'Y' to allow the parameter to be visible in the report.

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

Notes:

•Report parameters are case-sensitive and must be referenced using the same case with which they are defined.

•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.

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 list selection (assuming MultiSelect is selected in Step g above) to Analytical Results II where you can select SYS_SAMPLE_CODE under Sample > Sample_code(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'

Here is an example of a static list, with options separated by a pipe character, | . Note the inclusion of ' NULL/BLANK' to allow null results as a filter option:

!header_name_within_report_parameter_selection! ' NULL/BLANK'|Y|N

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.

7.Click the Save button to save the parameter just added. Click the Reports button, then Refresh, to open your updated report. 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. 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 Additional 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.