Using SAI360 Parameters for BI Reports

note_awesome

NOTE:

The following section will only apply to reports which are designed using the Pentaho Report Designer.
If you are looking for information about
- the generic parameters which are used in the Pentaho Report Builder, please refer to this section.

When to Use SAI360 Parameters for BI Reports

First of all: SAI360 parameters for reports are optional. Existing, or newly created BI reports will run perfectly fine without the use of SAI360 Parameters.

The biggest difference/advantage for the use of SAI360 Parameters for BI Reports is that as a designer I can make use of the already existing filtering mechanisms of the SAI360 Web Application.

For example, when a parameter is required so that the user can select from a Department List, but at the same time we need to make sure that the Departments are filtered according to the user's profile, then this is the way to go.

The most relevant filters, be it for security purposes or for user convenience, already exist within SAI360, and it is easier to re-use the already existing mechanisms, instead of re-creating and maintaining them in a separate location.

Since it is possible to 'hide' these parameters from the user's interface, they can also be used to as a means to tighten security for BI reports - and apply any security filters dynamically should the user's circumstances alter (for example change of Department, or elevation to a higher managerial level granting further access to records).

Once a BI Report Parameter has been defined, the following will happen:

A User selects a BI Report from the existing list of Reports
The system will display the SAI360 Parameter interface, from which the user can select the required values.
Once done, the system will display the BI report and apply the filter values as selected by the user.

Approach to Parameters for BI Reports

The links between the main record and any related and/or referential records are entirely built in the Pentaho Report Designer.
All what is required is the correct BMS_ID for the related record, which can be taken from the MAIN table of the record without the 'related' attribute (in this example, from the PERSON component).
Therefore, you can use the same Parameter for ASSIGNED TO or ASSIGNED BY. All you need is the BMS_ID of the person record.

Linking Report Designer Reports and SAI360 Parameters

Linking BI Reports to SAI360 Parameters requires a series of steps, as outlined below:

note_awesome

NOTE:

There is a 1:1 relation between reports and SAI360 parameters. I.e. you can only apply one SAI360 parameter to a report.

Step

Description

Create the BI Report

Create the Report in the Pentaho Report Designer
To make sure that the WHERE clause work correctly, initially use either:
- hard-coded values, or
- apply Pentaho Parameters.
The required values/format will be for:

all Lookups (single or multi-select)	the ID of the lookup item. A sample sql code for the WHERE close for Action Priority could be: `WHERE "dbo"."rt_action"."priority_id" = '9999999_104'`
all Component Lookups	the BMS_ID of the corresponding record. A sample sql code for the WHERE clause for a related person could be: `WHERE "dbo"."rt_person"."bms_ID" = '9999999_1'`
Date Fields	For Date Parameters we recommend to set up a parameter as follows: Value Type = Date Label = as appropriate Data Format = dd/MM/yyyy Display Type = Text Box All other fields: leave blank You can then simply refer to this parameter in the sql statement as in this example: `WHERE "dbo"."rt_action"."date_raised" >= ${Param_Date}`
Time Fields	For Time Parameters we recommend to set up a parameter as follows: Value Type = Time Label = as appropriate Data Format = HH:mm Display Type = Date Picker All other fields: leave blank You can then simply refer to this parameter in the sql statement as in this example: `WHERE "dbo"."rt_incident"."incident_time" >= ${Param_Time}`

Once done, make sure that you 'Publish' the Report to the Web Application.

Define the BI Report Parameter in SAI360

To set up a new Parameter:

In the Web Application, navigate to:
Designer Reports and Graphs BI Report Parameters.
and select NEW.
Select the BI Report to which you would like to link the Parameter.
Show Pentaho Parameters:
- If in the Report Designer a parameter is designed to be displayed, AND you select this option, then the Web Application will display the Pentaho parameters to a user when the report is displayed.
- If this flag is not set, then the Pentaho parameters will NOT show when the report is displayed.
- If in the Report Designer a parameter is set to be hidden, then this parameter will not be displayed in the Web Application under any circumstances.
- If you intend to give the user of the report the option to change the output format of the report (e.g. as PDF or HTML), then you will also need to set this flag.
Define each Parameter for this report.
- Name: this name will be used as the parameter reference in the report.
  Please note that the name is case sensitive.
- Label: will be displayed to the user in the parameter window.
- User Access: Select an option from
  - Modifiable (user can view and change),
  - Read Only (user can view but not change), or
  - Hidden (user cannot view or change).
- Mandatory: tick this field if the parameter is mandatory.
  (If a parameter is optional, you will have to make sure that the SQL statement in the Report Designer will NOT apply the filter if the parameter value is left empty.)
- Field Input Type: select what type of field the parameter needs to be, for example a Date Field, a Single Lookup, Multi Component Lookup, etc.
- Lookup Type or Component Type:
  these dynamic fields and will only be displayed if the Field Input Type is either based on a
  - Lookup: system will display a list of all lookups in the database
  - Component Lookup: system will display a list of all components in the database.
- Mandatory or Optional Filters:
  these fields will only display of the Field Input Type is any form of lookup.
  The user can select a filter which will apply to the parameter.
- Role Defaults:
  it is possible to define a Role specific Default Value for all field input types, with the exception Multi Select Lookups or Multi Select Component Lookups.
  - Roles:
    - if you don't add any role, then the default will apply for EVERY user in the system.
    - if you add on or several roles, then the default will apply to all users who are member of the selected role(s).
  The defaults can be based on different sources, or
  - Value Types:
    - undefined: no default has been defined
    - Value: you can enter a hard-coded value. If you are defaulting a lookup or a component lookup, then you need to enter the corresponding ID of the field rather than the actual value.
    - Constant: if you want to use a User Constant to default a parameter, for example TODAY_ADD_180, or USER_ID.
    - Component Constant
    - Profile: to default a parameter based on the user's profile value.
  - Default Value:
    Depending on the selected Value Type, the system will either display a corresponding selection list, or a text field, where you enter the required value.
Save the Parameter.
To run the report with the newly created, or modified Parameter, refresh the browser window (use CTRL+5 keys) and navigate to the report. No publishing is necessary.

Set up the Parameter in the BI Report in the Pentaho Report Designer.

If you have used hard-coded values, you will have to replace these with parameters:

Set up parameters in the Pentaho Report Designer and update the WHERE clauses so that they can receive parameter values as outlined in this section.
Please note that the name is case sensitive.
Save the changes, and publish the report to the SAI360 Web Application.

note_awesome

COPYING AND MOVING REPORTS WITH SAI360 PARAMETERS:

If you COPY a report with a SAI360 Parameter, the link between the report and the parameter will NOT be retained.
It will therefore be necessary to re-recreate the parameter in the copy of the report you just created.
If you MOVE a report however, the link will be maintained and no further actions will be necessary.

In This Section

Samples for Complex Parameters

Valid Format Strings for Dates in BI