I have a report that has two groups, first is filtered by top 20 values, and second is filtered by bottom N on count(idField, "dataset") - 20 - in other wordsm, the rest of the dataset fields. The data displays as expected,  however, adding total sum on both groups displays total for the entire dataset, not the filtered group. Do you guys know why it's doing this and ifthere is a workaround?

Sums don't work on data that is filtered in the report - they work on the data behind the scenes

you can write a bit of custom code that will do this for you. the following link is a decent discussion on  filtering / Summing in SSRS and has a link to the custom code: http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/39963590-1130-485d-b275-926f1852fa61

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244: How to get the best answers before you ask a question

Thanks for the reply and provided link. This behaivior seems kind of strange, I would still expect a group to aggregate on its filtered subset, not the entire report dataset, but oh well. I ended up modifying the MDX query to return the top N and agregated "others" records. Seemed like a cleaner solution vs. writing cutom code inside of the report.

Agreed on the custom code not being a "clean" solution but not sure I agree on how aggregation should or should not work.

Summing based on a filtered dataset could easily introduce misinterpretation into the reading of a report if the user did not know a filter was being applied.

Microsoft has previous for this way of thinking in MS Excel where a seperate "subtotal" function is needed to show aggregates of subsets of data as the sum function always sums all data

The other way to think of this is in the usage of filtering vs query parameters. If you return all data then filter, the priority is given to the fact that you return all data so summing applies to that. If you want to see only aggregated data for a dataset, you can use a query parameter to restrict the data coming back to only the subset

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244: How to get the best answers before you ask a question