IBM Support

How to filter a report using a comma-separated string entered in a text box



This document outlines a technique that allows users to enter a comma-separated string of multiple values that can be passed to a report parameter as a list of multiple items (essentially behaving as a mult-select prompt.) Text box prompts in IBM Cognos BI have a special 'Multi-Select' mode that allows users to enter multiple values to pass to a parameter in a report. To use a multi-select text box prompt, a user has to enter a value and hit an 'Insert' button to take a just-entered value and add it to a list of items that is passed to the report parameter. In some cases, a more efficient way to pass multiple text strings to a parameter in a report is to enter comma separated values such as, "Camping Equipment, Mountaineering Equipment, Golf Equipment" rather than entering each value and hitting the "Insert" button in a multi-select text box prompt. There is no direct way to have a text box prompt handle a comma-separated value string, but we can create this behaviour by using some query macro functions. [Note that as of the writing of this document, this technique does not work with dimensional data sources like cubes or DMR models.]

Resolving The Problem

Use a single-select Text box prompt in the report, and use one of the following expressions in the filter expression of the prompt-filtered query. Replace "[Data Item to be Filtered]" and "Parameter" with values that are specific to your report:

  1. Use this expression if the values users will be entering have spaces in them. The report will not filter correctly if users put spaces between the comma-separated values (ie. "Fax, Telephone" instead of "Fax, Telephone"):
    [Data Item to be Filtered] in (#csv( split(',', prompt('Parameter', 'token') ) )#)
  2. Use this expression if the values users will be entering do not have spaces in them. This expression will remove spaces entered by users between the comma-separated values:
    ((#sq(prompt('Parameter', 'token', 'testValue'))# <> 'testValue') and [Data Item to be Filtered] in (#csv( split(',', substitute(' ','', prompt('Parameter', 'token')) ) )#) ) or ((#sq(prompt('Parameter', 'token', 'testValue'))# = 'testValue'))

These expressions take the comma-separated value string entered by the user and split it up into a valid multi-item expression that can be used with the 'in' operator in a Cognos BI fitler expression.

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSTSF6","label":"IBM Cognos Analytics"},"ARM Category":[{"code":"a8m500000008d78AAA","label":"Reporting"}],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions","Type":"MASTER"}]

Document Information

Modified date:
13 October 2021