How to wisely transform a straight table with complicated expressions to a pivot table?

[#1]

Hello,

I built a very fancy straight table with many expressions, some use prior expressions in the object as an input. For example, I defined an expression:

sum(....) and labeled it X, and later used X in another expression, and so on. The final number I seeked uses many of this. Frankly, without 'calling' previous expressions by their labels the expressions would be monstrous.

Anyway,

now I was asked to present my numbers in a way that could only be done with a pivot table. That is, for example, time dimension as columns, horizontal dimensions have sub dimensions collapsed, etc.

Two things you can't do in pivot tables:

1) hide some of the expressions

2) (to the best of my understanding) use previously defined labels as inputs for expressions.

So, how can I elegantly transform my straight table to a pivot? (or rebuild a pivot from scratch).

I should mention that I don't need to present all of the expressions that were used as aids for calculations in the pivot table. I'm required to present only a small subset of the expressions calculated.

Also, when I just transformed the straight table to pivot table, some of the rows lost data (and not sure the other rows presented accurate data).

Thanks in advance for your help!

[swuehl]

I believe you can reference an expression label also in a pivot table chart.

It's hard to tell you what might be wrong with your table without knowing almost anything (data model, expressions, dimensions).

Take care when using chart inter record functions like above() in a straight table, since you might need to use before() instead when pivoting the dimension to the top (analog  for other chart inter record functions).

[#3]

yes, I'm sorry I just can't post my model or a piece of it due to the sensitivity of information.

And indeed I have a problem with the above function.....

And there's also the problem of hiding expressions I create

[bdunphy]

The straight table and pivot table are very different. Features like Dimension Limits and Hidden Expressions can be used to create quite complex straight tables which are not possible to copy over to a pivot table.

I usually create a second version of the straight table which has a more limited set of 'fancy' features but allows pivoting.

Then set a variable 'vShowVersion' controlled by an action button and set this to 1 or 0 depending on the user pressing the button.

If you need to have  time across the top of your pivot e.g. month - you could create a straight table with 12 different calculations - one for each month to mimic the features of a pivot table (of course the problem is if you have 5 metrics * 12 months you have a lot of calcs - and if you need it weekly - it quickly becomes out of control)