How To Add Parameter Selection

<< Click to Display Table of Contents >>

Navigation:  EQuIS Library > Reports >

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.

 

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). Steps to publish report to the database:

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 should then contain or be identical to the database field name, e.g., 'task_type', so that the entire name = '@task_type'.

 

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

 

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_TYPECase 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; 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.

 

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'

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.
 

10.Click the Save button to save the parameter just added. 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.

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 - e.g., 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.

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

 

Office Hour Video

 

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)