Intellicus Enterprise Reporting and Business Insights 18.1

Working with Report Parameters

1 views June 28, 2020 0

To access Report Parameters, on Intellicus Desktop Studio screen, click Tools > Parameters. The Parameters screen appears.

Report Parameters Screen
Figure 1: Report Parameters Screen

You can perform the following actions:

Action Comments
Add Add a new parameter with local definition, which means this parameter will be local to a specific report only.
Modify Edit properties of local parameter.

This option is not available for parameters imported from repository by reference.

Delete Delete selected parameter from the report.

If it is locally created parameter, then its definition will be deleted. If it is an imported parameter, local copy or reference is deleted and the original copy is saved in the repository.

Import Import one or more parameters from the repository. You can either import a parameter by reference or without reference.

Select ‘Yes’ or ‘No’ to import the parameter by reference or not.

If you select ‘Yes’, then the latest definition of the parameter object will be used at the report run time.

If you select ‘No’, the definition of the parameter object at this time is copied from repository and a local copy is created. Any changes in repository parameter object does not affect this report. Refer to section, Importing Parameters.

Export This option is available if the user has Administrator privilege or access rights to create parameters objects in the repository. A new parameter object is created in the server repository with the definition of the local parameter.
OK Saves all the changes made to report the parameter list in the report.
Cancel Close screen without making changes to the report. Changes made to repository by Export cannot be reverted by clicking Cancel button.

Adding Parameters

Parameter Details
Figure 2: Parameter Details

To add or create parameters, click menu Tools → Parameters to open the Parameters screen. Click Add button. The options in this screen are given below.

Field Description
Name NameSpecify a name to uniquely identify this parameter. You can access this parameter in the report using this name enclosed by <% and %>. For example, Date.
Prompt Prompt

Specify the prompt caption that should appear on Input Parameter Form while prompting for this parameter. For example, if the name is Date, the prompt can be Product Date.

Data Type Data typeSelect the type of value to be provided at run time. The data type of a parameter decides the tools provided and validations applied while you enter the values for this parameter.

Data type Input tools Validations
CHAR Text box, list box, multi select list box Alphabets, numbers, or special characters are allowed
NUMBER Text box Numbers are allowed
DATE Date, date part, variable date Valid date according to selected input format

Predefined data variables such as MONTH_START_DATE, YEAR_START_DATE, and CURRENT_DATE

BOOLEAN Check box Select or de-select
Size sizeSpecify number of digits or characters that this parameter value can accept.
Format formatSelect the input format to provide value for this parameter. Click button button to open Data Format dialog box to select the format.

format-type
Based on the format selected, a format string appears in the entry box. Format string characters for date and time are as below:

Characters Use Example (Friday, December 26, 2008, 17:46:13 hours)
dd Date 26
ddd Day in 3 characters Fri
dddd Complete day name Friday
hh Hour of time in 2 digits 17 (or 05)
Mm Minute of time in 2 digits 46
MM Month in number in 2 digits 12
MMM Month name in 3 characters Dec
MMMM Complete month name December
ss Seconds in 2 digits 13
yy Year in 2 digits 08
yyyy Year in 4 digits 2008
a time in 12 hours format am/pm

Negative numbers

You can provide format strings (patterns) for positive number and negative number. To separate both the patterns, use a semi colon (;) character. For example, ##,###.##;(##,###.##).

Note: Negative pattern is optional. If you omit negative pattern, localized minus sign (-) will be considered to represent a negative number.

Prompt Format prompt-format

Select the format for prompting the parameter.

Default Value Default value

Specify the default value. This value is pre-populated on the parameter control on the Input Parameter Form when it is loaded. For multi select type controls, you can select multiple default values. For example, when you select parameter for data type as Date, the Default Value entry box changes to a drop-down.

Database Time Zone database time zoneYou can specify a value in this drop-down box as well as select a value from the options:

  • CURRENT_DATE
  • MONTH_START_DATE
  • YEAR_START_DATE

You can take the default date ahead / behind the date falling on any of the options set.  For example, you can set default date as 3 days after CURRENT_DATE, or 5 days before month start. You can do that by specifying CURRENT_DATE + 3 and MONTH_START_DATE – 5 respectively.

At run time, the date that will appear will have default date accordingly.

Time Zone is useful when different users access the application from different time zones. In such cases, it may happen that date/time data stored in database may be in one specific time zone and user may be accessing application from a different time zone. In this situation, application can convert date / time type data from one-time zone to another time zone.

User Time Zone In User Time Zone, select the time zone from where the user is expected to access the application and so provide parameter value in that time zone (to convert from). Select SYS_USER_TZ to use time zone applicable at run time (depending pre-set priority by the application). Select SYS_SERVER_TZ to use time zone set on Server Properties page (Report Server’s time zone).
Prompt User Time Zone Select Prompt User Time Zone check box to select time zone at run time at the time of providing value for this parameter on Parameter Details screen.

In Database Time Zone, select the time zone in which date / time data was entered in the database (to convert to). Select SYS_CONN_TZ to use time zone set on Database page. Select SYS_SERVER_TZ to use time zone set on Server Properties page (Report Server’s time zone).

For time zone conversion to take place, value for database time zone and user time zone needs to be provided.

Description descriptionSpecify information about parameter to provide the desired parameter value. This description is displayed on the web portal when you run the report which uses these parameters and by clicking on desciption icon icon next to the parameter. Description is also displayed on Save Parameter Object and Open Parameter Object screens.
Mandatory mandatorySelect Mandatory check box to indicate a value to be given mandatorily to this parameter before submitting Input Parameter Form. An error message pops up if end user tries to submit the form without entering any value for this parameter.
Visible Select Visible check box to decide a parameter and if its values are visible to end user or not. By default, if this check box is selected, the parameter is displayed. Parameters to drill down reports are defined as visible false. Data security parameters must be defined with visible false.
Enable Select Enable check box to decide that the parameter is active on the Input Parameter Form for user inputs. By default, it is selected. This property can also be changed using scripting added to Input Parameter Form.
Secure Select this check box to secure the parameter values.
Pass Values Using Table Select Pass Values Using Table check box to pass multiple parameter values through a table. This is done especially when the number of values that can be passed (total number of bytes of selected values) as part of stored procedure or SQL is more than the limit allowed.

Maximum Selectable: Specify the maximum number of values a parameter can take as input.

  • Enclosed By: Specify the character used to enclose the set of values. This depends on the database.
  • Separator: Specify the character used to separate two values. This depends on the database.
Restrict to List Select Restrict to List check box for the parameters for which Input Type is ‘Dropdown’. By default, it is selected to ensure that the users select value(s) only from the list.

Data Restriction: This property is visible to Super Admin users only. As a super administrator, you can assign a set of parameter values for each user or an organization. Hence, the user can select value(s) from the assigned set only.

For example, if you want Tom to select values from ‘Central region’ and ‘Western region’, and John to select values from ‘Eastern region’ and ‘Alaska region’, then you set property as ‘True’ for this parameter and assign respective values. On Intellicus web portal home page, click Manage Users > Modify > Data Restriction screen to view this.

Note: Data Restriction is enabled only when you are working with a parameter which is imported by reference.  To make any changes in the values, you need to open this parameter through Portal’s Parameter page.

Setting Dropdown Source

This is applicable when the input type for the parameter is Dropdown or Option. Values should be pre-defined or received from the database using an SQL.

Input Type input-typeInput type decides the input control provided to the end user to enter a parameter value.

Input Type Control
Edit text Textbox control with type in cursor
Dropdown Multi-select list box
Options Radio button
Slider Range of input type

Input type for Boolean type parameters are as below. Boolean type parameters are represented as check boxes and have only two statuses: ‘Checked’ and ‘Clear’.

To setup a Boolean type parameter:

1.       Select data type as Boolean.

2.       Under Values if Checked check box is selected, specify the value to be passed.

3.       If Checked check box is de-selected, specify value to be passed.

Data Restriction: This property is visible to Super Admin users only. As a super administrator, you can assign a set of parameter values for each user or an organization. Hence, the user can select value(s) from the assigned set only.

