Now, the specified parameters have to be used in the report definition so that they will take effect in the report. How to do this varies with the report type and datasource. In the follow-through ing we describe the parameter use in the dynamic list. How to use the parameters in Jasper BIRT or script reports is given in each section dealing with the respective report type.

For the dynamic list, parameters are used in datasources. In the following we treat the individual datasource types and introduce how parameters can influence data selection.

For relational databases, parameters are used in the appurtenant query. Here you have basically two options to embed parameters in queries: either by query replacement or by direct replacement. In the first case, the query will first be processed and the replacements will only be integrated afterwards by verifying the data type. In the second case the replacement will be added prior to processing and, therefore, this changes the query to be processed. Let us have a look at the following sample query:

SELECT * FROM MY_TABLE WHERE ID = ?

In the example, an ID shall be given by a parameter. Now by replacing "?" in the query by a value it would be ensured that the transferred value (say ) is of the correct data type (in the example it is the same as the attribute ID), and only then the replacement will be made. In case of a direct replacement the query would be changed to

SELECT * FROM MY_TABLE WHERE ID = 1234

and will be processed only afterwards. The result is that such a query can maliciously be changed by skillfully selecting the parameter value (a so-called SQL injection attack http:// en.wikipedia.org/wiki/Sql_injection). Now, if a user enters for instance the value 1 OR 1=1 instead of a valid ID, the query would be changed as follows:

SELECT * FROM MY_TABLE WHERE ID = 1 OR 1=1

By using these techniques it might be possible that a user can display values to which it should not have access. Please consider that SQL injection examples often show the follow- ing attack: The user selects string "1; DROP TABLE MY_TABLE;" which would transfer to the following statement (respectively to the following two statements)

SELECT * FROM MY_TABLE WHERE ID = 1; DROP TABLE MY_TABLE;

ReportServer will intercept this attack by allowing a query to only include exactly one statement. Still, we want to emphasize that direct replacement represents a potential security risk, and, therefore, should only be applied with particular caution.

In the following we present you the various possibilities how to integrate parameter values in a query.

A parameter referenced in this way will be converted according to the standard behaviour of the parameter type and will be added to the query (query replacement) by applying the internal JDBC parameter mechanism. Quotation marks and special escape characters will automatically be set at the right place. $P{key} parameters can only be used as part of a WHERE condition. Our sample query would therefore be as follows:

SELECT * FROM MY_TABLE WHERE ID = $P{key_ID}

where key_ID is the key of a parameter which returns a single value of the ID attribute type.

Unlike the $P{} parameter reference, $P!{} parameter will directly be entered in the query by omitting the JDBC parameter mechanism (direct replacement). This enables to use this parameter type at positions in the query where no JDBC parameters are allowed. As demonstrated in the example, a parameter can, for instance, provide part of a source table name. Please consider that for this parameter type, quotation marks will not automatically be set or special characters escaped. In addition, by directly interfering in the query you run the risk that your report will be an open gate for SQL injection. Therefore, use this parameter type with caution. Example:

SELECT * FROM tblpfx_$P!{paramTblSuffix}

The $X{} parameter reference facilitates the use of SQL clause functions and provides an injection safe way of use. Furthermore it properly handles NULL values and is able to handle lists (for example, for IN or NOTIN clauses).

Functions supported are:

• $X{IN, column, parameterkey}: If all parameter values are other than NULL, an expression in the form <column> IN (?, ?, .., ?) will be generated. If the list of values includes NULL values as well as values other than NULL the generated expression is:

  (<column> IS NULL OR <column> IN (?, ?, .., ?))

  If all delivered values are NULL, the expression generated becomes <column> IS NULL.
• $X{NOTIN, column, parameterkey}: If all parameter values are other than NULL an expression in the form <column> NOT IN (?, ?, .., ?) will be generated. If the list of values includes NULL values as well as values other than NULL, the generated expression is:

  (<column> IS NOT NULL AND <column> NOT IN (?, ?, .., ?))

  If all delivered values are NULL, the expression generated becomes <column> IS NOT NULL.
• $X{EQUAL, column, parameterkey}: If the parameter value is other than NULL, an expression in the form <column> = ? will be generated. If the parameter value is NULL, the generated expression is <column> IS NULL.
• $X{NOTEQUAL, column, parameterkey}: If the parameter value is other than NULL, an expression in the form of <column> <> ? will be generated. If the parameter value is NULL, the expression generated is <column> IS NOT NULL.
• $X{LESS, column, parameterkey}: If the parameter value is other than NULL, an expression in the form of <column> < ? will be generated. If the parameter value is NULL, an expression always evaluating to true will be generated, e.g. 0=0.
• $X{LESS], column, parameterkey}: If the parameter value is other than NULL, an expression in the form of <column> <= ? will be generated. If the parameter value is NULL, an expression always evaluating to true will be generated, e.g. 0=0.
• $X{GREATER, column, parameterkey}: If the parameter value is other than NULL, an expression in the form of <column> > ? will be generated. If the parameter value is NULL, an expression always evaluating to true will be generated, e.g. 0=0.
• $X{[GREATER, column, parameterkey}: If the parameter value is other than NULL, an expression in the form of column >= ? will be generated. If the parameter value is NULL, an expression always evaluating to true will be generated, e.g. 0=0.
• $X{BETWEEN, column, lowerParameterkey, upperParameterkey}: If both parameter values are other than NULL, an expression in the form of (<column> >? AND column < ?) will be generated. If one of the two parameters is NULL, it will only be compared with the other parameter, the then generated expression will be <column> > ? or <column> < ?. If both parameter values are NULL, an expression always evaluating to true will be generated, e.g. 0=0.
• $X{[BETWEEN, column, lowerParameterkey, upperParameterKey}: If both parameter values are other than NULL, an expression in the form of (<column> >= ? AND column <?) will be generated. If one of the two parameters is NULL, it will only be compared with the other parameter, the then generated expression will be <column> >= ? or <column> < ?. If both parameter values are NULL, an expression always evaluating to true will be generated, e.g. 0=0.
• $X{BETWEEN], column, lowerParameterkey, upperParameterKey}: If both parameter values are other than NULL, an expression in the form of <column> > ? AND <column> <= ?) will be generated. If one of the two parameters is NULL, it will only be compared with the other parameter, the then generated expression will be <column> > ? or <column> <= ?. If both parameter values are {NULL}, an expression always evaluating to true will be generated, e.g. 0=0.
• $X{[BETWEEN], column, lowerParameterkey, upperParameterKey} If both parameter values are other than NULL, an expression in the form of (<column> >= ? AND column <=?) will be generated. If one of the two parameters is NULL, it will only be compared with the other parameter, the then generated expression will be <column> > =? or <column> <= ?. If both parameter values are NULL, an expression always evaluating to true will be generated, e.g. 0=0.

