Filtering dates

All date columns in the application database are timestamps. The fact that report parameter pages send date values to Cognos causes a problem when filtering data by dates. Syntax, such as [Date Column] between ?Start Date? and ?End Date?, in a filter generates the Cognos prompt page, as Cognos is expecting to compare timestamp values. The workaround is to cast the timestamp query item to the date datatype so that Cognos knows that dates are being compared, and expects a date to be passed. However, due to the casting of date columns, report queries can potentially be broken down into multiple queries and stitched in memory. Therefore, a different way to filter dates is needed.

The new approach is to cast the date value passed from the application to a timestamp by using parameter maps built into the metadata model, and filtering those against the timestamp query items in the metadata model. This approach does not use Cognos' CAST function. Instead, this approach uses database vendor specific functions that change date data types to timestamps. By using database vendor specific functions, Cognos now sends the entire query to the database.

Parameter maps are invisible to the user (they cannot be selected), but they can still be used if you use the correct syntax. Here is the syntax using the TIMESTAMP parameter map:

#$TIMESTAMP{ 'database_name' }# <date_value> #$TIMESTAMP{ 'database_name' + '.end' }#

Where the database_name parameter is one of these:

Oracle
MSSQL

For this to work, you must surround a passed-in date value with these two calls to the parameter map:


Key	Value
MSSQL	CAST_TIMESTAMP(
MSSQL.end	)
Oracle
Oracle.end

Note: The core reports that use this date filtering method do not specify the database_name parameter, as the core solution needs to be generic. In its place, core reports use the Cognos session parameter $account.parameters.DB_TYPE, which holds the database vendor name of the particular installation. This value comes from the com.workbrain.sql.DBServer.getDBType() method and is populated during authentication.

Finally, if the above TIMESTAMP method to alter value to timestamps is used, the question mark prompt generation syntax (for example, ?DateParam?) cannot be used as this syntax forces Cognos to determine what data type to expect. This syntax has been changed to a more robust Cognos method of defining a prompt:

#prompt ( 'param_name', 'data_type' )#

Note: There are other parameters that can be passed into this function. For more details on the Cognos #prompt(…)# function, see the Cognos Framework Manager User Guide.

These methods let you change the way dates are prompted for and filtered.

This method can cause problems:

cast([Namespace].[Query Subject].[Date column],date)
between
?Start Date?
and
?End Date?