How to SubTotal a filtered "Column Group"

Question

How to SubTotal a filtered "Column Group"

evan_shechtman

SSC-Addicted

Points: 465
More actions
October 6, 2010 at 1:06 pm

#224817

Hello,
I've been working on an SSRS 2008 report and I'm having some trouble getting the total from a filtered column group. I'm using a dataset from an MDX query and need to grab all records because I calculate totals on the entire set. From there, I've grouped the records by type and filtered the group to only view certain types.
After receiving all the records from the orginal dataset query, all the attempts at using Sum(fields!fieldname.Value) only calculates the total of all types.
Is there a way to calculate the sum (subtotal) of the filtered types in the column group and not include all of the unfiltered records returned from the original dataset query?
Thanks so much in advance,
Evan

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

blue.aqua.43 SSC Enthusiast Points: 132 More actions · Answer 1

Hi

I believe you have filtered only on the group. Filter on the tablix as well to get a sum of only the filtered rows that you want to view. Hope this helps.

evan_shechtman SSC-Addicted Points: 465 More actions · Answer 2

Thanks so much for your response!

The problem with applying the filter to the tablix is I still need to calculate the total sum (grand total) of all types along with the total of the filtered "column group".

Can I achieve this by creating two matrices and setting one to filtered?

Much appreciated!

Evan

blue.aqua.43 SSC Enthusiast Points: 132 More actions · Answer 3

Do you have multiple groups in your matrix that you are setting filters on?

evan_shechtman SSC-Addicted Points: 465 More actions · Answer 4

I'm only using one column group at the moment. I've also tried adding a parent group for "Year" which is only one umbrella group, "2011".

blue.aqua.43 SSC Enthusiast Points: 132 More actions · Answer 5

Then I think you should be fine if you can create two matrices.

In one of them you can filter on only the group to get a total of the filtered as well as other rows.

In the other one you can filter on the tablix as well so that you get a sum of only the filtered rows.

I would then adjust by hiding rows in the matrices as required to get the desired display.

evan_shechtman SSC-Addicted Points: 465 More actions · Answer 6

Thanks so much! By adding a filter on the inner matrix I was able to show the grand total and column group sub-total in the same table.