For example, if you want Tom to select values from ‘Central region’ and ‘Western region’, and John to select values from ‘Eastern region’ and ‘Alaska region’, then you set the property as ‘True’ for this parameter and assign respective values. On Intellicus web portal home page, click Manage Users > Modify > Data Restriction screen to view this.

Note: Data Restriction is enabled only when you are working with a parameter which is imported by reference. To make any changes in values, you need to open this parameter through Portal’s Parameter page.

Setting Dropdown Source
This is applicable when input type for the parameter is Combo or Option. Values can be pre-defined or dynamic (received from database using an SQL).

dropdown-source

To set pre-defined values:

dropdown-source2

1.       Under Dropdown Source, select Pre Defined option.

pre-defined-value

2.       In Display entry box, specify the value that should be displayed to the user at run time.

3.       In Value entry box, specify the value that should be used (passed as filter).

4.       Click add button button to add it in list. Repeat the steps 2 and 3 for each option.

5.       Click delete-icon button to delete a value.

6.       Click modify-button button to modify the value.

To set dynamic values:

dropdown source

1.       Under Dropdown Source, select Dynamic option. The Source Statement screen appears.

2.       To create SQL on SQL Designer, click SQL Designer button.

dropdown-source

3.       Specify SQL to be used to get data.

4.       After having specified the SQL, click Verify SQL button. If the SQL is valid, the fields it returned will be listed in Display Column and Value Column.

5.       In Display Column, select the field whose value should be displayed.

6.       In Value Column, select the field whose value should be used (passed as filter).

Display Parameter Name display-parameter-nameSelect Display Parameter Name check box when the parameters are used as a control on a report.

Note: Display Parameter Name has no effect when the parameter object is used in adhoc report.

Display Column display-columnSelect the column name to be displayed.
Value Column value columnSelect the value of the column.
Multi Select Select the Multi-Select tab header to select / specify multiple values for this parameter. For example, for Country Names, you can select multiple country names.multi-select

Maximum Selectable: Specify the maximum number of values a parameter can take as input.

  • Enclosed By: Specify the character used to enclose the set of values. This depends on the database.
  • Separator: Specify the character used to separate two values. This depends on the database.

Setting multiple default values

A multi-select parameter may have multiple default values. Default values are displayed selected at runtime on Parameter Details screen. Under Select Default Values area, select any of the following options.

default-value

  • Selected: To display some of the values as selected at run time, click to select any values from the list (appearing below).
  • All: All values displayed as selected at run time.
  • None: To display no value as selected at run time.
Search If a parameter is specified in a text box at run time, there are chances that you may provide a value that does not exist in the database. To avoid this, the parameter offering is set to possible values as a combo-box or multi-select options because selecting becomes difficult when there are large number of possible values.

You can filter the values to be offered to select from. For example, from all the product numbers, you can select ‘product numbers of the products belonging to this category and that product line’. This is made possible through search options. Set up a parameter with Input Type as ‘Dropdown’ and Dropdown Source as ‘SQL’. Specify SQL to fetch the record-set.

Select Search tab header. You can do a quick search by selecting Quick Search radio button and by providing minimum key length for search.

quick-search

OR

You can do an advanced search by selecting Advance Search radio button by providing the values for each column. Here, the fields that you will use to apply filter criteria (to get list of options in the combo) are specified.

advanced-search

Click Search button on this screen to get Search dialog box. You can provide filter options. The number of results displayed on the Result tab will depend on the value specified for the rows.

An empty row will be auto-appended once you complete with present level entry. Click delete-button to delete respective row.

To get filtered records on Result tab

1.       After selecting the right field for Display Column and Value Column under Dropdown Source, click Search button to expand the area.

display-value-column
2.       Select a column in Column Name.

3.       Select its Data Type.

4.       Select Prompt text that should appear at run time.

5.       Select Operator to set filter condition and provide Value1 (and Value2 based on condition).

On Result tab, you can view the values that satisfies the filter criteria given on the Search tab.

search tab

Select a single value and click OK to proceed.

Note: After setting filters with Search, when you open Multiple Default Values area, you will get list of values filtered based on conditions set here.

At run time, you will be presented with the Report Parameters screen for you to select parameters.

report-parameter

