Display Dimension Properties along with Dimension
I am intending to display the properties of the dimension in a grid along with the dimension. If I add properties as line item then each line item is repeated across all the dimension elements, as the properties are tied to dimension members and they should not be repeating across dimensions. Below is the screen shot.. In this example my line item should be only amount and somehow I should be in position to display the dimension properties along with dimension. Happy to be on call as well. Need assistance.
| Dimension (X) | Dimension(X) Property 1 | Dimension (X) Proprtery 2 | Jan | Feb | Mar | |
| 10050111001 | CC_10050 | Amount_USD | Amount | 100,000 | 100,000 | 100,000 |
| 10100111002 | CC_10100 | Amount_USD | Amount | 0 | 0 | 0 |
| 10050112006 | CC_10050 | Amount_USD | Amount | 500 | 500 | 500 |
| 10050211001 | CC_10050 | Amount_USD | Amount | 200,000 | 200,000 | 200,000 |
Best Answer
-
Please don't use properties as I don't believe they work the way you think it should work meaning they will automatically be rendered in a module because they are a property. It is best to store them in a SYS module so you aren't double counting them from a cell perspective (properties add to cell count as well).
In your example, you should have a list which is Dimension X then create two modules:
- the first one will be transactional module which is dimensionalized by Dimension X and Time with the only line item being Amount. This is where you will load your transactional data
- the second module will be a SYS module which is only dimensionalized by Dimension X, no time. In here, you can have two line items, list formatted. The first being CC and the other Currency.
From a reporting perspective, create a 3rd module which is dimensionalized by CC, Currency, and Time with a line item named Amount. The formula for Amount will be: Name of Trans module.Amount[SUM:SYS Module.Cost Center Line item, SUM: SYS Module.Currency line item]. Then you can pivot the module the way you would like.
Hope this helps,
Rob
1
Comments
-
@rob_marshall
Appreciate your quick reply. Your solution works. I really wish there is some way to use properties. We are un-necessary creating additional dimensions where there is no need.
0
