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?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Total on a TopN group 1
- Thread starter LV
- Start date
-
1
- #2
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:
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 before you ask a question
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:
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 before you ask a question
- Thread starter
- #3
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 before you ask a question
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 before you ask a question
- Status
Similar threads
- Locked
- Question
- Locked
- Question