Multiselect Value Prompt with + Separator

[Nagendar]

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

[Andrei I]

Why can't you simply replace "," with " - " ?

[Nagendar]

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

[Andrei I]

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

[Nagendar]

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

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.

[Andrei I]

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

[Nagendar]

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.

[Andrei I]

Actually you could use macro functions only without need for DB replace
[prompt label] :=

Code Select Expand


# 
csv(
split(';',
sq( promptmany('par_Country','token')  )
)
, ' + ', '')
#

[Nagendar]

Actually you could use macro functions only without need for DB replace
[prompt label] :=

Code Select Expand


# 
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  :)