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
Are all of the values going to contain the same pattern?
Take a look at this expression:
#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:
#sq(
join(','
substitute('\|','',
grep('\|',
split(' - ',
substitute(' - ','| - ',
split(';',
promptmany('p_ABC','token')
)))))
)
)#
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 )
#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
#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 !!!
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
[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?
[level1] = 123 OR [level2]=234 OR [level3]=345
Or can you get away with just:
[identifier] in (123,234,345)
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
Can you paste in an example mun? The syntax may be a little different based on the muns your cube generates.
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]
Try this:
[data item] in (
#
csv(
substitute('\[','',substitute('\]','',substitute('\|\|','',
grep('\|\|',
split('.',
join('||.',
split(';',
promptmany('p_ABC','token')
))+'||')))
)))
#
)
It did not work for me because it gave me the hierarchy whereas I am looking at the lowest level
Run this, and in the p_ABC prompt paste in the MUNs you're expecting. Then paste the results here.
#sq(
csv(
substitute('\[','',substitute('\]','',substitute('\|\|','',
grep('\|\|',
split('.',
join('||.',
split(';',
promptmany('p_ABC','token')
))+'||')))
)))
)#