I've a slight problem with a pivot in that I am using the Suppress Zero Values option for the data, but this is removing the total row under certain circumstances.
Quick example to show what I mean;
Transactions:
load * inline
[
Name, Amount
Bob, 2
Seraphim, -2
Baldrick, 1
];
...some nice simple data.
I then create a simple pivot with one dimension, 'Name', and one expression, 'sum(Amount)'. On the Presentation tab I select to show totals under Name.
My default view on the pivot shows this;
Name sum(Amount)
Baldrick 1
Bob 2
Seraphim -2
Total 1
Wonderful so far. However, when I filter out Baldrick...
Name sum(Amount)
Bob 2
Seraphim -2
...no Total row, because it's zero!
Now, in this example it's not much of a problem because even with my brain I can see that -2 and 2 = 0, but in the system I'm developing there can be +ve and -ve transactions which cancel each other out, and there can be LOTS of them, many more than anyone's brain will care to perform a running total on. There may even be a +5, -3, -1.75, -.25... having that Total line at the bottom showing zero is pretty important to me!
I have kinda faked it, but I don't like the solution, which is to add a new expression column with a value of
= if ( RowNo() = 0, 'Total', '' )
, so that total lines will have some non-zero data on them. However, that leaves me with a strange, fake column on my table, and I'd rather not have that there.
So then, the big question: Is there an option, or a way to implement, 'Suppress Zero Value Rows Except For Total Lines' please? Or is what I've done about as good as it's going to get?
Thanks for looking.
