MDX question

[LFC]

Hello All,
I was wondering if its possible to create a calculated field which returns concatenated value of the Member Caption(or MUN) + Tuple Value. I have dimensional data with powerplay cube as source.

My query has the following.

Query Item 1) Month ( from data dimension)
Query Item 2) State ( from Country dimension)
Query Item 3) Revenue( measure)
Query Item 4) Quantity ( Measure)

The calculated field i am thinking of should be concatenation of fields 1 thru 4. So it will be ( Month + '-'+ State+'-'+Revenue+'-'+Quantity)

So the result set would be

Month  State      Revenue     Quantity         Calculated Field
May    Florida    100              32             "May-Florida-100-32"


Thanks,

[navissar]

You could probably use a report expression for this. Drag in a layout calculation in a new column, and drag in the data items concatenated like this:
[month]+' '+[state]+' '+[Quantity]+' '+[revenue]

[LFC]

Thanks Nimrod. I am using powerplay cube as source so cannot concatenate the columns like you mentioned..throws an error that "invalid coercion member set to value" and cast the measure to string doesn't work either.

so was wondering its possible to do using MDX statement.

[torre2011]

You could use the CAPTION for the concatenation of the values.

So like this: caption([month])+' '+caption([state])+' '+caption([Quantity])+' '+caption([revenue])

Were you going to use these in any aggregations?

[navissar]

Do note I said "report expression" and "Layout calculation"  ;)

[LFC]

Cheers mate. Caption throws errors as well and trying to embed the Concatenated query as Report expression and Layout Calculation doesnt work either  :'(
any other suggestions?

[navissar]

You'll need to give us more to go on. When errors are thrown, do elaborate which errors and what do they read. When something "doesn't work", try to write down what exactly happens and how it differs from your expected result.

[LFC]

My apologies, below are the errors i am receiving.

using Caption throws the error that

"operands of '[month] + rolevalue('_memberCaption',[revenue])' cannot be converted to consistent dimensional object."

which i kind of understand as the value is numeric and i am not aware of MDX function to convert number to string.


when i use it as Layout calculation

"An error ocurred at or near the position '0'. The variable named '[Query1].[month]' is invalid"

[navissar]

Ah! Much better. The question is, where precisely are you using the layout calculation. It was my understanding you needed it within the context of a list (Of the perils of using a list over dimensional we can talk later). Basically, I had imagined the scenario attached in the screenshot (where also you could see that the layout calculation works...).
As a rule, the layout calculation needs to be in the context of a container (list, crosstab, chart, repeater,singleton...) which is in the context of your query and has all the different items you're using. You could also set the container's "Properties" property to include fields from the query which you do not wish to display.