Tree View This provides additional information about the parameter value. For example, cities

Text box view

  • Indore
  • Bhopal
  • Bangalore

Tree View

India

          Madhya Pradesh

                       Indore

                       Bhopal

          Karnataka

                  Bangalore

United States (parent node)

          Nevada (child node)

              Las Vegas (leaf)

          California

              San Francisco

In this view, while you can view the parameter values (as last item in the hierarchy – leaf), you are also able to view other information about the parameter value. In this example, you can view the state and country in which a city is located. On the Input Parameter Form, you can select a branch to select all the values within the branch.

Tree-view
Select Tree View tab header. For Source Type, select Flat or Hierarchical depending on database.

In the first row of Levels, select the field that should appear topmost in the tree view. To have second branch, set the fields in second row

multi-select

When Flat is selected,

An empty row will be auto-appended once you complete with present level entry. Click delete-button to delete respective row.

Following type of tree views are available:

1.       Flat: Select this when you are using a flat database structure. When Flat is selected, the leaf is set as Display Column under Dropdown Source. Nodes are set under Tree View Details. For example, for a three-level tree, you will set two levels in Tree View Details and third in Dropdown Source. Make sure the SQL used to get parameter retrieves all the fields required to create the tree.

2.       Hierarchical: Select this when the database has hierarchical relationship. In Oracle, for example, when database has hierarchical relationship, the SQL uses Start with and connect by clause.

In case of Hierarchical, the query should return:

  • NODEID: Unique identification value of the node.
  • PARENTNODEID: Unique identification value of current node.
  • NODLEVEL: A number indicating node level of current node.
  • NODEVALUE: Actual value of node.

Example query for Oracle

select child “NODEID”,parent “PARENTNODEID”,level “NODELEVEL”,child “NODEVALUE”

from test_connect_by

start with parent is null

connect by prior child = parent

At report’s run time, following screen appears for you to select parameters.

report-parameter

Note: Tree view is not available for Search Options.

Link A report may need multiple parameter values. In this case, value displayed for a parameter may depend on the value specified in other parameters.  For example, values displayed in ‘Cities’ depends on the value selected in “States”.

You can link a parameter with a SQL combo type parameter.

General steps to get filtered list by linking parameters

1.       Select Link tab header.

2.       Select the parameters to be linked with the parameter being created.

3.       For the SQL being created for SQL Dropdown Source, specify name of the parameters being linked enclosed by <% and %>.

For example, to get list of cities belonging to state selected in another parameter, specify this SQL in SQL box of this parameter:

Select CityNM from FinData where FinState=<%State%>

At run time, Parameter Input Form appears with these two parameters.  At that time, you can specify a value for ‘State’.  This value is used to fetch values for this (for example CityNM) parameter. This dropdown will have only the cities belonging to the value provided for ‘State’.

Validation Applying range validations to parameter ensures that you do not key in invalid values. Validations can be set if the Input type of the parameter is ‘Text box’.

 

 

 

 

 

 

Select Validation tab header.

You can provide:

  • Valid Characters and Allowed Characters (characters, numbers, or dates)
  • Invalid Characters (characters, numbers, or dates)
  • Allowable Range and Invalid Range (From and To range of values so that the script to be executed at run time validates the entered value)

Specifying validation for Number type parameter
To specify Range of numbers, mention starting number in From box and ending number To box (of the same row).

If valid value is a number onwards, mention in the starting number in From box, leave To box blank.

If a valid value is up to a number, leave From box blank and mention the ending number in To box.

A number can be positive, negative, with or without decimal point.  For example, 24, -17 and 56.77.

An empty row will be auto-appended once you complete with present level entry. Click  to delete respective row.
Specifying validation for Character type parameter
To mention Allowed Characters, you may type in the characters or select the characters from Character set dialog.
Selecting characters from Character Set dialog
Click  button to open Character Set dialog.  Click a character to select it. Selected characters appear depressed.  To de-select a character, click it once more.  After making the selection, click OK to close the screen and return to Parameter Details screen.

Range(s)

Each character has a unicode ‘number’.  Unicode of the character will be considered for range validation.

To specify Range of characters, mention unicode of starting character in From box and that of ending character in To box (of the same row).

