Hi,
I'm creating a martix report that must have overall averages at the end of each row and column. I've added a list with a textbox to cater for the row totals but this doesn't work for the columns (unless there's some way of displaying the list horizontally...?) Adding a new item within the matrix doesn't work as it won't accept aggregate functions. There must be a way of doing this quite easily but I can't figure it out.
Any ideas or suggestions would be much appreciated.
Thanks,
Aidan
Turns out the solution is very simple - all you have to do is right click on the group header cell and select 'subtotal' from the pop-up menu. You can change the properties of the subtotal cell by clicking on the green triangle in the corner of the cell when viewing the properties window. It made more sense when I realised that the pivot cell must have an aggregate value - I was aggregating in my stored procedure so it took a while to figure it out...
Hope this helps someone at some point.
Aidan
|||Thanks for pointing out the "SubTotal" setting for the column totals. However my question is regarding the Row Totals.
My report format is roughly like so
Category1 Category2 Category3 ... CTotal
Day1 10 20 30 60
Day2 11 22 33 66
Day3
...
RTotal 21 42 63 X
Basically I'm querying for rows by Day and have a GROUP BY for Category for certain type of records and am displaying the COUNT() on each day (e.g. 10, 20, 30)
So theCTotal is easily accomplished using the "SubTotal" setting. The problem is I cannot get RTotal to work. I at a point where I'm thinking of doing theRTotal in my query itself using a temp table for the original data, then doing a SUM of all Category COUNTS vertically and then doing a UNION to give me the bottom row. Needless to say, this would so lame of me I will not able to show my face to anyone.
So if you can set me straight on how you managed to do the Row Totals using a List Region, I would sure appreciate it. For the life of me, I cannot get it to work.
Best.
You should be able to use the same subtotal function by right-clicking the the cell with your (Day1, Day2, Day3) Date Value (usually the second cell from the top in the leftmost column).