Parameters referenced by $X{} will also be added to the actual query via the standard JDBC parameter mechanism, they also include correct quoting and escaping just like $P{} parameter references, but they can also only be used in WHERE clauses.

The advantage of using $X{} parameter references over absolute $P{} parameters is the correct processing of parameters of the value NULL.

Beside the $P, $P! and $X replacements that we have already discussed, you are provided with the ${} and $!{} options to integrate parameters by using ReportServer ${} formula expressions. The exclamation mark effects the avoidance of the parameter mechanism (direct replacement) as it is the case with the $P!{} parameter.

The ReportServer ${} formula language builds on the Java-Unified-Expression-Language (http://juel.sourceforge.net/, https://www.jcp.org/en/jsr/detail?id=245 and http://www.oracle.com/technetwork/java/unifiedel-139263.html) and enables you to run simple operations as for instance date arithmetic, etc. based on the parameter values. Here, for each parameter the following replacements will be provided.

key	Allows to access the parameter value in the form as it was inserted in the query by entering $P{key}.
_key	Enables to access the Java object lying below the parameter instance (a sub-class of net.datenwerke.rs.core.service.parameters.entities.ParameterInstance which is designed to save the values selected by the user). Here, each parameter type can define differing functions to be executed on the parameter. The resulting values will then be entered to the query in the correct data type and, if applicable, in quotation marks.
__key	Enables to access the parameter definition object (a sub-class of net.datenwerke.rs.core.service.parameters.entities.ParameterDefinition which is designed to save the parameter settings). This enables to access, for instance, the name or other metadata of the parameter.

For Saiku Reports you can use parameters in the SQL tag of the Mondrian schema query as described for relational databases.

For CSV lists you can use parameters in the Wrapper query as described for relational databases.

For script datasources you can use parameters in the Wrapper query as described for relational databases. In addition, you can use parameters with the ${} Syntax in the script arguments.

In addition to the parameters defined per report, ReportServer adds a few special parameters to the parameter set of each report. These special parameters enable the report designer to access special properties.

The properties of the current user can be called up by using ${_RS_USER.xx}, as a substitute getFirstname(), getLastname(), getTitle(), getUsername(), getEmail(), getId() can be used.

Additionally, the $P, or $P! syntax can be used. Here, the following replacements are available.

• _RS_USER_FIRSTNAME
• _RS_USER_LASTNAME
• _RS_USER_TITLE
• _RS_USER_USERNAME
• _RS_USER_EMAIL
• _RS_USER_ID

The properties of the current report can be accessed by ${_RS_REPORT.xx}, and as methods you can access getName(), getDescription(), getKey(), getId(), and isVariant().

Additionally, the $P, or $P! syntax can be used. Here, the following replacements are available.

• _RS_REPORT_NAME
• _RS_REPORT_DESCRIPTION
• _RS_REPORT_KEY
• _RS_REPORT_ID

You can access the system's default locale as well as the locale of the current user via the replacements

• _RS_LOCALE_SYS
• _RS_LOCALE_USER

The following allows you to access the complete locale which is used, e.g. in Jasper reports, for localization.

• _RS_LOCALE

Values defined as global constants may be used in queries in the same way as parameters. For further information on this refer to Chapter 8. Global Constants.

Metadata which were defined at the report can be used by means of the parameter syntax. For further information on this refer to Section 7.13. Report Metadata.