If valid value is a character onwards, mention unicode of starting character in From box, leave To box blank.

If valid value is up to a character, leave From box blank and mention unicode of ending character in To box.

An empty row will be auto-appended once you complete with present level entry. Click  to delete respective row.

How to specify Date values

You can specify a date or from dropdown, select any one among

  • CURRENT_DATE (The date on which the report would be generated).
  • MONTH_START_DATE (First day of the month in which the report would be generated).

YEAR_START_DATE (First day of the year in which the report would be generated)..

Range To specify range of dates, mention date in From box and To box (of the same row).

If valid value is a date onwards, mention date in From box, leave To box blank.

If valid value is up to a date, leave From box blank and mention date in To box.

An empty row will be auto-appended once you complete with present level entry.  Click  to delete respective row.

About Scripts for parameter value validation

You can add a validation script for a parameter.  Click Add Script button to open Script Editor screen and write the script.

At parameter level (parameter name will appear in the section), OnChange() event is supported. It means, validation script will be executed when:

1.       User types in a value for the parameter (for input type TEXT), or

2.       Selects/de-selects value from the parameter combo/list/tree.

3.       Selects/de-selects a check box.

Validation script written at parameter level can access other report parameters. It can also access parameter objects (even if not imported) and global business parameters. This will be Read-only access.

If the parameter value is valid, script will return ‘True’. If it is invalid, script will return ‘False’. You can set an error message that should be displayed if parameter validation fails. Report will not be generated if parameter validation fails.

Using script, you can modify attributes of parameters. (For example, if paramA is invalid, disable paramB.) The Parameter Details screen reloads the parameters that are affected by the script.

In case of scheduled report execution, the Parameter Details screen is not displayed. Hence, script will be executed at the time of saving of schedule tasks. Script will not be executed at report run time.

Importing Parameters

Parameters stored in the repository can be imported and reused during designing of reports.

Parameters can be imported either by selecting individually or by reference. In case of importing the parameters by reference, if you modify the source parameter at any point of time, this change automatically effects all the reports where the parameter is used.

On the Parameters screen, click Import button. The Import screen appears.

Figure 3: Importing a parameter

Figure 3: Importing a parameter

  1. In Category object selector helps you navigate to folder to list the parameters below.
  2. To select a parameter for import, check the Selected checkbox in corresponding row.
  3. To select a parameter to be imported by reference, select the check box By Ref in the same row.
  4. Click the Apply

Selected parameters are imported to the open report.  Click OK to import the parameters and close the dialog.

Adding Scripts for parameter value validation

You can add a validation script for a parameter. Click Add Script button to open Script Editor screen and write the script.

Figure 4: Adding Script

Figure 4: Adding Script

At parameter level (parameter name will appear in the section), OnChange() event is supported. It means, validation script will be executed when:

  1. User types in a value for the parameter (for input type TEXT), or
  2. Selects/Unselects value from the parameter combo/list/tree.
  3. Checks/ Un-checks a check box.

Validation script written at parameter level can access other report parameters. It can also access parameter objects (even if not imported) and global business parameters. This will be Read-only access.

If the parameter value is valid, script will return ‘True’. If it is invalid, script will return ‘False’. You can set an error message that should be displayed if parameter validation fails. Report will not be generated if parameter validation fails.

Using script, you can modify attributes of parameters. (For example, if paramA is invalid, disable paramB.)  The Parameter Details screen reloads the parameters that are affected by the script.

In case of scheduled report execution, the Parameter Details screen is not displayed. Hence, script will be executed at the time of saving of schedule tasks. Script will not be executed at report run time.

  1. Click Compile to compile the scripts.
  2. Click Ok to save the changes.
  3. Click Cancel to close the screen without making changes.

Exporting Parameters

You can export the parameters on to the portal which in turn will be stored in the repository and can be imported and used while designing other reports.

On the Parameters screen, click Export button. The Export screen appears.

Figure 4: Exporting a parameter

  1. Specify the name for the parameter.
  2. Click Export The parameter gets exported.

Note:  Exported parameters are stored in the repository and can be used while designing any other reports.

Parameters Form Layout

