Hide expression column in pivot table

[VivenReddy]

I have a pivot table in which I'd like the Total [$ Volume] column displayed. However I do not want the [$ Volume] columns displayed under the pivoted Supplier columns. See screenshot below.

I have also attached the qvw file

[rubenmarin]

Hi, a workaround can be moving the expression as a calculated dimension:

=If(RowDim=1,aggr(Num(sum(Total_kg * Price_kg),'$#,##0;-$#,##0'),Fruit, Category))

It won't show totals, maybe you can simulate them if you insert another RowDim to show totals and simulated them setting bakground color and bold text format.

The calculated dimension for Fruit can be:

=Pick(RowDim, Aggr(Fruit, Fruit), 'Others')

[marcus_sommer]

Another approach could be just to add another on-top dimension to the object. If none native hierarchycally one exists you could also simply use something like: = 'all' and then you could with (secondary)dimensionality() refer to it to apply different expressions for each level.

Also possible is adding a further value to your dimension in the script, like:

concatenate(INLFED) load '$ Volume' as Supplier autogenerate 1;

and then you could query this fieldvalue to branch into different expressions.

- Marcus

[VivenReddy]

Thanks for the response. Are you able to show me your proposal on my sample qvw file.

[marcus_sommer]

Here both approaches whereby the second suggestion with adding an appropriate dummy-value within the script might need some further adjustments (adding appropriate values within other fields, too to be to consider any selections and/or those fields within the aggr and/or the totals) because of your rather complex design with the aggr.

Beside this I would suggest to keep the logic as simple as possible and avoiding the aggr and the various other stuff with the col/dim and so on and to build a rather "classical" datamodel and also calculating the volume and similar measures already within the script.

- Marcus

[Brett_Bleess]

Viven, did Marcus' updated app get you what you needed?  Please be sure to give him credit if so by using the Accept as Solution button on his post.  If you did something else, please consider posting that and then using the button to mark that post as the solution.  If you are still working on things, leave an update of where things stand.

Regards,
Brett