How to remove all spaces from ParamDisplayValue('Para1') when selecting multiple

[lookingforK]

Question: How to remove all spaces from a string generated from ParamDisplayValue('Para1') when selecting multiple values?


I create a prompt page with a parameter 'Para1' that enables multiple values selected and is supposed to be part of a dynamic title and .

I add a Layout Calculation for the title, and use TRIM function to trim ParamDisplayValue('Para1'), i.e.:
trim( 'trailing', ' ', ParamDisplayValue('Para1') ).

But only the trailing spaces for the last value are removed.

For example, when running the report and selecting multiple values (e.g. 0001   ,0002   ,0003   ) for the parameter 'Para1', I get: 0001   ,0002   ,0003 (no trailing spaces here for 0003)

Which function can I use to remove all spaces from the string generated from ParamDisplayValue('Para1')?

Thank you in advance

[blom0344]

You are distributing the same issue over multiple posts, that will not help much, just confuse matters. Keep it within the same post please..

[Lynn]

Do you have a query that is feeding your prompt control? If so then just use a database trim function around the query item.

[lookingforK]

Thank you Blom0344.

Did you mean this post against the other one "How to trim ParamDisplayValue('Para1') that shows trailing spaces? "?

If so, ... they are different topics.

The previous one is for a parameter that enables single value selection, and the new one is about a parameter that enables multiple values selection.

[lookingforK]

Do you have a query that is feeding your prompt control? If so then just use a database trim function around the query item.

Thank you Lynn.

In this case, TRIM can't work because some spaces in middle of the string, e.g.
* Select multiple values (e.g. 0001   ,0002   ,0003   ) for the parameter 'Para1' and use trim( 'trailing', ' ', ParamDisplayValue('Para1') ), get: 0001   ,0002   ,0003. You can see here, some spaces in between 0001 and 0002, and in between 0002 and 0003. TRIM function only trim the leading or trailing spaces (e.g. here for 0003).
* The desired result should be: 0001,0002,0003

[Lynn]

I understand the desired result. Your users get to select those multiple values from somewhere. What is it that allows a user to select those values in the first place?

I was guessing that you have a query that feeds your prompt control. I suspect that query returns '001   ', '002   ', etc. so that your param display strings those together with the embedded spaces between. If you try to trim at the point of param display then you are too late because the blanks are already baked in throughout.

I am suggesting you edit the query item expression for that value from the query that feeds your prompt control. So if it says [SomeNamespace].[SomeQuerySubject].[SomeQueryItem] then you can change it to put a trim function around it. Not sure if your source is relational or dimensional.

[lookingforK]

Thank you Lynn.

The source is relational.

I fix this problem using your method. Bravo ...

[Lynn]

Outstanding!

[yogeswari]

Hi Lynn,

I could not understand the answer provided.  So only i am asking this.  I too have the same issue.

I am value prompt with Static Choices;Multiple Selections.  The values (Eg: 001,002,003) both as Use value and Display value.

When i am selecting the parameter and displaying the values i am getting 001, 002, 003.
But resultant should be 001,002,003.

Please provide ur suggestion.

Thanks,
Yogeswari

[Lynn]

Not sure there is much you can do in your case. The space following the comma seems to be part of the delimiter and has nothing to do with the values themselves.