When a report has run time parameter(s), Input Parameters Form (IPF) appears on screen at report run time.  The layout of the IPF can be set on Parameters Form Layout screen.

Figure 5: Parameters Form Layout

To access this screen,

  • On menu bar, click Tools > Parameters Form Layout
  • On toolbar, click button.

Provide the following information:

  • Description: Description that should appear on Input Parameter Form when it is displayed at runtime.
  • No. of Parameters in a row: Number of parameters that should appear in a row on Input Parameter Form.

Enter 0 (Zero) for auto adjusting parameter form. When you enter zero, the engine decides the number of parameters that should appear in a row.

Click Add Script button to open Script Editor screen and write scripts.

At Parameters Details level (Form level), OnSubmit() event is supported. It means, script is executed when you click OK / Run button on Parameter Details screen.

If such a report is scheduled, Parameter Details screen is presented at the time of setting the schedule and script will be executed at the time of scheduling.

Script can access any parameter of the report. This includes parameter objects (even if not imported) and global business parameters. This will be Read-only access (parameter objects and global business parameters).

If all the parameter values are valid, it will return ‘True’. If one parameter value is invalid, it will return ‘False’ and report will not be executed. You can set an error message that should be displayed to the user in parameter value is invalid.

Note: In case of JavaScript error, Report Server will respond with ERROR.

Click OK to save the changes and close the screen.  Click Cancel to abandon the changes and close the screen.

When you preview the report in Studio having three or more parameters, Input Parameter Form having up to three parameters in a row will appear.

Figure 6: Report Parameters having description and three parameters in a row

The Report Parameters screen appears in the following cases:

  • When you open the report or refresh the fields.
  • When you click the Preview tab to preview the report.
  • When you click the OK tab of SQL Editor.

Using Report Parameters in SQL Editor

The parameter that you defined through the parameter window can be used under multiple options as required. In SQL editor you can use this parameter along with the column list as long as it conforms to the SQL syntax.

Important: Make sure that you use the parameter in the SQL enclosed between ‘<%’ and ‘%>’.

When you execute this SQL, the supplied parameter values will replace the parameter defined between ‘<%’ and ‘>%’ symbols. It is important to use single quotes while using these parameters. For example:

Select * from table1 where name = ‘<%prm_agencyname%>’

Using Report Parameters in Layout Editor

You can also use the defined parameters through the field window of the Layout Editor. To use the parameters, refresh this window (View > Refresh Field List) and drag the parameter fields to the Report Layout window in the Layout Editor.

Using System Parameters

You can use system parameters to display date and time, or other values on the report. Previously defined parameters will be visible in the field list window of the Layout Editor. You can simply drag them on the layout window.

The system parameters are global parameters that can be used in any report, as per the requirements.

Apart from the parameters defined by you, there a set of other system parameters that Studio supports:

  • SYS_DATE: Provides system date.
  • SYS_TIME: Provides system time.
  • SYS_LOCALE: Provides report output language.
  • SYS_REPORT_FORMAT: Provides Report output format.
  • SYS_REPORT_ID: Report ID of currently executing report.
  • SYS_CATEGORY_ID: Provides the category under which the report is running.
  • SYS_REPORT_NAME: Provides the name of the report name stored in repository.
  • SYS_USER_PARAMS: Provides the string of user parameters with values.
  • SYS_SORT_PARAMS: Provides the string of sort parameters.
  • SYS_REQUEST_ID: A unique ID allotted by report server to the report.
  • SYS_PAGENO: Current Page no.
  • SYS_FILTER_PARAMS: All filter parameters and values.
  • SYS_GROUPBY: All “group by” parameters in Adhoc reports.
  • SYS_USERID: The user ID using which user logged into Studio.
  • SYS_ORGID: The ORG ID of the user who has logged into Studio.
  • SYS_FIRST_RECORD: Returns -1 if current record is the first record in result-set. Returns 0 for other records.  This is useful to check “First record” or “Not first record” conditions during scripting.
  • SYS_LAST_RECORD: Returns -1 if current record is the last record in result-set. Returns 0 for other records.  This is useful to check “Last record” or “Not last record” conditions during scripting.