How to filter a report using a comma-separated string entered in a text box ?
This technique will allow 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 ReportNet (CRN) and Cognos 8 (C8) 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 will be 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.
Resolving the problemUse 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:
Use this expression if the values users will be entering have spaces in them. Note that 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') ) )#)
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 8/CRN fitler expression.
Text box prompts in IBM Cognos ReportNet (CRN) and Cognos 8 (C8) 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 will be 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.
Resolving the problemUse 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:
Use this expression if the values users will be entering have spaces in them. Note that 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') ) )#)
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 8/CRN fitler expression.
Comments
Post a Comment