Filters in IBM Cognos® reports

As part of your design for a IBM Cognos® report, you can provide options to filter report results, rather than selecting all the data in the application. Using IBM Cognos® Report Studio, you can create various filters.

Unica Plan users frequently want filters that do the following.

  • Filter by the name or code of an object
  • Filter by when an object is active
  • Filter by the status, type, or both for an object

Make filter prompts optional, rather than required. Optional filters are simpler to use when the report is run.

Filters for object names or codes

You can include a Select & Search prompt to enable users to filter a report that is based on an object name or object code. The Unica Plan 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 by using project IDs. This configuration generally results in a faster search.

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

Filters for dates

You can create a date filter in Cognos® that returns any object that is active during a certain date range, exactly like Advanced Search in Unica Plan. To do so, use a date prompt with the range option enabled and create a filter that includes both the start date and the end date. This filter 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 that is 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?)

Filters for object status and type

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

To prompt users for the status or type of an object (or both), do the following.

  • To prompt for status, use a multi-select control that uses the OBJECT Status Query Subject.
  • To prompt for type, use a multi-select control that uses the OBJECT Template Query Subject.