6.3. Working with Parameters

By setting parameters you can specify additional configuration options for the users. First, we want to discuss the basics of parameters based on a simple example. Then we will look more closely to the various parameter types.

In the following we will again use the supplied demo data and set up a report on the orders of the company "1 to 87". Here we want to enable the users of the report to restrict the data basis to certain customers. To do this, we will create a new dynamic list, select again the demo data source (refer to Chapter 4.), and first apply the following basic query:

SELECT * FROM T_AGG_ORDER

Run the report in its actual state to get better acquainted with the demo data. In the following we would like to introduce a parameter which enables the users to filter the report by customer numbers. To do this, we return to Report management, select the report and switch to the parameter tab (at the bottom). From the tool bar we add a new data source parameter. The parameter properties will open by double clicking on the icon of the parameter. We will assign the following properties:

Name: Customer
Description: Selection of the customer to be displayed only.
Key P_CUSTNUM

Now we switch to the Specific properties of the parameter. Here we enter/activate the settings valid for the parameter type (data source parameter). Data source parameters draw their data from a data source. Here we enter the demo data source again. ReportServer expects a result giving two columns: the value column (this value can later be used in the query) and the display column (this column will be shown to the user on selection). We place the following query.

SELECT DISTINCT
    OR_CUSTOMERNUMBER, CUS_CUSTOMERNAME
FROM
    T_AGG_ORDER
ORDER BY 2

You may keep the default values of the other settings. Apply the settings by clicking on Apply. If you now run the report (double click in the tree) you will discover that the aspect displayed first is the page Report parameters, and no longer Lists configuration. To set the parameter data, double click into the corresponding data grid.

Now we added a parameter, and it can be configured by the users, but so far this parameter had no effect on the underlying data volume. We still have to embed it in the base query of the report. To do this, in Report management we return to the report settings and modify the query as follows:

SELECT * FROM T_AGG_ORDER WHERE $X{IN, OR_CUSTOMERNUMBER, P_CUSTNUM}

ReportServer interprets the syntax $X{IN, OR_CUSTOMERNUMBER, P_CUSTNUM} as an IN-Clause where the field OR_CUSTOMERNUMBER must correspond to a value selected in the parameter P_CUSTNUM (key of the parameter). Translated to SQL, the query would be as follows:

SELECT 
    *
FROM 
    T_AGG_ORDER 
WHERE OR_CUSTOMERNUMBER IN ('WERT_1','WERT_2','WERT_3')

Now, if you execute the report again, you will discover that when selecting the parameter it will have the desired effect on the data volume. Please observe that if no parameter is selected, this will by default translate in "All values are valid".

If you are familiar with the JasperReports report engine, you will find out that you can use the parameter syntax applied there for ReportServer dynamic list as well. We will discuss the syntax more closely at a later point.
6.3.1. The Parameter Types

In the following we will first introduce the parameters supported by ReportServer individually and then look at them in detail. ReportServer distinguishes two types of parameters. True parameters are the ones that enable the user to make settings. So-called separators enable the administrator to design the aspect report parameter, i.e. to add descriptive texts, etc.

The following parameters are available:

  • Text entry parameter Enables the user to make an entry to a text field
  • Date parameter Enables to enter a date or a time field
  • Data source parameter Enables to select from a number of possible pre-set values
  • User variable Enables to readout so-called user variables. User variables are discussed in detail in Chapter 8.
  • File-selection parameter Allows users to upload files, which can then be used in the report creation process. This is especially powerful in combination with script reports or custom export targets.
  • Script parameter Allows you to specify a parameter in HTML and JavaScript. This gives you the flexibility to master almost any requirement.

The following separators are available:

  • Display text Enables to display text
  • Heading Enables to integrate a sub-heading
  • Separator Enables to set a division

In the following we will present a close look at the individual parameter type settings. Then we will explain how to use parameters in data sources.

6.3.2. General Usage of Parameters

You add further parameters or separators to a report via the respective buttons in the tool bar in tab Parameters. By clicking on the Remove button you can remove either the selected or all report parameters. In addition, by using copy & paste you can add parameters to your current report or to another one. To copy one or more parameters to the Clipboard, activate the respective parameters and press CTRL+SHIFT+C (you will get a short notice about the successful move to the Clipboard). To add parameters, first click on the parameters list (if there is no parameter available click on the header), then press CTRL+SHIFT+V.

To edit the parameter properties, double click on the parameter icon, or activate the parameter and select "Edit" from the tool bar. Parameter name and key can directly be edited in the list. To do this click on the respective cell.

There are properties specific to each parameter type, but all parameters have the following properties in common:

Name: Plain text name. Visible to the user.
Description: Description of the parameter. Visible to the user.
Key: A technically unique name. It is used to access the parameter from the data source.
Hidden: Indicates whether users can see the parameter in the parameter page or not.
Editable: Indicates whether users are allowed to modify the parameter.
Mandatory Whether or not the parameter is mandatory.
Display inline: The option Display inline controls the layout of the parameter page. By default parameters are displayed in a block. This means that line feed ends each parameter which results in the parameters being displayed one below the other. If a parameter is displayed inline, no line feed will be inserted. This enables to position the parameters next to each other.
Label width: Allows to set a width for the label (i.e., name and description). The label width is inherited by all following parameters unless it is overwritten there. To set the label width to auto (which is the default) set it to -1
6.3.3. Parameter Instances

When a user allocates parameters and saves their configuration in a report variant, the parameter settings made will be stored in parameter instances. If you modify a parameter subsequently, the instances of the variants will not automatically change. This can be best explained by giving an example:

We assume that we have created a multiple choice list parameter following the above example. The appurtenant basic query was as follows:

SELECT * FROM T_AGG_ORDER WHERE $X{IN, OR_CUSTOMERNUMBER, P_CUSTNUM}

In the meantime, some variants have already been created, and the users have edited the variants' parameter P_CUSTNUM. If you modify the parameter definition so as to select only one value, and adapt the query as follows:

SELECT * FROM T_AGG_ORDER WHERE OR_CUSTOMERNUMBER = $P{P_CUSTNUM}

This would cause problems with the existing variants as here a list of values is given to the query and not only a single value. In this case, by applying the button "Adapt instances", you could reset the instances for the selected parameter to the initial value. However, this is not necessary for all parameter modifications, and as a reset will lead to the loss of the user's selection, ReportServer will not automatically delete the instances with every parameter change. Let us assume you did not set the parameter from multiple choice to single choice, but you only rearranged the query to offer less selection options. Here it is not necessarily required to adapt the existing instances.