Hi Team,
We have a report with multi select value prompt, when user selects values ex: India, UK & USA,
we are displaying these selected country names in report page using layout calculation.
Cognos by default is giving India,UK,USA
we are Expecting result as: India + UK + USA
Environment : Cognos Analytics 11.0.12
SQL Server
Relational Package
Could someone help me to achieve this.
Thanks
Why can't you simply replace "," with " - " ?
Quote from: Andrei I on 07 Jan 2020 09:49:20 AM
Why can't you simply replace "," with " - " ?
Hi Andrei I,
When I tried with replace its giving error saying that invalid expression
I have used like replace ( ParamDisplayValue('Parameter1') , ',','+') but this statement checks for , in those values with are displaying in value prompt.
Correct me if I am wrong.
Thanks
Did you try to use Macro functions" # csv( promptmany(), separator) #
https://www.ibm.com/support/knowledgecenter/en/SSEP7J_10.2.2/com.ibm.swg.ba.cognos.ug_cr_rptstd.10.2.2.doc/c_mac_csv.html#mac_csv (https://www.ibm.com/support/knowledgecenter/en/SSEP7J_10.2.2/com.ibm.swg.ba.cognos.ug_cr_rptstd.10.2.2.doc/c_mac_csv.html#mac_csv)
Quote from: Andrei I on 08 Jan 2020 03:50:53 PM
Did you try to use Macro functions" # csv( promptmany(), separator) #
https://www.ibm.com/support/knowledgecenter/en/SSEP7J_10.2.2/com.ibm.swg.ba.cognos.ug_cr_rptstd.10.2.2.doc/c_mac_csv.html#mac_csv (https://www.ibm.com/support/knowledgecenter/en/SSEP7J_10.2.2/com.ibm.swg.ba.cognos.ug_cr_rptstd.10.2.2.doc/c_mac_csv.html#mac_csv)
Hi Andrei I,
Thank you for your reply I have gone through the link which you have shared even I haven't got clarity on this
Value Prompt Query is [Country] in ?Parameter1?
Layout Calculation expression is : ParamDisplayValue('Parameter1')
Could you please help me how to write macro expression for the above Layout Calculation
Thanks you for the help.
It was actually a way more complicated.
I could not do it in the Report Expression.
Had to use a Query Calculation with Macro functions and DB Vendor replace.
Create 2 data items in the same query:
par_Country is the name of the parameter from the Prompt
// macro function promptmany returns string like this: 'United State';'Canada'
// so change it to 'United State + Canada'
[prompt value] :=replace( # sq( promptmany('par_Country') ) #, ''';''', ' + ' )
// drop extra quote characters (first and last)
[prompt label] :=substring( [prompt value], 2, character_length ([prompt value] ) - 2 )
See an attached report for details (C11, Cognos Samples)
Please confirm that it works for you
Quote from: Andrei I on 09 Jan 2020 11:11:19 AM
It was actually a way more complicated.
I could not do it in the Report Expression.
Had to use a Query Calculation with Macro functions and DB Vendor replace.
Create 2 data items in the same query:
par_Country is the name of the parameter from the Prompt
// macro function promptmany returns string like this: 'United State';'Canada'
// so change it to 'United State + Canada'
[prompt value] :=replace( # sq( promptmany('par_Country') ) #, ''';''', ' + ' )
// drop extra quote characters (first and last)
[prompt label] :=substring( [prompt value], 2, character_length ([prompt value] ) - 2 )
See an attached report for details (C11, Cognos Samples)
Please confirm that it works for you
Thank you a lot Andrei I,
Worked Perfectly.
Actually you could use macro functions only without need for DB replace
[prompt label] :=
#
csv(
split(';',
sq( promptmany('par_Country','token') )
)
, ' + ', '')
#
Quote from: Andrei I on 10 Jan 2020 10:42:56 AM
Actually you could use macro functions only without need for DB replace
[prompt label] :=
#
csv(
split(';',
sq( promptmany('par_Country','token') )
)
, ' + ', '')
#
Thank you again Andrei I, for your solution,
I have found similar macro function in other blog and tried those before posting this question, but didn't get expected result.
your solutions Worked Perfectly fine.
Thank you :)