Can't get sum to work with 2 groups.

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.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
"Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
news:OooQ%23sqRFHA.356@.TK2MSFTNGP14.phx.gbl...
> 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.
>|||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?
"Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
news:eO5AA5qRFHA.1176@.TK2MSFTNGP12.phx.gbl...
> 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
>
> "Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
> news:OooQ%23sqRFHA.356@.TK2MSFTNGP14.phx.gbl...
> > 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.
> >
> >
>|||Are you wanting specific groups added together or are you just wanting a
grand total?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
news:%23vtMdbrRFHA.2348@.TK2MSFTNGP09.phx.gbl...
>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?
> "Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
> news:eO5AA5qRFHA.1176@.TK2MSFTNGP12.phx.gbl...
>> 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
>>
>> "Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
>> news:OooQ%23sqRFHA.356@.TK2MSFTNGP14.phx.gbl...
>> > 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.
>> >
>> >
>>
>|||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.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:u5GRcTuRFHA.576@.TK2MSFTNGP15.phx.gbl...
> Are you wanting specific groups added together or are you just wanting a
> grand total?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
> news:%23vtMdbrRFHA.2348@.TK2MSFTNGP09.phx.gbl...
> >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?
> >
> > "Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
> > news:eO5AA5qRFHA.1176@.TK2MSFTNGP12.phx.gbl...
> >> 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
> >>
> >>
> >> "Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
> >> news:OooQ%23sqRFHA.356@.TK2MSFTNGP14.phx.gbl...
> >> > 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.
> >> >
> >> >
> >>
> >>
> >
> >
>|||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
"Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
news:uuj$kfzRFHA.3928@.TK2MSFTNGP09.phx.gbl...
> 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.
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:u5GRcTuRFHA.576@.TK2MSFTNGP15.phx.gbl...
> > Are you wanting specific groups added together or are you just wanting a
> > grand total?
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
> > news:%23vtMdbrRFHA.2348@.TK2MSFTNGP09.phx.gbl...
> > >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?
> > >
> > > "Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
> > > news:eO5AA5qRFHA.1176@.TK2MSFTNGP12.phx.gbl...
> > >> 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
> > >>
> > >>
> > >> "Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
> > >> news:OooQ%23sqRFHA.356@.TK2MSFTNGP14.phx.gbl...
> > >> > 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.
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>|||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.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:e2eR$3zRFHA.3880@.tk2msftngp13.phx.gbl...
> 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
> "Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
> news:uuj$kfzRFHA.3928@.TK2MSFTNGP09.phx.gbl...
> > 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.
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > news:u5GRcTuRFHA.576@.TK2MSFTNGP15.phx.gbl...
> > > Are you wanting specific groups added together or are you just wanting
a
> > > grand total?
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
> > > news:%23vtMdbrRFHA.2348@.TK2MSFTNGP09.phx.gbl...
> > > >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?
> > > >
> > > > "Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
> > > > news:eO5AA5qRFHA.1176@.TK2MSFTNGP12.phx.gbl...
> > > >> 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
> > > >>
> > > >>
> > > >> "Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
> > > >> news:OooQ%23sqRFHA.356@.TK2MSFTNGP14.phx.gbl...
> > > >> > 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.
> > > >> >
> > > >> >
> > > >>
> > > >>
> > > >
> > > >
> > >
> > >
> >
> >
>|||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
"Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
news:O01Lax0RFHA.904@.tk2msftngp13.phx.gbl...
> 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.
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:e2eR$3zRFHA.3880@.tk2msftngp13.phx.gbl...
> > 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
> >
> > "Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
> > news:uuj$kfzRFHA.3928@.TK2MSFTNGP09.phx.gbl...
> > > 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.
> > >
> > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > > news:u5GRcTuRFHA.576@.TK2MSFTNGP15.phx.gbl...
> > > > Are you wanting specific groups added together or are you just
wanting
> a
> > > > grand total?
> > > >
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
> > > > news:%23vtMdbrRFHA.2348@.TK2MSFTNGP09.phx.gbl...
> > > > >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?
> > > > >
> > > > > "Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
> > > > > news:eO5AA5qRFHA.1176@.TK2MSFTNGP12.phx.gbl...
> > > > >> 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
> > > > >>
> > > > >>
> > > > >> "Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
> > > > >> news:OooQ%23sqRFHA.356@.TK2MSFTNGP14.phx.gbl...
> > > > >> > 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.
> > > > >> >
> > > > >> >
> > > > >>
> > > > >>
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||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.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23nS4E60RFHA.2356@.TK2MSFTNGP14.phx.gbl...
> 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
> "Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
> news:O01Lax0RFHA.904@.tk2msftngp13.phx.gbl...
> > 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.
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > news:e2eR$3zRFHA.3880@.tk2msftngp13.phx.gbl...
> > > 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
> > >
> > > "Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
> > > news:uuj$kfzRFHA.3928@.TK2MSFTNGP09.phx.gbl...
> > > > 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.
> > > >
> > > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > > > news:u5GRcTuRFHA.576@.TK2MSFTNGP15.phx.gbl...
> > > > > Are you wanting specific groups added together or are you just
> wanting
> > a
> > > > > grand total?
> > > > >
> > > > >
> > > > > --
> > > > > Bruce Loehle-Conger
> > > > > MVP SQL Server Reporting Services
> > > > >
> > > > > "Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
> > > > > news:%23vtMdbrRFHA.2348@.TK2MSFTNGP09.phx.gbl...
> > > > > >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?
> > > > > >
> > > > > > "Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
> > > > > > news:eO5AA5qRFHA.1176@.TK2MSFTNGP12.phx.gbl...
> > > > > >> 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
> > > > > >>
> > > > > >>
> > > > > >> "Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
> > > > > >> news:OooQ%23sqRFHA.356@.TK2MSFTNGP14.phx.gbl...
> > > > > >> > 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.
> > > > > >> >
> > > > > >> >
> > > > > >>
> > > > > >>
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>