Question Re: Multi-value dimension onto a single row in multiple c... in Technology Q&A

Multi-value dimension onto a single row in multiple columns

Gizmo14 — Wed, 07 Feb 2024 09:24:28 GMT

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.

Re: Multi-value dimension onto a single row in multiple c...

ayman_salem — Mon, 12 Feb 2024 18:25:14 GMT

You can use a crosstab

....

I hope this is what you are looking for

Re: Multi-value dimension onto a single row in multiple c...

G-Garlatti — Thu, 22 Feb 2024 11:22:12 GMT

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.