Can't Sum Table Fields in SQL 2000 Reporting Services Report

I have a report in SQL 2000 Reporting Services with several tables.
The only thing showing in these tables are the group footers because
the detail data is not needed.
I am needing to add a total for a column in each table and then add
those totals together in a text box.
When ever I do this I am getting the following errors:
The value expression for the text box 'textbox58' uses an aggregate
function on a report item. Aggregate functions can be used only on
report items contained in page headers and footers.
The value expression for the text box 'textbox37' uses an aggregate
expression without a scope. A scope is required for all aggregates
used outside of a data region unless the report contains exactly one
data set.
I've tried several different ways and still can't figure it out, PLEASE
HELP!!A few design suggestions. If you don't need the detail then don't return it.
Let SQL Server do the work. But, let's say you continue the way you are
today. What I do sometimes is to add a colum to the result set and make it
calculated, then using the expression editor make it the sum of the two
fields. This makes things a whole lot easier at times.
Once you have done this you can use the expression editor, base it on the
dataset and sum up the column.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<jasper.frazee@.gmail.com> wrote in message
news:1162406083.894334.80910@.k70g2000cwa.googlegroups.com...
>I have a report in SQL 2000 Reporting Services with several tables.
> The only thing showing in these tables are the group footers because
> the detail data is not needed.
> I am needing to add a total for a column in each table and then add
> those totals together in a text box.
> When ever I do this I am getting the following errors:
> The value expression for the text box 'textbox58' uses an aggregate
> function on a report item. Aggregate functions can be used only on
> report items contained in page headers and footers.
> The value expression for the text box 'textbox37' uses an aggregate
> expression without a scope. A scope is required for all aggregates
> used outside of a data region unless the report contains exactly one
> data set.
> I've tried several different ways and still can't figure it out, PLEASE
> HELP!!
>|||Thanks for the Responce Bruce!
In my dataset there are 5 columns that will alwayse have the same
result and one column that has detail data that needs to be summed. If
I sum that one column in SQL I have to have the other columns in an
aggregate function as well. In SQL 2000 the "First" function is not
supported. Any ways around that?
Once I get the totals in each table on my report how can I grab those
totals and sum them in a textbox? It keeps telling me that "Aggregate
functions can be used only on
report items contained in page headers and footers."
Thanks A Lot!
Jasper
Bruce L-C [MVP] wrote:
> A few design suggestions. If you don't need the detail then don't return it.
> Let SQL Server do the work. But, let's say you continue the way you are
> today. What I do sometimes is to add a colum to the result set and make it
> calculated, then using the expression editor make it the sum of the two
> fields. This makes things a whole lot easier at times.
> Once you have done this you can use the expression editor, base it on the
> dataset and sum up the column.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <jasper.frazee@.gmail.com> wrote in message
> news:1162406083.894334.80910@.k70g2000cwa.googlegroups.com...
> >I have a report in SQL 2000 Reporting Services with several tables.
> > The only thing showing in these tables are the group footers because
> > the detail data is not needed.
> > I am needing to add a total for a column in each table and then add
> > those totals together in a text box.
> > When ever I do this I am getting the following errors:
> >
> > The value expression for the text box 'textbox58' uses an aggregate
> > function on a report item. Aggregate functions can be used only on
> > report items contained in page headers and footers.
> >
> > The value expression for the text box 'textbox37' uses an aggregate
> > expression without a scope. A scope is required for all aggregates
> > used outside of a data region unless the report contains exactly one
> > data set.
> >
> > I've tried several different ways and still can't figure it out, PLEASE
> > HELP!!
> >|||You can sum the numbers by summing =ReportItems("textbox1").Value +
ReportItems("textbox2").Value etc.
You need to know the names of the textboxes, so you might want to name the
textboxes with something more descriptive than textbox1 etc before you start
summing. :)
(Not sure if it's ReportItems or ReportItem, so you might have to try both.)
Kaisa M. Lindahl Lervik
<jasper.frazee@.gmail.com> wrote in message
news:1162417277.149051.147270@.f16g2000cwb.googlegroups.com...
> Thanks for the Responce Bruce!
> In my dataset there are 5 columns that will alwayse have the same
> result and one column that has detail data that needs to be summed. If
> I sum that one column in SQL I have to have the other columns in an
> aggregate function as well. In SQL 2000 the "First" function is not
> supported. Any ways around that?
> Once I get the totals in each table on my report how can I grab those
> totals and sum them in a textbox? It keeps telling me that "Aggregate
> functions can be used only on
> report items contained in page headers and footers."
> Thanks A Lot!
> Jasper
> Bruce L-C [MVP] wrote:
>> A few design suggestions. If you don't need the detail then don't return
>> it.
>> Let SQL Server do the work. But, let's say you continue the way you are
>> today. What I do sometimes is to add a colum to the result set and make
>> it
>> calculated, then using the expression editor make it the sum of the two
>> fields. This makes things a whole lot easier at times.
>> Once you have done this you can use the expression editor, base it on the
>> dataset and sum up the column.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> <jasper.frazee@.gmail.com> wrote in message
>> news:1162406083.894334.80910@.k70g2000cwa.googlegroups.com...
>> >I have a report in SQL 2000 Reporting Services with several tables.
>> > The only thing showing in these tables are the group footers because
>> > the detail data is not needed.
>> > I am needing to add a total for a column in each table and then add
>> > those totals together in a text box.
>> > When ever I do this I am getting the following errors:
>> >
>> > The value expression for the text box 'textbox58' uses an aggregate
>> > function on a report item. Aggregate functions can be used only on
>> > report items contained in page headers and footers.
>> >
>> > The value expression for the text box 'textbox37' uses an aggregate
>> > expression without a scope. A scope is required for all aggregates
>> > used outside of a data region unless the report contains exactly one
>> > data set.
>> >
>> > I've tried several different ways and still can't figure it out, PLEASE
>> > HELP!!
>> >
>sql

can't return zero when no rows exist using aggregate function sum

Hi,
I'm having a heck of a time trying to get SQL Server 2000 to return a
zero (0) if no rows are returned in a sum() statement. I know in
Oracle you wrap an NVL around the whole shebang, and in Googling it
seems as if SQL Server should work this way, too. But I cannot get it
to work, I just keep getting null.
I have tried:
what I thought would work:
SELECT ISNULL(SUM(ISNULL(PointValue, 0)), 0)
FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
tbl_event_types et ON ae.[event type] = et.eventID
WHERE [File #] = '0001001047'
AND EventDate BETWEEN '2/24/2005' AND getdate()
GROUP BY [File #]
a case statement testing for null:
SELECT CASE
WHEN SUM(ISNULL(PointValue, 0)) IS NULL THEN 0
ELSE SUM(ISNULL(PointValue, 0))
END
FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
tbl_event_types et ON ae.[event type] = et.eventID
WHERE [File #] = '0001001047'
AND EventDate BETWEEN '2/24/2005' AND getdate()
GROUP BY [File #]
wrapping the whole select statement in ISNULL as a subquery:
SELECT ISNULL((SELECT SUM(ISNULL(PointValue, 0))
FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
tbl_event_types et ON ae.[event type] = et.eventID
WHERE [File #] = '0001001047'
AND EventDate BETWEEN '2/24/2005' AND getdate()), 0)
FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
tbl_event_types et ON ae.[event type] = et.eventID
WHERE [File #] = '0001001047'
AND EventDate BETWEEN '2/24/2005' AND getdate()
and then to make sure what I was getting back was null and not empty
...something:
declare
@.n_sum numeric(6, 2)
SELECT @.n_sum = ISNULL(SUM(ISNULL(PointValue, 0)), 0)
FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
tbl_event_types et ON ae.[event type] = et.eventID
WHERE [File #] = '0001001047'
AND EventDate BETWEEN '2/24/2005' AND getdate()
GROUP BY [File #]
print 'sum is: ' + convert(char(8), isnull(@.n_sum, 0))
sum is: 0.00
What the heck am I doing wrong? I tried setting ANSI_NULL off and on,
etc...but no go. I guess I could get around by using the variable and
then returning that to the VB (since this is a stored proc used to find
one value to return to the VB6 program), but that seems kind of messy.
I would appreciate any help!
TIA
PatCould you provide actual specs and desired results?
http://www.aspfaq.com/5006

> I'm having a heck of a time trying to get SQL Server 2000 to return a
> zero (0) if no rows are returned in a sum() statement.|||I need to say this - I inherited this code and database structure!!!!
CREATE TABLE [tbl_Attendance_Event] (
[EventCode] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[File #] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Event Type] [numeric](9, 0) NULL ,
[EventDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Excused] [bit] NULL ,
[EventDate] [smalldatetime] NULL ,
[EventDuration] [money] NULL ,
[PointValue] [money] NULL ,
[ConsecDayCount] [numeric](9, 0) NULL ,
[Consecutive_Days] [bit] NULL ,
[EventRemove] [bit] NULL ,
[EventRemoveDate] [datetime] NULL ,
CONSTRAINT [PK_tbl_Attendance_Event] PRIMARY KEY NONCLUSTERED
(
[EventCode]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_tbl_Attendance_Event_tbl_Event_Types
] FOREIGN KEY
(
[Event Type]
) REFERENCES [tbl_Event_Types] (
[EventID]
)
) ON [PRIMARY]
GO
CREATE TABLE [tbl_Event_Types] (
[EventID] [numeric](9, 0) IDENTITY (1, 1) NOT NULL ,
[EventDescription] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[EventPoints] [smallmoney] NULL ,
[ExcepID] [numeric](9, 0) NULL ,
[TardyLogic] [bit] NULL ,
[Minimum Minutes] [numeric](9, 0) NULL ,
[Maximum Minutes] [numeric](9, 0) NULL ,
[ExtendsYearWindow] [bit] NULL CONSTRAINT
[DF_tbl_Event_Types_ExtendsYearWindow] DEFAULT (0),
[RuinsPerfectMonthlyAttendace] [bit] NOT NULL CONSTRAINT
[DF_tbl_Event_Types_RuinsPerfectMonthlyA
ttendace] DEFAULT (0),
[LogEvent] [bit] NULL ,
[PayCodeEvent] [bit] NULL ,
[Paycode] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Consecutive_Days] [bit] NULL ,
[AddToDropOffPeriod] [bit] NULL ,
CONSTRAINT [PK_tbl_Event_Types] PRIMARY KEY CLUSTERED
(
[EventID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
There are currently no rows that match the query, but I want to get
back a zero if that is the case. In other words, no rows = 0 for my
purposes. This query is in a stored procedure that returns the number
of points an employee has, so if there are no points I'd like a zero,
to do further processing with that zero. I could mess with the VB code
to make it zero there if there's no rows in the recordset, but this is
something that I've done with Oracle and is very useful in lots of
cases. I've Googled and seen responses that the
isnull(sum(isnull(expression, value)), value) should work, so I'm
missing something here!
Thanks
Pat|||If you want to see the groups where no matching rows exist by the where
clause, then you have to use [ALL] in the group by clause.
-- this is to reproduce what is happening to you
select c1, isnull(sum(1), 0)
from (select 1) as t1(c1)
where (c1 = 2)
group by c1
-- this is what you want
select c1, isnull(sum(1), 0)
from (select 1) as t1(c1)
where (c1 = 2)
group by all c1
go
AMB
"miapjp@.gmail.com" wrote:

> Hi,
> I'm having a heck of a time trying to get SQL Server 2000 to return a
> zero (0) if no rows are returned in a sum() statement. I know in
> Oracle you wrap an NVL around the whole shebang, and in Googling it
> seems as if SQL Server should work this way, too. But I cannot get it
> to work, I just keep getting null.
> I have tried:
> what I thought would work:
>
> SELECT ISNULL(SUM(ISNULL(PointValue, 0)), 0)
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()
> GROUP BY [File #]
> a case statement testing for null:
> SELECT CASE
> WHEN SUM(ISNULL(PointValue, 0)) IS NULL THEN 0
> ELSE SUM(ISNULL(PointValue, 0))
> END
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()
> GROUP BY [File #]
>
> wrapping the whole select statement in ISNULL as a subquery:
> SELECT ISNULL((SELECT SUM(ISNULL(PointValue, 0))
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()), 0)
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()
> and then to make sure what I was getting back was null and not empty
> ....something:
> declare
> @.n_sum numeric(6, 2)
> SELECT @.n_sum = ISNULL(SUM(ISNULL(PointValue, 0)), 0)
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()
> GROUP BY [File #]
> print 'sum is: ' + convert(char(8), isnull(@.n_sum, 0))
> sum is: 0.00
> What the heck am I doing wrong? I tried setting ANSI_NULL off and on,
> etc...but no go. I guess I could get around by using the variable and
> then returning that to the VB (since this is a stored proc used to find
> one value to return to the VB6 program), but that seems kind of messy.
> I would appreciate any help!
> TIA
> Pat
>|||You are dealing with two separate questions.
1. What is the sum on each row when there are records
2. What are the number of records (only important if 0)
One possible solution:
SELECT into a temp table. If the temp table has no rows, insert a row with
0. SELECT back off the temp table for your return value.
Since you have a filter on [File #], another option is to select into a
value and then test @.@.RowCount and set the value to 0 if @.@.RowCount = 0.
SOmething like:
DECLARE @.ReturnValue decimal
SELECT @.ReturnValue = ISNULL(SUM(ISNULL(PointValue, 0)), 0)
FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
tbl_event_types et ON ae.[event type] = et.eventID
WHERE [File #] = '0001001047'
AND EventDate BETWEEN '2/24/2005' AND getdate()
GROUP BY [File #]
IF (@.@.ROWCOUNT = 0)
SET @.ReturnValue = 0
SELECT @.ReturnValue
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"miapjp@.gmail.com" wrote:

> Hi,
> I'm having a heck of a time trying to get SQL Server 2000 to return a
> zero (0) if no rows are returned in a sum() statement. I know in
> Oracle you wrap an NVL around the whole shebang, and in Googling it
> seems as if SQL Server should work this way, too. But I cannot get it
> to work, I just keep getting null.
> I have tried:
> what I thought would work:
>
> SELECT ISNULL(SUM(ISNULL(PointValue, 0)), 0)
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()
> GROUP BY [File #]
> a case statement testing for null:
> SELECT CASE
> WHEN SUM(ISNULL(PointValue, 0)) IS NULL THEN 0
> ELSE SUM(ISNULL(PointValue, 0))
> END
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()
> GROUP BY [File #]
>
> wrapping the whole select statement in ISNULL as a subquery:
> SELECT ISNULL((SELECT SUM(ISNULL(PointValue, 0))
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()), 0)
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()
> and then to make sure what I was getting back was null and not empty
> ....something:
> declare
> @.n_sum numeric(6, 2)
> SELECT @.n_sum = ISNULL(SUM(ISNULL(PointValue, 0)), 0)
> FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
> tbl_event_types et ON ae.[event type] = et.eventID
> WHERE [File #] = '0001001047'
> AND EventDate BETWEEN '2/24/2005' AND getdate()
> GROUP BY [File #]
> print 'sum is: ' + convert(char(8), isnull(@.n_sum, 0))
> sum is: 0.00
> What the heck am I doing wrong? I tried setting ANSI_NULL off and on,
> etc...but no go. I guess I could get around by using the variable and
> then returning that to the VB (since this is a stored proc used to find
> one value to return to the VB6 program), but that seems kind of messy.
> I would appreciate any help!
> TIA
> Pat
>|||> There are currently no rows that match the query, but I want to get
> back a zero if that is the case.
Assuming that PointValue will always be positive if it *has* a value, you
can do this ugly stuff...
SELECT MAX(foo) FROM
(SELECT foo = COALESCE(SUM(PointValue),0)
FROM dbo.[tbl_Attendance_Event] ae INNER JOIN
tbl_event_types et ON ae.[event type] = et.eventID
WHERE [File #] = '0001001047'
AND EventDate BETWEEN '20050224' -- m/dd/yyyy is a terrible format
AND getdate()
UNION SELECT foo = 0) x|||>> ..get SQL Server 2000 to return a zero (0) if no rows are returned in a s
um() statement <<
SELECT COALESCE (SUM(i), 0) FROM EmptyTable;
However, what you are actually getting back is an empty set (ab nulo,
ex nulo) and the empty set is converted into a NULL. This is important
when you use EXISTS() predicates.|||>> There are currently no rows that match the query, but I want to get
back a zero if that is the case. In other words, no rows = 0 for my
purposes.
<<
then why don't you add a dummy row to every group:
select a, sum(b) from(
select a,b from some_table
union all
select distinct a, 0 from some_table) t|||Aha!
when I have the where clause limiting the sum, I don't need the group
by (right?). The original code also had the [file #] (again, not my
code, I don't make column names with spaces!!) in the select statement
even though it wasn't ever used - I don't know, I'm trying to make this
whole application work with bad code and bad underlying database
structure. In any case, I was trying to shoehorn in code to existing
code and not seeing the group by wasn't needed.
Thanks!!!!!

Can't refer to Column

Hello I have this query:
SELECT ClientCred.ClientID, SUM(ClientCred.Balance) AS TOTBalance
FROM ClientCred INNER JOIN
Clients ON ClientCred.ClientID = Clients.ClientID
WHERE (Clients.Active = '-1') AND (TOTBalance > 0)
GROUP BY ClientCred.ClientID
In the where clause, I need to filter out all results that have a balance
greater than 0, so I refer to the column I created in the select clause. I
get 'Invalid Column Name TOTBalance'. Can someone please explain why this
does not work and what a solution would be? Performing the SUM in the where
clause gives an error about having an aggregate in the where.
Thanks in advance!Because server will evaluate the Where clause first.
Try this:
SELECT ClientCred.ClientID, SUM(ClientCred.Balance) AS TOTBalance
FROM ClientCred INNER JOIN
Clients ON ClientCred.ClientID = Clients.ClientID
WHERE (Clients.Active = '-1') AND (SUM(ClientCred.Balance) > 0)
GROUP BY ClientCred.ClientID
Perayu
"Amon Borland" <AmonBorland@.+nospam+gmail.com> wrote in message
news:uZ1sA1JtFHA.3080@.TK2MSFTNGP15.phx.gbl...
> Hello I have this query:
> SELECT ClientCred.ClientID, SUM(ClientCred.Balance) AS TOTBalance
> FROM ClientCred INNER JOIN
> Clients ON ClientCred.ClientID = Clients.ClientID
> WHERE (Clients.Active = '-1') AND (TOTBalance > 0)
> GROUP BY ClientCred.ClientID
> In the where clause, I need to filter out all results that have a balance
> greater than 0, so I refer to the column I created in the select clause.
> I get 'Invalid Column Name TOTBalance'. Can someone please explain why
> this does not work and what a solution would be? Performing the SUM in
> the where clause gives an error about having an aggregate in the where.
> Thanks in advance!
>
>|||WHERE clauses deal with individual rows underlying the aggragate query;
if you need to refer to the aggragate, use HAVING instead:
SELECT ClientCred.ClientID, SUM(ClientCred.Balance) AS TOTBalance
FROM ClientCred INNER JOIN
Clients ON ClientCred.ClientID = Clients.ClientID
WHERE (Clients.Active = '-1')
GROUP BY ClientCred.ClientID
HAVING SUM(ClientCred.Balance) >0
HTH,
Stu|||Thanks Stu, this works!
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1126202094.969185.94760@.g44g2000cwa.googlegroups.com...
> WHERE clauses deal with individual rows underlying the aggragate query;
> if you need to refer to the aggragate, use HAVING instead:
> SELECT ClientCred.ClientID, SUM(ClientCred.Balance) AS TOTBalance
> FROM ClientCred INNER JOIN
> Clients ON ClientCred.ClientID = Clients.ClientID
> WHERE (Clients.Active = '-1')
> GROUP BY ClientCred.ClientID
> HAVING SUM(ClientCred.Balance) >0
> HTH,
> Stu
>|||Still doesn't work Perayu, can't have aggregate in where clause.
Thanks for the reply Stu's works.
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:uc68I5JtFHA.3868@.TK2MSFTNGP10.phx.gbl...
> Because server will evaluate the Where clause first.
> Try this:
> SELECT ClientCred.ClientID, SUM(ClientCred.Balance) AS TOTBalance
> FROM ClientCred INNER JOIN
> Clients ON ClientCred.ClientID = Clients.ClientID
> WHERE (Clients.Active = '-1') AND (SUM(ClientCred.Balance) > 0)
> GROUP BY ClientCred.ClientID
> Perayu
> "Amon Borland" <AmonBorland@.+nospam+gmail.com> wrote in message
> news:uZ1sA1JtFHA.3080@.TK2MSFTNGP15.phx.gbl...
>

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.
> > > > > >> >
> > > > > >> >
> > > > > >>
> > > > > >>
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>