NOTE:

• The following section describes how the generic Parameter Functionality can be implemented. These parameters are entirely built in the Report Design Tool.
• To leverage from the existing filtering options in SAI360, and/or to make use of familiar parameter interfaces in SAI360 (especially in regards to Tree Views), an alternative is the use of SAI360 Parameters as described in this section.

If you want to ...

Perform these steps ...

Create a script which will return the selection list in the parameter field.

In the same fashion you created a script to return the records for the report, you will need to

• Select a DB Connection (most likely the same as for the records)
• and define a SQL query which will determine which entries will be shown in the PARAMETER box.
  
  The following example returns a list of those persons.
  The JOIN statement acts as a filter to only show persons who are listed as a "Responsible Supervisor" in any of the Incidents which are listed in the Report.
  
  
  
  

Create a new Report Parameter.

In the Data Tab for an existing report, you need to right-click on Parameter, and

• select Add Parameter:
  
  
  
• This will open the Parameter Window:
  
  
  
  To define the parameter, you MUST fill in the following fields (the fields which are not listed are optional)
  
• Under Data Sources, select the script which is used to retrieve the parameter values, and fill the following fields:
  
• Name: Name of the Parameter, and of the variable used.
  This name will be used as the variable in the WHERE condition of the main SQL script.
  (NOTE: when you intend to use SAI360 parameters, please be aware that the name is case sensitive!)
  
• Label: The label of the parameter to be displayed to the End User
  
• Value Type: select the value type, according to the type of value which will be used in the WHERE condition of the main SQL script.
  
• Display Type: select the display format for the parameter.
  
• Query: select which SQL query is being used to populate the current parameter
  
• Value: select which field from the SQL query needs to be passed on for the WHERE condition in the main query.
  
• Display Name: select which field from the SQL query needs to be shown to the user in the parameter field.
  

Apply a parameter to the main SQL script.

In the final step, you will need to use the parameter in a WHERE condition of the SQL script used to retrieve the records for the report.

• The format is: ${Name of Parameter}
  
  In the above example, the parameter is named "ListOfSupervisors", and a resulting query could look as follows:
  
  WHERE Supervisor."last_name" = ${ListOfSupervisors}
  

If you want to ...

Perform these steps ...

Apply several Parameters to a report.

Repeat the above steps until you have all the parameters you need.

Only show values in a list of parameters which will actually create a report.

Since the list of values in a parameter can be defined via a SQL script, you can also filter this list via a WHERE condition. For example:

• Sample Requirement:
  You create a report "Incidents by Responsible Supervisor", where a user can select the "Responsible Supervisor" from a list of names.
  It is required that the List of "Responsible Supervisors" only shows persons who actually have Incidents they are responsible for.
  
• Solution:
  You create a script which will only return persons, where there is a corresponding record in the Incident table.
  
  The script for the parameter COULD look as follows:
  
  SELECT distinct
Persons.last_name,
Persons.dwi_person,
(Persons.last_name + ', '
+ Persons.first_name) as FullName

FROM "dbo"."rt_incident" as Incidents
LEFT OUTER JOIN "dbo"."rt_person" as Persons
ON Incidents."dwk_responsible_supervisor" = Persons."dwi_person"

ORDER BY
Persons."last_name" ASC
  

Link Parameters

You have the option to link several parameters together.

• Sample Requirement:
  an Incident Report is filtered by Department, and the Responsible Supervisor.
  It is required that, after selecting the Department, the system will only list those Persons who work in the selected Department, and below.
  
• Solution:
  Similar to applying a parameter to the report itself, you can do the same for parameters.
  The value selected in the first parameter will be used to filter the values of subsequent parameters.
  
  Please see below for a possible implementation for such an approach.

Apply a roll-up to a Parameter.

The data which are generated by the ETL tool also allow the application of rollups.

• Sample Requirement:
  You create a report "Incidents by Responsible Supervisor", where a user can select the "Responsible Supervisor" from a list of names.
  It is required that the List of "Responsible Supervisors" only shows persons
  a) who actually have Incidents they are responsible for.
  b) work in the Department, and all Business Units below this Department, which the user has selected in the first step.
  
• Solution:
  This report will require two Parameters:
  
  1.) Select a Department, with the script returning the "Department Key"
  
  2.) Select the Person, but only show Persons who work in the selected Department.
  
  The script for the parameter COULD look as follows:
  
  SELECT distinct
Persons.last_name,
Persons.dwi_person,
Persons.dwk_department,
(Persons.last_name + ', '
+ Persons.first_name) as FullName

FROM "dbo"."rt_incident" as Incidents
LEFT OUTER JOIN "dbo"."rt_person" as Persons ON Incidents.dwk_responsible_supervisor = Persons.dwi_person

INNER JOIN "dbo"."rt_department" as Departments ON Persons.dwk_department = Departments.dwi_department

INNER JOIN "dbo"."dn_department_parent_department_cl" as Rollups ON Departments.dwi_department = Rollups.dwk_department

INNER JOIN "dbo"."rt_department" as ParentDepartment ON ParentDepartment.dwi_department = Rollups.dwk_parent_department

WHERE Rollups.dwi_parent = ${ParentDepartment} -- This value is returned from the first parameter in the report - Department

ORDER BY
Persons."last_name" ASC
  

Emulate the use of a Hierarchical View

The Report Designer does not offer hierarchical selection list, like for example a Tree View.

The work-around would be to "daisy-chain" several linked parameters, with

• each parameter representing a "level" in the Hierarchy, starting from the highest to the lowest.
• subsequent parameters being filtered by the previous parameter.

Make a parameter "truly Optional".

• What is a "truly optional" Parameter?
  While a report may have a parameter, this parameter will only applied as a filter to a report when the user has actually selected a value from the parameter.
  
• Sample Requirement:
  You created a report which can be filtered by "Department", and by "Responsible Supervisor. It is required that:
  a) The user can initially only select the Department, and the System will then show all the Incidents in the Department.
  b) OPTIONALLY, the user then can select a Person (the "Responsible Supervisor"), to only see Incidents which are related to this Person.
  
• Sample Solution:
  To achieve this, you will need to add an
  - additional 'hidden' parameter
  - and use a 'Post-Processing Formula'
  
  The BASIC LOGIC behind the formula is:
  If no value has been selected for the optional parameter, then set this current parameter to TRUE (or 1), otherwise set it to FALSE (or 0):
  
  
  
  The SQL script used to retrieve the records then could look as follows, making use of both of these parameters:
  
  ...
and (${NoSupervisorSelected} = 1 OR i."dwk_responsible_supervisor" = ${ResponsibleSupervisor})
...