Subtotals in a filtered matrix
-
I've been trying to get this to work for months, so I come pleading for help.
I have the following matrix. The column and row I will reference are artistically shown below.
There is a field which outputs either a 1 or a 0 based on whether it is a "supplementary event" or not. For context, this is things like ice cream, programmes when you go see a show.
The row group is grouped on Order ID to show a row per order, and is filtered to ONLY show orders which contain events with BOTH a 1 and a 0. Meaning they have booked a main ticket and a supplementary item. This is working perfectly.
The first column group is filtered to show the main event they have booked for, so supplementary event = 0. When you run the data, you will only ever include one main event, so this column is fine.
The second column group with the red arrow is filtered to only show events where supplementary event = 1, and is grouped on event name which splits out each supplementary event giving the totals for each. The end result is a report which shows how many supplementary events a customer has booked for a main event.
What I'm struggling with is getting totals for each supplementary event as shown in red above.
I can't just Sum(), because the rows have been filtered to only show customers who have purchased 2 different types of event, but the total has to sit outside of this group as it's grouped on order ID and I'm after the total.
If you need any more information please let me know, it's my first time posting so might have gone wrong!
-
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
This is actually pretty easy to do and Microsoft even has a writeup on it:
The steps are:
In the tablix data region row group area, right-click a cell in the column group area for which you want totals, then point to Add Total, and click Before or After.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
Sadly it's blocked out! But thanks so much for taking a look
-
As a guess, if you select "Insert Row", it should have an option to add it at the top or bottom (if I remember right)... after adding a row, are you able to add totals?
My guess is that there is no place to put the total as all rows in that column are used by calculations, so adding the total would need a new row for that.
Again, this is just a guess as it's been forever since I did stuff like this...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply