If I have a crosstab as below based on a Transformer Cube, with subtotals (and Prodcut and Store Hierarchy are drillable):
Sls$ Sls Units
TY LY TY LY
Product Hierarchy Store Hierarchy 5 4 8 7
Summary 5 4 8 7
Summary 5 4 8 7
If at any level, there is only one detail row, i do not wish to display the entire summary row.
So at the highest level above, I want to only get the following:
Sls$ Sls Units
TY LY TY LY
Product Hierarchy Store Hierarchy 5 4 8 7
Yet, when I drill down within the hierarchies, I wish the Summaries to appear.
I have tried adding a [row number] calculation = 1 as a total aggregate, but I cannot seem to find a way to conditional format the entire Summary crosstab fact cells and the Summary cell itself on this row number Calculation.
Any help would be appreciated.
Thanks in advance,
Adam.
I was able to work thru this and find a workaround to give me the outcome I needed (more or less).
1. Created a RowCount column and added to Crosstab.
2. Set both the Aggregate Function and Rollup Aggregate Function to "Total".
This gave me 1 on each Detail row, but the sum of all the 1's on the Summary row.
Therefore, if one Detail row, RowCount = 1 on Summary row or
if more that one Detail row, RowCount > 1 on Summary row.
3. Create Conditional Style on Summary Rows when RowCount = 1 and set the height of the cell to be 0 px.
4. Set Box Type on RowCount to None (to Hide column).
5. Set table properties of Crosstab to be Fixed Size.
Downside:
Once checking Fixed Size, I had to spend a lot of time trying to get cells in Crosstab to format correctly.
Even then, all column sizes are split evenly within highest (top) heading level of crosstab.
Also, the "squashed" Summary rows still have borders showing, but this I can live with!
Thanks for sharing the solution Adam.