April 28, 2010 at 1:52 pm
I'm aware that by default a Total field that sums up group values is actually summing up the entire dataset, not just the visible rows of the group. I'm wracking my brain and internet searches trying to find a method to do what I want, though.
My dataset contains all of the individual data points I am reporting on. However, my report just needs aggregates, sums of the individual data. That works great. I don't have a good way to give specific data samples, but here's roughly what it looks like:
My data consists of
DIVISION
ValueW
ValueX
ValueY
ValueZ
I can have multiple rows of this data. My report is grouped by DIVISION, and displays Sum(ValueW), Sum(ValueX), Sum(ValueY), and Sum(ValueZ) in columns. Perfect.
What I've done is created a filter on the GroupByDivision, where I want to exclude where a parameter is true and Sum(ValueX + ValueY) = 0. Basically I want to exclude from the output. The filter works great.
I have a total row now that should have totals of everything. Of course this works, but is ignoring the filter. So how the heck do I get it to only sum the groups items and not everything? Sum(whatever, "GroupByDivision") doesn't work because it's out of the group scope. I've seen references to using code functions, but I'm not sure how that would work.
Any help?
April 28, 2010 at 2:08 pm
You know, the people that help out here are all un-paid volunteers. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 7, 2010 at 8:19 am
Thanks, I'm aware of the situation but I really don't have the means to recreate a sample dataset...I'm working with protected information and it would be cumbersome to try and come up with the sample structures, schema, data, etc., to try and get my point across. Besides, the problem I'm describing seems to be a somewhat known issue around the 'net, it's just that the workarounds aren't working for me either.
Take any dataset in SSRS. Spit it out to a table. Now group that dataset on something, and have a row for group sums. Hide the details row - you should now have a list of the grouped values. Now add a total row, which sums the group rows. Now add a filter to the group so some group rows are dropped off. You'll have group rows suppressed, but the total row will still down everything regardless if the row is filtered or not. Here's some more info and some sample code I posted on another forum:
In SSRS 2008 I am trying to maintain a SUM of SUMs on a group using custom Code. The reason is that I have a table of data, grouped and returning SUMs of the data. I have a filter on the group to remove lines where group sums are zero. Everything works except I'm running into problems with the group totals - it should be summing the visible group totals but is instead summing the entire dataset. There's tons of articles about how to work around this, usually using custom code. I've made custom functions and variables to maintain a counter:
Public Dim GroupMedTotal as Integer
Public Dim GrandMedTotal as Integer
Public Function CalcMedTotal(ThisValue as Integer) as Integer
GroupMedTotal = GroupMedTotal + ThisValue
GrandMedTotal = GrandMedTotal + ThisValue
Return ThisValue
End Function
Public Function ReturnMedSubtotal() as Integer
Dim ThisValue as Integer = GroupMedTotal
GroupMedTotal = 0
Return ThisValue
End Function
Basically CalcMedTotal is fed a SUM of a group, and maintains a running total of that sum. Then in the group total line I output ReturnMedSubtotal which is supposed to give me the accumulated total and reset it for the next group. This actually works great, EXCEPT - it is resetting the GroupMedTotal value on each page break. I don't have page breaks explicitly set, it's just the natural break in the SSRS viewer. And if I export the results to Excel everything works and looks correctly.
If I output Code.GroupMedTotal on each group row, I see it count correctly, and then if a group spans multiple pages on the next page GroupMedTotal is reset and begins counting from zero again
Thanks!
May 24, 2010 at 9:59 am
Hi,
My approach would be to filter out the group data within the sql script and then your normal total sum expression will be correct as you have filtered out the data.
Your sql should be something like this:
select * from (
Select division, sum(w) as W ,sum(x) as X ,sum(y) as Y, sum(z) as Z
group by division ) as Derived_Data
where x+y <> 0
Do you really need to have the detailed data in the report?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply