Modifying the value stored in the parameter passed by a prompt

[Cognosnewbie26]

Hi,
I have a prompt p_ABC that contains a value like 1234 - WERTY and I want to extract the value '1234' to filter a list report of another datasource and the datasource has the column with only the numbers. Also it's a prompt that can have multiple values. Any help would be greatly appreciated

[CognosPaul]

Are all of the values going to contain the same pattern?
Take a look at this expression:

Code Select Expand

#sq(
csv(
substitute('\|','',
grep('\|',
split(' - ',
substitute(' - ','| - ',
split(';',
  promptmany('p_ABC','token')
)))))
)
)#

Pass the following parameters to it:
1234 - WERTY
1235 - ERTYU
1236 - RTYUI

You'll get this output:
'1234','1235','1236'

First around it splits the list of parameters into an array, then on each element in the array it replaces  " - " with "| - ", making it look like:
1234| - WERTY
1235| - ERTYU
1236| - RTYUI

Next it splits on " - " so we'll get the array:
1234|
WERTY
1235|
ERTYU
1236|
RTYUI

Next it greps "|", returning all the elements with the pipe. If the second half of your parameter values might have pipes, you should use a different delimiter.
1234|
1235|
1236|

Next it replaces the pipes with an empty string
1234
1235
1236

The CSV function will return comma separated string:
'1234','1235','1236'

If you just want it as numbers you could use:

Code Select Expand

#sq(
join(','
substitute('\|','',
grep('\|',
split(' - ',
substitute(' - ','| - ',
split(';',
  promptmany('p_ABC','token')
)))))
)
)#

[Cognosnewbie26]

Thanks @CognosPaul for your response. I am a big fan of your blogs specially the searchable tree prompts that I have implemented in our reports and I have worked with your friend Itzik as well. As my username suggests I just learnt Cognos on my own in the past 9 months.
Initially when I asked the question, I didn't realize that the parameter ( coming from tree prompt) contains the entire hierarchy and I had to look for the lowest level. I am able to do this by using the below piece of code that gets me the values in the format 123,XYZ,345,XYZ ( XYZ is basically the level that is also getting extracted )

Code Select Expand

#csv(array(substitute(' ','',join(',',split(' - ',substitute(']','',grep(']',split('||',join('||',grep('all',split('->',join('||',split(')',join('||',grep(']',substitute(';','||',substitute('\[','||',substitute(']\.','||',split('].',promptmany('Parameter2', 'memberuniquename','[SG&A Presentation Layer - DMR].[Cost Center Hierarchy].[Cost Center Hierarchy] in (', '', ')'))))))))))))))))))))#

source is :http://cognosandme.blogspot.com/2010/08/single-select-tree-prompt-on-relational.html

The above solution works for a single selection but doesn't work in case of multi selection.

I also tried the below code that gives me data in the format '123','XYZ','345' but it doesn't work at all

Code Select Expand

#csv(array(substitute(' ','',''''+join(''',''',split(' - ',substitute(']','',grep(']',split('||',join('||',grep('all',split('->',join('||',split(')',join('||',grep(']',substitute(';','||',substitute('\[','||',substitute(']\.','||',split('].',promptmany('Parameter2', 'memberuniquename','rootMembers([SG&A Presentation Layer - DMR].[Cost Center Hierarchy].[Cost Center Hierarchy] in (', '', ')'))))))))))))))))))+''''))#
I would really appreciate your help on this !!!

[CognosPaul]

I'm really glad that my posts have helped! It's always gratifying to hear things like that.

Can you post an example of the members you're getting? Also, are you expecting the users to select from multiple levels?

Let's say the users select the following

Code Select Expand

 
  [cube].[dim].[hier].[level1]->[123]
, [cube].[dim].[hier].[level2]->[234]
, [cube].[dim].[hier].[level3]->[345]


Are you expecting the filter at the end to look like this?

Code Select Expand

[level1] = 123 OR [level2]=234 OR [level3]=345

Or can you get away with just:

Code Select Expand

[identifier] in (123,234,345)

[Cognosnewbie26]

It am just using a single level to filter all those values and that level contains only numbers and so something like (123,345,456) should suffice.
Thanks again for your help

[CognosPaul]

Can you paste in an example mun? The syntax may be a little different based on the muns your cube generates.

[Cognosnewbie26]

Here is what it looks like:
[Cube].[Dimension].[Dimension].[Level 11]->[all].[Level 1].[Level 2].[Level 3].[Level 4].[Level 5].[Level 6].[Level 7].[Level 8].[Level 9].[Level 10]

[CognosPaul]

Try this:

Code Select Expand


[data item] in (
#
csv(
substitute('\[','',substitute('\]','',substitute('\|\|','',
grep('\|\|',
split('.',
join('||.',
split(';',

  promptmany('p_ABC','token')

))+'||')))
)))
#
)

[Cognosnewbie26]

It did not work for me because it gave me the hierarchy whereas I am looking at the lowest level

[CognosPaul]

Run this, and in the p_ABC prompt paste in the MUNs you're expecting. Then paste the results here.

Code Select Expand

#sq(
csv(
substitute('\[','',substitute('\]','',substitute('\|\|','',
grep('\|\|',
split('.',
join('||.',
split(';',

  promptmany('p_ABC','token')

))+'||')))
)))
)#