Using Parameters and Filters

This chapter explains the usage of filters and parameters in Data Sets and the Expression Builder.

Filters and Parameters in Data Sets

For each data set, you may specify filters and parameters in the corresponding property pages.

Parameters
 

Filter

Parameters are used to parameterize the query which yields the result set of the data set. Filters are used to filter this result set after the query has been performed. The set of possible parameters depends on the type of the data set. Filters can be specified arbitrarily on the columns of the result set.

Wherever large amounts of data are processes, you should attempt to use the parameters preferably, because this allows the reporting engine to apply intrinsic tuning mechanisms e.g. on database level. Filters should only be used to apply a "final filtering" on an already small result set.

Using Report Parameters in Data Set Parameters and Filters

After having specified a report parameter in the BIRT report designer (in the Data Explorer right-click Report Parameters and select New Parameter).

Report Parameter Creation

you may use it as a parameter or filter in your data sets for Stardust ODA data sources by using the expression browser

Expression Builder

and selecting the desired report parameter (or a more complex expression) as the parameter or filter default value:

Parameters with Report Parameter

You may also specify the report parameter value as a filter value for tables in your report overwriting the default settings of the data set.

Parameter Data Types

Parameter data types are of type String with restrictions depending on the specific parameters as described in the following sections:

Boolean Data

Parameter values for process data of boolean data type are represented as 'true' or 'false'.

In the Parameters property page, the value for a boolean type can be set to 'true', 'false' or can be left empty by adding only the two quotes ('').

Note
In case UseOuterJoins is to 'true' in the Parameters property page and processes exist having boolean data not yet initialized, the result view page displays for this data the value 'false'.

Time, Date and dateTime Data

Parameter values for process data of time, date or dateTime data type are represented in the following formats:

Date Type Implemented Format Example
primitive date of type Timestamp
  • 'yyyy-MM-dd HH:mm'
  • 'yyyy-MM-dd hh:mm a'
  • 'yyyy-MM-dd HH:mm:ss'
  • 'yyyy-MM-dd hh:mm:ss a'
'2011-01-24 2:02'
struct data element of type xsd:dateTime
  • 'yyyy-MM-dd HH:mm'
  • 'yyyy-MM-dd hh:mm a'
  • 'yyyy-MM-dd HH:mm:ss'
  • 'yyyy-MM-dd hh:mm:ss a'
'2011-01-24 2:02'
struct data element of type xsd:date 'yyyy-MM-dd' '2011-02-17'
struct data element of type xsd:time
  • 'HH:mm'
  • 'hh:mm a'
  • 'HH:mm:ss'
  • 'hh:mm:ss a'
'14:02'

Using NULL Values for StartTimestamp and EndTimestamp Parameters

You can use a null value for StartTimestamp and EndTimestamp parameters. In that case these parameter values are replaced in the following way:

Expression Builder

In the View Selection pane enable the Use expression builder option and select the Expression builder... button.

The Expression Builder window opens with an expression field, operator buttons and panes for the different categories and parameters. The Category pane offers the two categories:

In the third pane on the right side you find the parameters, which can be copied to the expression field. For example select a Foreign Data called col_num, created in the View Selection before.

Double-click one of the parameters to copy it in the expression field, in our example "StartDate".

Note, that if you use parameters of the type class java.sql.Timestamp, you have to add call methods like getTime() or toString() to them. Otherwise you get the following warning:

Add the getTime() method to the "StartDate" parameter:

Use the operator buttons provided in the dialog. For example to build an expression to compare with the values between start date and end date of the dataset, use the BETWEEN button and enter the "StartDate" and "EndDate" parameters. Don't forget to add the call methods. You would then have an expression like the following:

 col_num BETWEEN  ${DataSetParameter.StartDate.getTime()}  AND  ${DataSetParameter.EndDate.getTime()}

Usage of Report Parameters

The direct usage of parameters in the Expression Builder is only possible with DataSet parameters. If you like to use report parameters (see section Filter and Parameters in DataSets) instead in addition, assign the report parameter to an unused DataSet parameter. This is possible, because once the Expression Builder is activated, all process data and foreign data DataSet parameters are not included in the query.

Boolean Data Parameters

Parameters representing boolean data can be used in the Expression Builder. Here, they have to be entered without using quotes. Please see an example in the following screenshot:

Time, Date and dateTime Data Parameters

Parameters representing time, date or dateTime data, can be used in the Expression Builder by using the same formats as described in the section Time, Date and dateTime Data.

Using Materialized Query Tables

To manage the usage of sub-queries via replacement by MQTs (Materialized Query Tables) you can set the following properties in your carnot.properties file, residing in the the Stardust Eclipse plugin etc folder:

Using the optional predefined column StartingProcessID, it will be selected via sub-query from the database. To accelerate the process, you can create an MQT, which displays the sub-query as table already materialized. This MQT can then be used with the CARNOT.Reporting.MQT.SubQueryId.StartingProcDef parameter. Therefore the following value will be assigned to this parameter: [<schema name.>]<table name>. If no schema name is specified, the default schema of the reporting DataSource is used.

The other four properties will be used in a similar way in the following hierarchical schema:

CARNOT.Reporting.MQT.SubQueryId.SubPiActiveTime
CARNOT.Reporting.MQT.SubQueryId.PiActiveTime
CARNOT.Reporting.MQT.SubQueryId.AiActiveTime
CARNOT.Reporting.MQT.SubQueryId.AiLogActiveTime

Replacing a high-level sub-query will implicitly replace all subsequent sub-queries.

Please refer to our support team (ipp.ticket@sungard.com) for information on the usage of these parameters according to the specific requirements of your company.