Passing Multiple parameter values into one string

[Good Friend]

Hello Friends.

I have date stored in the database for a column and prompt values in the below format with varchar data type.
Let's say if user chose 2162 and 2172 from the below prompt values, the database column should filter out and show the rows 1,2,4 because 2162 is present in 1,2 & 4th row and 2172 is in 1st and 4th row. Let's say if user chose 2182, then 3rd and 4th row should be shown.Problem here is passing multiple values into IN Statement in the filter criteria doesn't seem to be working in report studio. LIKE Statement and other ways I tried also doesn't seem to be working. Any insight guys on how to proceed on this.Please share your thoughts. Let me know if it is unclear and I can add additional info. Thanks.

Drop Down Prompt Values                               TCode (Database Column Values)     
2162                                                             Row1:   2162,2172
2165                                                             Row2:   2162
2172                                                             Row3:   2162,2182
2182                                                             Row4:   2162,2165,2172,2182,2192
2192                                                                 

                             

[hespora]

Hi there,


I don't see that working without some serious manipulation of the prompt with macros or other workarounds. Both 'contains' and 'like' operators are just expecting one string expression, not an expression list that a multi-select prompt would return.

The easiest way, if that is a feasible option, would be multiple prompts if it is realistic that a user would only ever select a limited set of values. So speaking from your example: Should a user always only select one or two values? Or is it expected that they would select ten values as well?

[Good Friend]

Thanks for your reply. User can actually select multiple values at this moment. But if there is a limited feasibility, I can suggest the same.Can you please throw some light on how you are getting this done with multiple prompts or macros.Thanks again.

[CognosPaul]

The manipulation isn't that hard.

Code Select Expand

[Namespace].[Dimension].[Field] like
+ #
join('%'' or [Namespace].[Dimension].[Field] like  ''%',
  split(';',
    sq(promptmany('searchString','token','','%','','%'))
  )
)#

Flow is:

Prompt on the search string, add % to the beginning and end and wrap in single quotes.
So a single value search would look like: '%abc%'
multi: '%abc;def%'

split on ';' into an array.
join back on %' or [Namespace].[Dimension].[Field] like  '%
single: '%abc%'
multi: '%abc%' or [Namespace].[Dimension].[Field] like  '%def%'

and then put "[Namespace].[Dimension].[Field] like " in front.

single: [Namespace].[Dimension].[Field] like '%abc%'
multi: [Namespace].[Dimension].[Field] like '%abc%' or [Namespace].[Dimension].[Field] like  '%def%'

[Good Friend]

  .

[CognosPaul]

From what I understand the TCode in your database contains the date keys in as comma separated values. In order to search for individual values you need to perform some sort of search.

Code Select Expand

where
  mydim.tcode like '%2165%'

should return row 4 from your example.

Code Select Expand

where
  mydim.tcode like '%2165%' or mydim.tcode like '%2172%'

will return rows 1 and 4.

The trick is to build your filter to replicate the aforementioned where clause. In my expression, the following functions were used:
promptmany
split
join

promptmany will allow a user to return 1 or more selected values to a query. The values are separated with semicolons. The value formats are controlled by the datatype specified. promptmany('param','string') will return a value that looks like:

Code Select Expand

'value1';'value2'

In that case we can't use it because of the generated single quotes.

The token datatype returns the value as is:

Code Select Expand

value1;value2

since there is no such thing as a multiple like clause, we need to find a way to split the multiple values selected into their own expressions. In my example a combination of split and join works well to create the necessary expressions. Try it, replacing the necessary fields as needed.

[Good Friend]

Thanks a ton Paul.

[dougp]

Cognos Paul:
Once again, excellent work!  I was able to make appropriate substitutions and change one word (I'm sure you know which one) to meet Good Friend's stated requirement.  Another tip to keep in my back pocket for when a user needs it.

And I hope I'm not out of line setting myself up to look like a moderator, but...

Good Friend:
Many of us here really love coding and making Cognos do interesting things, but some topics take on a life that looks like we're being asked to do someone's job for them.  This topic took that turn with your last question.
Open the expression editor then find and click on the promptmany macro function.  The help will display in the Tips tab of the Information part of the dialog.  Alternatively, go read the actual help docs.  If you still have questions after reading the documentation, don't hesitate to post again.

[hespora]

The manipulation isn't that hard.

Oh dear... yeah, I had a completely different approach in mind, but this is much more elegant...

I'll go hide in a corner now. ;)

[cognos79]

Thank you CognosPaul for the macro code. That's smart fix!  I have similar ask with XML datasource.

Column B
Abc,EDf, Xsd, DFG, SMm,Xvf (some of these are with spaces after comma)

I create static choices for the 6 values and tried this macro as suggest above it validates and works but when rerun gives me this error below -
RQP-DEF-0177

      An error occurred while performing operation 'sqlScrollBulkFetch' status='-232'.

     DetailsExpand
UDA-SQL-0528 The XML parser returned the following message: "General XMLParser Error!".UDA-CUR-0000 Unable to fetch the row.RSV-SRV-0042 Trace back:RSReportService.cpp(733): QFException: CCL_CAUGHT: RSReportService::processImpl()RSReportServiceMethod.cpp(259): QFException: CCL_RETHROW: RSReportServiceMethod::process(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(864): QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(319): QFException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(909): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): asynchRunSpecification_RequestExecution/RSRenderExecution.cpp(600): QFException: CCL_RETHROW: RSRenderExecution::executeAssembly/RSDocAssemblyDispatch.cpp(340): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSLayoutAssembly.cpp(79): QFException: CCL_RETHROW: RSLayoutAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(446): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSReportPagesAssembly.cpp(185): QFException: CCL_RETHROW: RSReportPagesAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(384): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSPageAssembly.cpp(314): QFException: CCL_RETHROW: RSPageAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(384): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableRowAssembly.cpp(177): QFException: CCL_RETHROW: RSTableRowAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(384): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableCellAssembly.cpp(151): QFException: CCL_RETHROW: RSTableCellAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(446): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSTableAssembly.cpp(119): QFException: CCL_RETHROW: RSTableAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(446): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSTableRowAssembly.cpp(177): QFException: CCL_RETHROW: RSTableRowAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(384): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableCellAssembly.cpp(151): QFException: CCL_RETHROW: RSTableCellAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(446): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSDocAssemblyDispatch.cpp(384): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyRSResultSetIterator.cpp(1506): QFException: CCL_RETHROW: RSResultSetIterator::retrieveDataQFSPartialDataset.cpp(80): QFException: CCL_RETHROW: QFSPartialDataset::GetEdgeIteratorSource/QEDmResultSet.cpp(75): QFException: CCL_THROW: QE

I have looked up for the error and looks like could be related to execution time limit. I have to reach out to the concerned team for the logs.
I have another field (Column D)which has almost 25 comma separated string values. I am trying to have this logic in a dataitem to use for prompting and avoid creating 25 static choices. Is it possible.

Thanks in Advance !!