Creating report filters in Cognos

When you create a Cognos report, you may need to provide the people who run the report the ability to filter the results, rather than selecting all the data in the application. Using Cognos Report Studio, you can create a variety of filters. Unica Marketing Operations users frequently want filters that do the following.

Filter by an object’s name or code

Filter by when an object is active

Filter by an object’s status and/or type

As a best practice make filter prompts optional, rather than required. Optional filters are simpler for end users running the reports.

Best practices for name and code searching

As a best practice, use a Select & Search prompt to enable users to filter a report based on an object name or object code. The Unica Marketing Operations data items use the naming scheme [PlanBV].[<Object>].[<item>]. (For example, the data item for project IDs is [PlanBV].[Project].[ProjectID].)

When you create the Select & Search prompt, you can specify one type of value to display to the user and another to use to search the database. For example, the following prompt control configuration prompts users for the project name or code but searches using project IDs, which is generally a faster search.

Values to use: [PlanBV].[Project].[Project ID]

Values to display: [PlanBV].[Project].[Project Name (Code)]

Best practices for date searching

In order to create a date filter in Cognos that returns any object that is active during a certain date range, exactly like Advanced Search in Unica Marketing Operations, use a date prompt with the range option enabled and create a filter that includes both the start date and the end date. This returns objects that meet any of the following criteria.

Start within the active date range

End within the active date range

Start before the active date range AND end after the active date range

The following filter searches for projects active during the date range entered in the date prompt named Target_Date_Prompt.

[PlanBV].[Project].[Project Start Date] in_range ?Target_Date_Prompt? OR [PlanBV].[Project].[Project End Date] in_range ?Target_Date_Prompt? OR
([PlanBV].[Project].[Project Start Date] <= ?Target_Date_Prompt? AND
[PlanBV].[Project].[Project End Date] >= ?Target_Date_Prompt?)

Best practices for object status and type filters

Because there is a small, fixed set of status and types, use a simple multi-select control for filtering on status or type.

In order to prompt users for an object’s status and/or type do the following.

To prompt for status, use a a multi-select control using the <OBJECT> Status Query Subject.

To prompt for type, use a multi-select control using the <OBJECT> Template Query Subject.