A ROLAP report, or a report receiving data from the database at the time of its execution, can be significantly reduced in size with the help of parameters, which make it possible to filter the original query in the DBMS. Parameters are variables the value of which is requested from the user when the report is executed and then passed along to the Query. Besides decreasing the amount of data dealt with, parameters also make it possible to modify the content of the report in accordance with the criteria chosen by the user.
A report can contain several parameters of different types, such as number, time period, string, list, etc... To create a parameter you must first define it in your Query and add it to your report. A parameter can be defined by default, and can be assigned manually or as a relative parameter, for example, as final year, quarter or month, yesterday, or as a result of a query.
One of several possible comparison operators is assigned for a parameter, such as equal, more, less, range, etc... The conditional command is assigned by the report author.
Parameter Values can be freely input by the user or chosen from a list or from queries created by the report author. This makes it possible to supply the user with a list of allowed values definitely contained in the data, eliminating, for example, goods that were not sold during the time period in question.
Parameters can be "Non-Interactive", which means that the user does not see them and cannot change them. Non-Interactive Parameters are employed by authors to create multiple reports based on a single query.
Below we describe the creation of an example report and its parameters in the Contour BI Portal.
In this example, we create a Eurostat database, table Employment in high-tech sector for NUTS 2 regions. When creating the database, select the Eurostat database option from the list. Different tables are loaded automatically; select the respective table(s). For this table, we will create a Parameter for the Country field.
First we create the parameter in the Query:
Then add the parameter to the Report
- Choose an existing query under the Queries object in the project tree panel
- Select entry Parameters under the query
- Click on the Add parameter button from the toolbar or from the local menu of the Query
- A dialog is displayed. Specify the following values:
- Caption - Parameter title
- Code - A code to reference the parameter
- Source - Can be of two types: Field or Placeholder. The type is Field for automatic queries. Select type Placeholder for manual queries
- Field - Displays data source fields list
- Type - Displays the field type of the selected field
- Reference - Select from a list of previously created References
- Interactive - On (checked) = any changes result in parameter regeneration. False otherwise
- Comparison operator – (at manual input)- Comparison operator of a filtered field with a parameter value (=, <>, LIKE, NOT LIKE, IS NULL, IN)
- Value - Default parameter value. Specify manually or select from a list of values by clicking on button Select from list
- Click button Select parameters from the project-panel toolbar:
- The displayed dialog allows one to select from the list of parameters by moving them from one panel to another
- Once the parameter is added to the Report, its default value is taken when running or editing the report – if the Interactive field is unchecked.
- If Interactive property is On, a dialog will be displayed to type the parameter values:
The value can be directly typed in the box or selected from a list of values when you click over Select from list button.
The comparison operator can also be specified by selecting one from the dropdown list
- In this example we want to display Employment in high-tech sectors for the years 2010 and 2014. Then we select comparison operator In, and from the list the two years:
- In the Contour BI Portal, specify in the url the project or report name. Optionally, the parameter code and value.
In this example, the url contains only the project name. The parameter default value (specified in Contour Reporter) is displayed requesting confirmation.
User can accept the values and click the Apply button to view the report, or new values can be typed in the box: