<< Click to Display Table of Contents >> Navigation:  Professional > Reports > Configuring Report Parameters > Populate Parameter Selection List (DATA_SOURCE)

•SQL Queries

•Preset Lists

•Example – Change the Data Source of a Report Parameter

•Example – Add Unit Types to Report Parameter Selections

 

Within ST_REPORT_PARAMETER, several DISPLAY_TYPEs (SingleSelect, MultiSelect, or MultiSelectAlpha) need a list of options populated for parameter selections via the DATA_SOURCE field. See the right pane in the image below, in which two analytes from a longer list are selected:

 

 

The DATA_SOURCE field stores the query that populates this list by one of these methods:

•SQL queries

•Preset lists

 

The DATA_SOURCE field can also be configured to do the following:

•Use a specific sort order with the SQL ORDER BY Clause

•Add additional descriptive columns, as the report code only uses the first column

•Filter a list further

•Display parameter selection list in a tree view (see Set Report Parameter Lists in Tree View)

•Display groups of STATUS_FLAG=R

 

SQL Queries

 

A DATA_SOURCE can use a SQL query, such as from a reference or data table. Other report PARAM_NAMEs, such as “@facility_id”, can be referenced. When adding a SQL query, consider the following:

•Security – if querying a data table, the FACILITY_ID should be specified so that only records for that facility, facility group, or with no facility appear. Otherwise, users may see data they do not have permissions for. Here is an example DATA_SOURCE from standard report parameter @action_level_codes with the FACILITY_ID condition highlighted:
select a.action_level_code from dt_action_level a where a.facility_id is null or a.facility_id = @facility_id or @facility_id in (select facility_id from dbo.fn_facility_group_members(a.facility_id)) order by a.action_level_code
 

•Status – particularly for reference tables, only show Active records, such as with the following condition:
where status_flag = ‘A’
 

•Useability – the report only reads the first column of the SQL statement. It may help users to include other, more descriptive fields, particularly if the first column is an ID or Code field. Custom names may also help. Here are several examples from standard report parameters for Analytes and Labs:
SELECT cas_rn as id, cas_rn, chemical_name FROM rt_analyte WHERE status_flag = ‘A’ ORDER BY chemical_name
 
SELECT DISTINCT company_code ‘Lab Code’, company_name ‘Lab Name’ from rt_company where company_type like ‘lab’ AND status_flag = ‘A’ order by company_code
 

•Performance – querying a large table may increase the time it takes for a report to load.

 

Many standard report parameters have SQL queries within the DATA_SOURCE. An administrator can use these as a basis for customizing or creating DATA_SOURCEs.

 

Preset Lists

 

A DATA_SOURCE can use hard-coded lists of options. These may relate to specific reporting needs and can increase performance over querying a large dataset. These DATA_SOURCEs have the following structure:

!report parameter selection column name!option 1|option 2|option 3

 

These elements make up a hard-coded DATA_SOURCE:

•a descriptive column name between two exclamation marks

•pipe characters ( | ) to separate the selections

 

Here are examples of this structure from standard report parameters for File Types and Reporting Limits:

!Output File Type!.dxf|.pdf|.bmp|.jpg|.png
 
!REPORTING_LIMIT!reporting_detection_limit|method_detection_limit|quantitation_limit

 

Example – Change the Data Source of a Report Parameter

 

Some parameter selections display a table with multiple columns, such as individual analyte parameters showing both the CAS_RN and CHEMICAL_NAME or individual locations showing the SYS_LOC_CODE and LOC_NAME. A SQL SELECT statement can be expanded to include additional fields of interest, such as LOC_TYPE. This then allows the user to sort and select based on values in this field. For example:

1.Open ST_REPORT_PARAMETER and filter to your REPORT_ID and PARAM_NAME = ‘@sys_loc_codes’.

2.In the DATA_SOURCE field, paste:
select sys_loc_code, loc_name, loc_type, data_provider from dt_location where facility_id in (select facility_id from equis.facility_group_members(@facility_id)) order by loc_type
 

3.Save.

 

This statement allows the SYS_LOC_CODE, LOC_NAME, LOC_TYPE, and DATA_PROVIDER fields from DT_LOCATION to be provided for the active facility or facility group for the @sys_loc_code parameter. The report parameter selector presents the data sorted by LOC_TYPE.

 

Example – Add Unit Types to Report Parameter Selections

 

The report parameter selection menu populates the available items for selection based on the logic in ST_REPORT_PARAMETER.DATA_SOURCE. The default unit types (from RT_UNIT.UNIT_TYPE) that display are currently cpv (Concentration Per Volume) and cpw (Concentration Per Weight). Other examples of unit types include length, weight, time, volume, rate-v (rate by volume e.g. flow rate), radium-v (radiation by volume), radium-w (radiation by weight), and temperature. Additional UNIT_TYPEs may be added as follows.

 

1.Open ST_REPORT and note the REPORT_ID that references the report being used.

2.Open ST_REPORT_PARAMETER and filter for this REPORT_ID.

3.Find all rows with PARAM_NAME=@reporting_unit or @report_unit.

4.For these rows, edit the syntax in ST_REPORT_PARAMETER.DATA_SOURCE to add the new UNIT_TYPE. For instance, to add radiation unit types (radium-v and radium-w), the following code should be used.