Adding Studio Reports Parameters

Field

Description

Name

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 typeInput toolsValidationsCHARText box, list box, multi select list boxAlphabets, numbers, or special characters are allowedNUMBERText boxNumbers are allowedDATEDate, date part, variable dateValid date according to selected input format

Predefined data variables such as MONTH_START_DATE, YEAR_START_DATE, and CURRENT_DATE

BOOLEANCheck boxSelect or de-select

Size

Format

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

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

Select the format for prompting the parameter.

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

• 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

Mandatory

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 TypeControlEdit textTextbox control with type in cursorDropdownMulti-select list boxOptionsRadio buttonSliderRange 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).

To set pre-defined values:

1. Under Dropdown Source, select Pre Defined option.

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  button to add it in list. Repeat the steps 2 and 3 for each option.

5. Click  button to delete a value.

6. Click  button to modify the value.

To set dynamic values:

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

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

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

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

Display Column

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

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.

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

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.

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

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.

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.

Tree View

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

Text box view

• Indore

• Bhopal

• Bangalore

Tree View

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.

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

When Flat is selected,

An empty row will be auto-appended once you complete with present level entry. Click  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.

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.