Multi-value dimension onto a single row in multiple columns

[Gizmo14]

Hi. I have a service order with multiple operation lines. That service order is made up of many 'Account Indicator' 1, 2, 3, 4 etc, 1 for each line. On each service order operation line there is a field called 'User Field 00'. In the field called 'User Field 00' is a code, that code will change for each service order line/Account Indicator. In BI I want a single line for each service order and a column for each of the 'User Field 00' code for each of the different  'Account Indicator', so if a service order had 6 Account Indicators I would want service order number in the 1st column and then the User Field 00 is individual subsequent columns . What are the variable(S) I need to create for each column? I have tried various variations of '=If ([Account Indicator]) = 0 Then ([User Field 00]) ForEach ([Service order + AccIndi]).

Any help would be appreciated.

[G-Garlatti]

Hello, first of all, I think you need to change the Account indicator into a dimension and not a measure.

• If you load the data from an universe, see if your designer could change it, or add a new dimension object.
• If you load the date from an excel, in the last 4.3 sp you could change in properties panel measure to dimension.
• neither of the two above ? create a dimension, use a string formating to change the measure into a dimension.

Because a measure could have aggregate function in it like "sum()",

In the last 4.3 patch you could also use the data cube to alter your data in your own way.

Second, cross table is the right answer. But if you have a lot of account indicator in each order (hundred or thousand) it will not be a nice solution without something to filtering your field of vision. 

[ayman_salem]

You can use a crosstab

....

I hope this is what you are looking for