sql server reporting services: Can't get sum to work with 2 groups. -- sql server reporting services

You're in the

sql server reporting services

group:

Can't get sum to work with 2 groups.

Can't get sum to work with 2 groups.

Jason Agee
4/21/2005 2:49:32 PM

sql server reporting services: Hello, I've got 2 groups (on county, and on precinct) and my Dataset looks
like this:

row1 : county = 1, precinct = 1, count = 500, value = a
row2 : county = 1, precinct = 1, count = 500, value = b
row3 : county = 2, precinct = 3, count = 700, value = c
row4 : county = 2, precinct = 3, count = 700, value = d

The first group is on county, and the second group is on precinct, so it
would look like this:

County 1
Prec 1 Count 500
Value a
Value b

County 2
Prec 2 Count 700
Value c
Value d

What I want is a total -------------
1200

which sums up the above rows. It has to be on the county group to appear at
the bottom of the county, but it's adding all of the rows. So I get 2400
instead of 1200. I can't provide the scope of the precinct group, because
it's not a containing group (i.e. it's at a lower level). How can I just add
up the displayed values in column? Thanks.

Re: Can't get sum to work with 2 groups.

Adrian M.
4/21/2005 4:11:03 PM

This question has been asked various times on this group. I know. I asked
it once. I have not seen an answer or have been able to accomplish this
using Report Designer.

--
Adrian M.
MCP

[quoted text, click to view]

Re: Can't get sum to work with 2 groups.

Jason Agee
4/21/2005 4:12:43 PM

I agree. Before posting the question I searched and found a couple of very
similar posts with no clear answer. I would think it's an important issue,
and I have a hard time believing it can't be done. Does anyone have any
ideas?

[quoted text, click to view]

Re: Can't get sum to work with 2 groups.

Bruce L-C [MVP]
4/21/2005 10:41:58 PM

Are you wanting specific groups added together or are you just wanting a
grand total?

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

[quoted text, click to view]

Re: Can't get sum to work with 2 groups.

Jason Agee
4/22/2005 7:36:22 AM

Using the below example (sorry, this one is more accurate than the first
one), I would like a grand total of the Precinct Count values, displayed at
the county level.

Data Source
row1 : county = 1, precinct = 1, count = 500, value = a
row2 : county = 1, precinct = 1, count = 500, value = b
row3 : county = 1, precinct = 2, count = 600, value = c
row4 : county = 1, precinct = 2, count = 600, value = d
row5 : county = 2, precinct = 1, count = 700, value = e
row6 : county = 2, precinct = 1, count = 700, value = f

County 1
Precinct 1 Count 500
Value a
Value b

Precinct 2 Count 600 Value c
Value d
-----------
1100 //This is what I want
County 2
Precinct 1 Count 700
Value c
Value d
-----------
700 //This is what I want

By placing this in the County group footer, the location of the grand total
is correct, but the numbers are doubled. They don't show as 1100, and 700,
but as 2200 and 1400 because (I'm assuming) it's adding all of the rows in
the county group. I can't specify the scope of the sum function to be the
"Precinct" group because it's not a containing group. Any ideas? Thanks.

Jason A.

[quoted text, click to view]

Re: Can't get sum to work with 2 groups.

Bruce L-C [MVP]
4/22/2005 8:20:02 AM

One way to solve this is a sub report. Create a report that takes county as
the report parameter. Then have the report have a single field in it. Make
the report totally minimal. Test it and when it is working drag and drop it
into the cell for the total, then do a right mouse click, parameters and set
the report parameter to the county. When I do this I hide the subreport in
listview so the users don't ever go to it directly. It takes a little
playing with the sub report to get it formatted and lined up the way you
want but it will work.

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

[quoted text, click to view]

Re: Can't get sum to work with 2 groups.

Jason Agee
4/22/2005 10:02:48 AM

Thanks for the suggestion Bruce, but there is a problem with this solution -
the table that the subreport has to hit has several million rows. We've
already pulled the data once to display the information and it takes a
couple of seconds, which is acceptable. However if we have to query the data
50 times for 50 counties, the report becomes too slow. Is there any way to
access the data source in the "Code" section of the report so I can loop
through and add the values up myself? I've tried creating a global variable
and incrementing it based on the precinct id, but the problem with this is
that the "County" footer gets evaluated before the precincts grouping, so
it's incorrect. Any other ideas? Thanks.

[quoted text, click to view]

Re: Can't get sum to work with 2 groups.

Bruce L-C [MVP]
4/22/2005 10:18:19 AM

One thing I do a lot is have a total field like that on each line and then
just show it once. For instance I do this (usually this is done in a stored
procedure).

row1 : county = 1, precinct = 1, count = 500, value = a, countytotal = 1100
row2 : county = 1, precinct = 1, count = 500, value = b, countytotal = 1100
row3 : county = 1, precinct = 2, count = 600, value = c, countytotal = 1100
row4 : county = 1, precinct = 2, count = 600, value = d, countytotal = 1100
row5 : county = 2, precinct = 1, count = 700, value = e, countytotal = 700
row6 : county = 2, precinct = 1, count = 700, value = f, countytotal = 700

Then in the group footer for county you reference the field, you don't do a
sum or anything on it.

In your case have a stored procedure, put your existing data in a temp table
then do a
insert #temp2 select county, precinct, distinct(count) from #temp group by
county, precinct
etc

Anyway, you can do this without hitting the base tables a second time.

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

[quoted text, click to view]

Re: Can't get sum to work with 2 groups.

Jason Agee
4/22/2005 11:09:20 AM

I think I am going to go with this solution. We were trying to avoid passing
back the redundant totals, but it's looking like the best option. For the
record however I was able to find another solution that I will try to
explain for posterity.

I created two Vb.Net functions in the "Code" section of report properties.
These functions are below:

----------Code
Public Dim CurrentPrecinct As String
Public Dim PrecinctTotal As Integer = 0

//Add up the value from the first instance
//of each precinct in the precincts grouping
public Function TotalPrecinctValue(Name as String, Value As Integer)
//If the strings are equal just return the value
If String.Compare(Trim(Name), Trim(CurrentPrecinct)) = 0 Then
Return Value
End If
//If the strings are not equal,
//save the string as the CurrentPrecinct,
//add the total and return the value
CurrentPrecinct = Name
PrecinctTotal = PrecinctTotal + Value
Return Value
End Function

//Reset the PrecinctTotal value
public Function ResetTotalPrecinctValue() As String
PrecinctTotal = 0
return ""
End Function
----------End Code

Set the expression on the PrecinctCount value to be:
=Code.TotalPrecinctValue(Fields!Precinct.Value, Fields!Count.Value)
This will always return the same value passed in, but it will add the value
to a global variable if it's a new precinct.

Add a new row beneath the "Precinct" grouping. Under the Count value column,
set the expression for the field on the new row to be:
=Code.PrecinctTotal
This is the value of the global variable. Next set the visibility on the new
row to be:
=IIf(RowNumber("County") = CountRows("County"), False, True)
This makes sure the row is only displayed if it is the last row.

Finally, somewhere in the County row, set one of the fields to be:
=Cose.ResetTotalPrecinctValue()
so that each county group will reset the running precinct total.

Simple, right :) The only problem with this is that when the county is
collapsed, the total appears below the county:
County 1
1100
and our clients are just picky enough to care. So I think we'll go with your
suggestion so that we can just drop the totals on the county line. Thanks
for your help.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.

home · blog · groups

Questions? Comments? Contact the dn