What I'm trying to create:I'm trying to create the following crosstab structure in a report studio report (relational model):
Rows are dynamic based on dates. Columns are both dynamic (for products) and fixed (last 3 columns - the aggregates).
| Tents | Bug Spray | Knife | ... | Total Qty | Total $ | Cumulative $ |
2009-01-01 | 1 | 30 | 5 | ... | 36 | $360 | $360 |
2009-01-02 | 1 | 2 | 15 | ... | 18 | $180 | $540 |
2009-01-03 | 2 | 15 | 17 | ... | 24 | $240 | $780 |
... | ... | ... | ... | ... | ... | ... | ... |
What I've tried:Ive created a crosstab with the dates creating rows, the products creating columns, and the aggregate quantity. Easy enough.
Then I drag the [Sales $] as another column to the crosstab. Also easy enough, and works as expected.
Then, I drag a query calculation as another column to the crosstab, and define the value as running-total([Sales $]). And this is where it all goes wrong!
The first row is correct (eg. Shows $360). The second row is correct (shows $540), and beyond that the numbers are much higher than I would expect, and also in the wrong order (so not accumulating in the same order as the dates in my report).
What I need:I'm guessing that I need to specify some additional properties, aggregation levels and/or rollup types for the running-total calculation, but i've tried a lot of combinations and i just can't get it right.
Is anyone able to shed any light on what is going wrong, and how i can fix it?