I have an interesting problem and I am looking for some suggestions on how to solve. We have a reporting cube that has an Account and Cost Center dimension. The combination of these 2 dims make up a P&L line item.
To create this in a UX view we have used tuples to define the row set, and we have added a subview which pulls the line description from another cube.
We are now trying to figure out a scaleable way to manage the row formats. We have standard scss class built for this, SubTotal, GrandTotal, Etc. The only way we we have found to format the rows is to add the class references manually via the advanced settings.
Some options I have thought of
Building a cube that joins these 2 dims together, this would take a while and require quite a bit of testing so we want to avoid this.
Using a horizontally sectioned view since 1 of the dims changes, not currently supported by UX
store the formats in the same cube that houses the descriptions and reference them from there. Not sure how this could work as UX can’t pick up formats from data cells.
Interesting problem. We will discuss this internally in the product team and get back to you.
It seems to me though (as a TM1 developer) that building a P&L reporting cube by P&L Line Name would actually be the least work and lowest maintenance option.
I agree that typically I’d just build a cube to support it and that is likely the long term plan. However each user group prefers slightly different row definitions and the time to coalesce it all would be quite long.
many of these reports are being built by our end users and there current thinking is based around report row sections, similar to how you would add an activeform section. 1 section has cost center breakout, another region, another product.
Although formatting only supports attributes and therefore single dimension. Perhaps conditional formatting could solve this issue as UX now supports conditional formatting based on value of another column and not just of the cell itself. You could insert a column and then have a DBRW formula there look up a formating key, and then using conditional formatting based on on the format key column (which you would then hide).
Obviously this has some disadvantages since the key to format lookup would basically need to be hardcoded in the advanced options json so you wouldn’t want to do it if there were more than a handful of potential formats, and if the tables are big then there could also be performnce impacts. However, in principal I think this would work.
Was there ever a resolution to this? The stacked row dimensions always creates problems, so something like subviews below would be in my mind a good solution
