hide columns with no data

I'm trying to create a table where if the column has no data then it is not
visible. e.g. Sum(values)=0 then hide=true type of thing
This is causing a problem with printing PDF as this still includes all the
blank columns, I understand the explanation as to why this happens.
I've scoured the web and have seen many suggestions but nothing seems to work.
The 'cangrow' property is only valid for height not width.
It is not possible to set an expression on width etc etc
Did anyone ever figure out a way around this issue?I would suggest you to do this hiding part or remove the column itself using
your sql query and use matrix so that depending on the column this will be
displayed.
and all this can be done, provided you dont change/show/hide the columns
very frequently.
Amarnath
"adolf garlic" wrote:
> I'm trying to create a table where if the column has no data then it is not
> visible. e.g. Sum(values)=0 then hide=true type of thing
> This is causing a problem with printing PDF as this still includes all the
> blank columns, I understand the explanation as to why this happens.
> I've scoured the web and have seen many suggestions but nothing seems to work.
> The 'cangrow' property is only valid for height not width.
> It is not possible to set an expression on width etc etc
> Did anyone ever figure out a way around this issue?

Hi

I'm cuestion
how to update at all
this is my sentence
update Table1
set Total = sum(b.monto)
from Table1 a , Details b
Where a.numerCode = b.numerCode
the answer is : an aggregate may not apper in the set list of an Update
statement.
Thanks for you help.
ChreesHello, MJ
Try something like this:
update Table1 set Total = (
select sum(b.monto) from Details b
Where a.numerCode = b.numerCode
)
from Table1 a
where exists (
select * from Details b
Where a.numerCode = b.numerCode
)
or:
update Table1 set Total = isnull((
select sum(b.monto) from Details b
Where a.numerCode = b.numerCode
),0)
from Table1 a
Razvan|||Thanks
try Ok.
thank you for you help.
"Razvan Socol" escribió:
> Hello, MJ
> Try something like this:
> update Table1 set Total = (
> select sum(b.monto) from Details b
> Where a.numerCode = b.numerCode
> )
> from Table1 a
> where exists (
> select * from Details b
> Where a.numerCode = b.numerCode
> )
> or:
> update Table1 set Total = isnull((
> select sum(b.monto) from Details b
> Where a.numerCode = b.numerCode
> ),0)
> from Table1 a
> Razvan
>

Hi

I'm cuestion
how to update at all
this is my sentence
update Table1
set Total = sum(b.monto)
from Table1 a , Details b
Where a.numerCode = b.numerCode
the answer is : an aggregate may not apper in the set list of an Update
statement.
Thanks for you help.
ChreesHello, MJ
Try something like this:
update Table1 set Total = (
select sum(b.monto) from Details b
Where a.numerCode = b.numerCode
)
from Table1 a
where exists (
select * from Details b
Where a.numerCode = b.numerCode
)
or:
update Table1 set Total = isnull((
select sum(b.monto) from Details b
Where a.numerCode = b.numerCode
),0)
from Table1 a
Razvan|||Thanks
try Ok.
thank you for you help.
"Razvan Socol" escribió:

> Hello, MJ
> Try something like this:
> update Table1 set Total = (
> select sum(b.monto) from Details b
> Where a.numerCode = b.numerCode
> )
> from Table1 a
> where exists (
> select * from Details b
> Where a.numerCode = b.numerCode
> )
> or:
> update Table1 set Total = isnull((
> select sum(b.monto) from Details b
> Where a.numerCode = b.numerCode
> ),0)
> from Table1 a
> Razvan
>

Here's the Problem Again With Sample Data

I want the sum of the last payments (amount) for all customers. The last
payment is with one with most recent date. And if there are more than one
payment on the most recent date then the one with the higher paymentid is
the last payment. for example in the given data the insert statement that
starts with capital I is the last payment of that customer. The correct
answer should be 2100 as given below. both queries by Erland and Anith give
the result 100 ( I removed the "WHERE p1.date <= '20030301' " Clause from
both queries since right now I want current sum (not till some date). So
what should be the right query.
Thanks again for the help.
create table payments (
paymentid int,
customerid int,
amount int,
date datetime
)
insert payments values (1, 1, 100, '1/1/03')
insert payments values (2, 1, 200, '2/28/03')
Insert payments values (3, 1, 500, '5/15/03')
insert payments values (4, 2, 400, '1/16/03')
insert payments values (9, 2, 800, '4/30/03')
insert payments values (5, 2, 200, '6/15/03')
Insert payments values (6, 2, 900, '6/15/03')
insert payments values (7, 3, 700, '3/1/03')
insert payments values (10,3, 300, '7/10/03')
Insert payments values (8, 3, 600, '9/1/03')
insert payments values (11,4, 300, '8/1/03')
insert payments values (12,4, 400, '9/10/03')
Insert payments values (13,4, 100, '9/10/03')
customerid lastpayment amount
1 3 (on 5/15/03) 500
2 6 (on 6/15/03) 900
3 8 (on 9/1/03) 600
4 13 (on 9/10/03) 100
======== Result => 2100select customerid,paymentid,[date],amount
from payments p1
where paymentid=(select top 1 paymentid from payments p2 where
p2.customerid=p1.customerid order by [date] desc, paymentid desc)
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"MAB" <dsfoalsdfsdfadouisdf@.yahoo.com> wrote in message
news:bjmcjb$klvrf$1@.ID-31123.news.uni-berlin.de...
> I want the sum of the last payments (amount) for all customers. The last
> payment is with one with most recent date. And if there are more than one
> payment on the most recent date then the one with the higher paymentid is
> the last payment. for example in the given data the insert statement that
> starts with capital I is the last payment of that customer. The correct
> answer should be 2100 as given below. both queries by Erland and Anith
give
> the result 100 ( I removed the "WHERE p1.date <= '20030301' " Clause from
> both queries since right now I want current sum (not till some date). So
> what should be the right query.
> Thanks again for the help.
> create table payments (
> paymentid int,
> customerid int,
> amount int,
> date datetime
> )
> insert payments values (1, 1, 100, '1/1/03')
> insert payments values (2, 1, 200, '2/28/03')
> Insert payments values (3, 1, 500, '5/15/03')
> insert payments values (4, 2, 400, '1/16/03')
> insert payments values (9, 2, 800, '4/30/03')
> insert payments values (5, 2, 200, '6/15/03')
> Insert payments values (6, 2, 900, '6/15/03')
> insert payments values (7, 3, 700, '3/1/03')
> insert payments values (10,3, 300, '7/10/03')
> Insert payments values (8, 3, 600, '9/1/03')
> insert payments values (11,4, 300, '8/1/03')
> insert payments values (12,4, 400, '9/10/03')
> Insert payments values (13,4, 100, '9/10/03')
>
> customerid lastpayment amount
> 1 3 (on 5/15/03) 500
> 2 6 (on 6/15/03) 900
> 3 8 (on 9/1/03) 600
> 4 13 (on 9/10/03) 100
> ========> Result => 2100
>
>|||Thanks! this works. However its too slow to run on the actual table with
thousands of rows but i've managed to eliminate most of the rows by creating
a temporary table and then I run this query on the temporary table.
"oj" <nospam_ojngo@.home.com> wrote in message
news:uqjta41dDHA.2320@.TK2MSFTNGP12.phx.gbl...
> select customerid,paymentid,[date],amount
> from payments p1
> where paymentid=(select top 1 paymentid from payments p2 where
> p2.customerid=p1.customerid order by [date] desc, paymentid desc)
>
> --
> -oj
> RAC v2.2 & QALite!
> http://www.rac4sql.net
>
> "MAB" <dsfoalsdfsdfadouisdf@.yahoo.com> wrote in message
> news:bjmcjb$klvrf$1@.ID-31123.news.uni-berlin.de...
> > I want the sum of the last payments (amount) for all customers. The last
> > payment is with one with most recent date. And if there are more than
one
> > payment on the most recent date then the one with the higher paymentid
is
> > the last payment. for example in the given data the insert statement
that
> > starts with capital I is the last payment of that customer. The correct
> > answer should be 2100 as given below. both queries by Erland and Anith
> give
> > the result 100 ( I removed the "WHERE p1.date <= '20030301' " Clause
from
> > both queries since right now I want current sum (not till some date). So
> > what should be the right query.
> >
> > Thanks again for the help.
> >
> > create table payments (
> > paymentid int,
> > customerid int,
> > amount int,
> > date datetime
> > )
> >
> > insert payments values (1, 1, 100, '1/1/03')
> > insert payments values (2, 1, 200, '2/28/03')
> > Insert payments values (3, 1, 500, '5/15/03')
> >
> > insert payments values (4, 2, 400, '1/16/03')
> > insert payments values (9, 2, 800, '4/30/03')
> > insert payments values (5, 2, 200, '6/15/03')
> > Insert payments values (6, 2, 900, '6/15/03')
> >
> > insert payments values (7, 3, 700, '3/1/03')
> > insert payments values (10,3, 300, '7/10/03')
> > Insert payments values (8, 3, 600, '9/1/03')
> >
> > insert payments values (11,4, 300, '8/1/03')
> > insert payments values (12,4, 400, '9/10/03')
> > Insert payments values (13,4, 100, '9/10/03')
> >
> >
> > customerid lastpayment amount
> >
> > 1 3 (on 5/15/03) 500
> > 2 6 (on 6/15/03) 900
> > 3 8 (on 9/1/03) 600
> > 4 13 (on 9/10/03) 100
> >
> > ========> > Result => 2100
> >
> >
> >
> >
>|||MAB (fkdfjdierkjflafdafa@.yahoo.com) writes:
> Thanks! this works. However its too slow to run on the actual table with
> thousands of rows but i've managed to eliminate most of the rows by
> creating a temporary table and then I run this query on the temporary
> table.
You might need to review your indexes. It is difficult to test
performance on the small sample, but I would try somthing like:
CREATE CLUSTERED INDEX payments_index ON
payments(customerid, date DESC, paymentid DESC)
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||As Erland has suggested you should visit your indexing strategy. There is a
cost for creating the temp table and inserting data into it. With proper
index, this should be a breeze.
--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"MAB" <fkdfjdierkjflafdafa@.yahoo.com> wrote in message
news:bjoghs$l7pi0$1@.ID-31123.news.uni-berlin.de...
> Thanks! this works. However its too slow to run on the actual table with
> thousands of rows but i've managed to eliminate most of the rows by
creating
> a temporary table and then I run this query on the temporary table.
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:uqjta41dDHA.2320@.TK2MSFTNGP12.phx.gbl...
> > select customerid,paymentid,[date],amount
> > from payments p1
> > where paymentid=(select top 1 paymentid from payments p2 where
> > p2.customerid=p1.customerid order by [date] desc, paymentid desc)
> >
> >
> > --
> > -oj
> > RAC v2.2 & QALite!
> > http://www.rac4sql.net
> >
> >
> >
> > "MAB" <dsfoalsdfsdfadouisdf@.yahoo.com> wrote in message
> > news:bjmcjb$klvrf$1@.ID-31123.news.uni-berlin.de...
> > > I want the sum of the last payments (amount) for all customers. The
last
> > > payment is with one with most recent date. And if there are more than
> one
> > > payment on the most recent date then the one with the higher paymentid
> is
> > > the last payment. for example in the given data the insert statement
> that
> > > starts with capital I is the last payment of that customer. The
correct
> > > answer should be 2100 as given below. both queries by Erland and Anith
> > give
> > > the result 100 ( I removed the "WHERE p1.date <= '20030301' " Clause
> from
> > > both queries since right now I want current sum (not till some date).
So
> > > what should be the right query.
> > >
> > > Thanks again for the help.
> > >
> > > create table payments (
> > > paymentid int,
> > > customerid int,
> > > amount int,
> > > date datetime
> > > )
> > >
> > > insert payments values (1, 1, 100, '1/1/03')
> > > insert payments values (2, 1, 200, '2/28/03')
> > > Insert payments values (3, 1, 500, '5/15/03')
> > >
> > > insert payments values (4, 2, 400, '1/16/03')
> > > insert payments values (9, 2, 800, '4/30/03')
> > > insert payments values (5, 2, 200, '6/15/03')
> > > Insert payments values (6, 2, 900, '6/15/03')
> > >
> > > insert payments values (7, 3, 700, '3/1/03')
> > > insert payments values (10,3, 300, '7/10/03')
> > > Insert payments values (8, 3, 600, '9/1/03')
> > >
> > > insert payments values (11,4, 300, '8/1/03')
> > > insert payments values (12,4, 400, '9/10/03')
> > > Insert payments values (13,4, 100, '9/10/03')
> > >
> > >
> > > customerid lastpayment amount
> > >
> > > 1 3 (on 5/15/03) 500
> > > 2 6 (on 6/15/03) 900
> > > 3 8 (on 9/1/03) 600
> > > 4 13 (on 9/10/03) 100
> > >
> > > ========> > > Result => 2100
> > >
> > >
> > >
> > >
> >
> >
>

Heres the Problem Again With Sample Data

I want the sum of the last payments (amount) for all customers. The last
payment is with one with most recent date. And if there are more than one
payment on the most recent date then the one with the higher paymentid is
the last payment. for example in the given data the insert statement that
starts with capital I is the last payment of that customer. The correct
answer should be 2100 as given below. both queries by Erland and Anith give
the result 100 ( I removed the "WHERE p1.date <= '20030301' " Clause from
both queries since right now I want current sum (not till some date). So
what should be the right query.

Thanks again for the help.

create table payments (
paymentid int,
customerid int,
amount int,
date datetime
)

insert payments values (1, 1, 100, '1/1/03')
insert payments values (2, 1, 200, '2/28/03')
Insert payments values (3, 1, 500, '5/15/03')

insert payments values (4, 2, 400, '1/16/03')
insert payments values (9, 2, 800, '4/30/03')
insert payments values (5, 2, 200, '6/15/03')
Insert payments values (6, 2, 900, '6/15/03')

insert payments values (7, 3, 700, '3/1/03')
insert payments values (10,3, 300, '7/10/03')
Insert payments values (8, 3, 600, '9/1/03')

insert payments values (11,4, 300, '8/1/03')
insert payments values (12,4, 400, '9/10/03')
Insert payments values (13,4, 100, '9/10/03')

customerid lastpayment amount

1 3 (on 5/15/03) 500
2 6 (on 6/15/03) 900
3 8 (on 9/1/03) 600
4 13 (on 9/10/03) 100

========
Result => 2100select customerid,paymentid,[date],amount
from payments p1
where paymentid=(select top 1 paymentid from payments p2 where
p2.customerid=p1.customerid order by [date] desc, paymentid desc)

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net

"MAB" <dsfoalsdfsdfadouisdf@.yahoo.com> wrote in message
news:bjmcjb$klvrf$1@.ID-31123.news.uni-berlin.de...
> I want the sum of the last payments (amount) for all customers. The last
> payment is with one with most recent date. And if there are more than one
> payment on the most recent date then the one with the higher paymentid is
> the last payment. for example in the given data the insert statement that
> starts with capital I is the last payment of that customer. The correct
> answer should be 2100 as given below. both queries by Erland and Anith
give
> the result 100 ( I removed the "WHERE p1.date <= '20030301' " Clause from
> both queries since right now I want current sum (not till some date). So
> what should be the right query.
> Thanks again for the help.
> create table payments (
> paymentid int,
> customerid int,
> amount int,
> date datetime
> )
> insert payments values (1, 1, 100, '1/1/03')
> insert payments values (2, 1, 200, '2/28/03')
> Insert payments values (3, 1, 500, '5/15/03')
> insert payments values (4, 2, 400, '1/16/03')
> insert payments values (9, 2, 800, '4/30/03')
> insert payments values (5, 2, 200, '6/15/03')
> Insert payments values (6, 2, 900, '6/15/03')
> insert payments values (7, 3, 700, '3/1/03')
> insert payments values (10,3, 300, '7/10/03')
> Insert payments values (8, 3, 600, '9/1/03')
> insert payments values (11,4, 300, '8/1/03')
> insert payments values (12,4, 400, '9/10/03')
> Insert payments values (13,4, 100, '9/10/03')
>
> customerid lastpayment amount
> 1 3 (on 5/15/03) 500
> 2 6 (on 6/15/03) 900
> 3 8 (on 9/1/03) 600
> 4 13 (on 9/10/03) 100
> ========
> Result => 2100
>|||Thanks! this works. However its too slow to run on the actual table with
thousands of rows but i've managed to eliminate most of the rows by creating
a temporary table and then I run this query on the temporary table.

"oj" <nospam_ojngo@.home.com> wrote in message
news:uqjta41dDHA.2320@.TK2MSFTNGP12.phx.gbl...
> select customerid,paymentid,[date],amount
> from payments p1
> where paymentid=(select top 1 paymentid from payments p2 where
> p2.customerid=p1.customerid order by [date] desc, paymentid desc)
>
> --
> -oj
> RAC v2.2 & QALite!
> http://www.rac4sql.net
>
> "MAB" <dsfoalsdfsdfadouisdf@.yahoo.com> wrote in message
> news:bjmcjb$klvrf$1@.ID-31123.news.uni-berlin.de...
> > I want the sum of the last payments (amount) for all customers. The last
> > payment is with one with most recent date. And if there are more than
one
> > payment on the most recent date then the one with the higher paymentid
is
> > the last payment. for example in the given data the insert statement
that
> > starts with capital I is the last payment of that customer. The correct
> > answer should be 2100 as given below. both queries by Erland and Anith
> give
> > the result 100 ( I removed the "WHERE p1.date <= '20030301' " Clause
from
> > both queries since right now I want current sum (not till some date). So
> > what should be the right query.
> > Thanks again for the help.
> > create table payments (
> > paymentid int,
> > customerid int,
> > amount int,
> > date datetime
> > )
> > insert payments values (1, 1, 100, '1/1/03')
> > insert payments values (2, 1, 200, '2/28/03')
> > Insert payments values (3, 1, 500, '5/15/03')
> > insert payments values (4, 2, 400, '1/16/03')
> > insert payments values (9, 2, 800, '4/30/03')
> > insert payments values (5, 2, 200, '6/15/03')
> > Insert payments values (6, 2, 900, '6/15/03')
> > insert payments values (7, 3, 700, '3/1/03')
> > insert payments values (10,3, 300, '7/10/03')
> > Insert payments values (8, 3, 600, '9/1/03')
> > insert payments values (11,4, 300, '8/1/03')
> > insert payments values (12,4, 400, '9/10/03')
> > Insert payments values (13,4, 100, '9/10/03')
> > customerid lastpayment amount
> > 1 3 (on 5/15/03) 500
> > 2 6 (on 6/15/03) 900
> > 3 8 (on 9/1/03) 600
> > 4 13 (on 9/10/03) 100
> > ========
> > Result => 2100|||MAB (fkdfjdierkjflafdafa@.yahoo.com) writes:
> Thanks! this works. However its too slow to run on the actual table with
> thousands of rows but i've managed to eliminate most of the rows by
> creating a temporary table and then I run this query on the temporary
> table.

You might need to review your indexes. It is difficult to test
performance on the small sample, but I would try somthing like:

CREATE CLUSTERED INDEX payments_index ON
payments(customerid, date DESC, paymentid DESC)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||As Erland has suggested you should visit your indexing strategy. There is a
cost for creating the temp table and inserting data into it. With proper
index, this should be a breeze.

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net

"MAB" <fkdfjdierkjflafdafa@.yahoo.com> wrote in message
news:bjoghs$l7pi0$1@.ID-31123.news.uni-berlin.de...
> Thanks! this works. However its too slow to run on the actual table with
> thousands of rows but i've managed to eliminate most of the rows by
creating
> a temporary table and then I run this query on the temporary table.
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:uqjta41dDHA.2320@.TK2MSFTNGP12.phx.gbl...
> > select customerid,paymentid,[date],amount
> > from payments p1
> > where paymentid=(select top 1 paymentid from payments p2 where
> > p2.customerid=p1.customerid order by [date] desc, paymentid desc)
> > --
> > -oj
> > RAC v2.2 & QALite!
> > http://www.rac4sql.net
> > "MAB" <dsfoalsdfsdfadouisdf@.yahoo.com> wrote in message
> > news:bjmcjb$klvrf$1@.ID-31123.news.uni-berlin.de...
> > > I want the sum of the last payments (amount) for all customers. The
last
> > > payment is with one with most recent date. And if there are more than
> one
> > > payment on the most recent date then the one with the higher paymentid
> is
> > > the last payment. for example in the given data the insert statement
> that
> > > starts with capital I is the last payment of that customer. The
correct
> > > answer should be 2100 as given below. both queries by Erland and Anith
> > give
> > > the result 100 ( I removed the "WHERE p1.date <= '20030301' " Clause
> from
> > > both queries since right now I want current sum (not till some date).
So
> > > what should be the right query.
> > > > Thanks again for the help.
> > > > create table payments (
> > > paymentid int,
> > > customerid int,
> > > amount int,
> > > date datetime
> > > )
> > > > insert payments values (1, 1, 100, '1/1/03')
> > > insert payments values (2, 1, 200, '2/28/03')
> > > Insert payments values (3, 1, 500, '5/15/03')
> > > > insert payments values (4, 2, 400, '1/16/03')
> > > insert payments values (9, 2, 800, '4/30/03')
> > > insert payments values (5, 2, 200, '6/15/03')
> > > Insert payments values (6, 2, 900, '6/15/03')
> > > > insert payments values (7, 3, 700, '3/1/03')
> > > insert payments values (10,3, 300, '7/10/03')
> > > Insert payments values (8, 3, 600, '9/1/03')
> > > > insert payments values (11,4, 300, '8/1/03')
> > > insert payments values (12,4, 400, '9/10/03')
> > > Insert payments values (13,4, 100, '9/10/03')
> > > > > customerid lastpayment amount
> > > > 1 3 (on 5/15/03) 500
> > > 2 6 (on 6/15/03) 900
> > > 3 8 (on 9/1/03) 600
> > > 4 13 (on 9/10/03) 100
> > > > ========
> > > Result => 2100
> > > >

Help: Question to the Group on Report Grouping

Has anyone successfully created a report that has a main Table and 2 Group
Levels and correctly shows aggregates (SUM) in both Group footers and the
Table footer?
Like this:
_______________________________________________________________
Table
Group #1
Group #2
Detail Row #1 (this shows fields that relate to Group #1. HideDuplicates is
set to True.
Detail Row #2 (this shows fields that relate to Group #2)
Group #2 Footer
Group #1 Footer
Table Footer
_______________________________________________________________
The data I am selecting contains, per row, all the data to populate the
detail rows. (It's basically a flat row that selects invoice and invoice
line item information. So it looks like this:
CustID, InvoiceID, Invoice Number, Invoice Amt, Invoice Line Item ID,
Invoice Line Item Amount...
1,001,123ABC,100.00,1,50
1,001,123ABC,100,2,50
... and so on...
The Invoice fields go in the first detail row. The invoice line item fields
go in the 2nd detail row. I'm just showing line items by invoice by
customer.
I have SUMs in the Group #2 footer. Works fine.
I have SUMs in the Group #1 footer. Problem: Since each detail row
contains the value, it adds them all. For example, if I put invoice amount
in Detail Row #1 and SUM(invoiceamount) in the Group #1 footer it adds
invoice amount in every detail row so, of course, the value isn't correct.
Now, it only shows on the report once because of HideDuplicates is true.
The same applies to what goes in the Table footer.
Is this report design wrong? Any ideas on how to handle this'
--
Adrian M.
MCPHi Adrian,
I'm not 100% sure I understand how you are designing your report, how's
this?: You have many invoices, and each invoice has many line items,
right? So for each invoice you want a sum of the invoice amount?
Without knowing what your groups are, here is what I would suggest, I
hope this helps: I would add another group - this group would be where
you put your invoice fields. There would then only be 1 detail row for
your line items.
Group #1 ?
Group #2 ?
Group #3 invoice info
Detail Row line items
Take care,
Michelle|||I think my question is similar. I'm trying to total items that are grouped
but I don't want all the items totaled, just the grouping. For example, I
have 1 - 1, 2-2's and 3-3's, which display as, 1-1, 2-2, 3-3 (in a table
format). I want to sum those 'groups', meaning the sum would be '6', not '14'.
How can I do that?
Bill
"Adrian M." wrote:
> Has anyone successfully created a report that has a main Table and 2 Group
> Levels and correctly shows aggregates (SUM) in both Group footers and the
> Table footer?
> Like this:
> _______________________________________________________________
> Table
> Group #1
> Group #2
> Detail Row #1 (this shows fields that relate to Group #1. HideDuplicates is
> set to True.
> Detail Row #2 (this shows fields that relate to Group #2)
> Group #2 Footer
> Group #1 Footer
> Table Footer
> _______________________________________________________________
> The data I am selecting contains, per row, all the data to populate the
> detail rows. (It's basically a flat row that selects invoice and invoice
> line item information. So it looks like this:
> CustID, InvoiceID, Invoice Number, Invoice Amt, Invoice Line Item ID,
> Invoice Line Item Amount...
> 1,001,123ABC,100.00,1,50
> 1,001,123ABC,100,2,50
> ... and so on...
> The Invoice fields go in the first detail row. The invoice line item fields
> go in the 2nd detail row. I'm just showing line items by invoice by
> customer.
>
> I have SUMs in the Group #2 footer. Works fine.
> I have SUMs in the Group #1 footer. Problem: Since each detail row
> contains the value, it adds them all. For example, if I put invoice amount
> in Detail Row #1 and SUM(invoiceamount) in the Group #1 footer it adds
> invoice amount in every detail row so, of course, the value isn't correct.
> Now, it only shows on the report once because of HideDuplicates is true.
> The same applies to what goes in the Table footer.
> Is this report design wrong? Any ideas on how to handle this'
> --
> Adrian M.
> MCP
>
>|||So are you trying to COUNT and not SUM?|||No, I'm trying to sum. I'm trying to su the values that are grouped, not all
the values of data set.
"Vivienne" wrote:
> So are you trying to COUNT and not SUM?
>|||I have a similar problem and I know there has to be a way to do it. My
dataset query returns results like this:
Company Value CaseNumber CaseDate
---
Microsoft 134.40 00462 June 25
Microsoft 134.40 01568 May 6
Microsoft 134.40 00224 Sept 12
Sony 212.00 00986 Mar 22
Sony 212.00 02944 Dec 1
I want the company and value shown at the group level, and each case listed
in the details for each company. I can get everything to work except for a
grand total of the company's values.
Here is how I want the report to look:
Microsoft 134.40
00462 June 25
01568 May 6
00224 Sept 12
Sony 212.00
00986 Mar 22
02944 Dec 1
--
Total 346.40
Everything I've tried gives me an error, or returns a Total of 827.20
instead of 346.40.
Thanks in advance for any help.

Help: query to get total last 7 day sales for each day

Let say I have this table in MS SQL server
table transaction(date,sales)

how to query to get result like this (date,sales,sum(sales last 7 day))
I'm thinking about using self join, but it means I must have to self join 7 times to get the total sales for the last 7 day. Is there any better way to do this? or maybe special function within MS SQL server.

note: i'm not looking for total sales per week group by each week, but total last 7 day sales for each day

thanksselect date,sum(sales) from table where date between (getdate()-7) and getdate()
group by date|||That will get you sales within the last 7x24 hours, because getdate returns the full date and time. Maybe that's what you want, but if you are looking at full sales days you should try the datediff function:

select convert(char(10), date, 120), sum(sales)
from table
where datediff(yourdate, getdate()) < 7 --(or 6, depending upon whether you want to count the current day)
group by convert(char(10), date, 120)

Help: Complex Select Statement

Here is my SQL string:

"SELECT to_ordnum, to_orddate," _
& "(SELECT SUM((DDPROD.pr_stanmat * DDPROD.pr_prfact) *
(DOBOM2.b2_quant * DDORD.or_quant)) FROM DDPROD INNER JOIN DOBOM2 ON
DDPROD.pr_prodnum = DOBOM2.b2_prodnum INNER JOIN DDORD ON
DOBOM2.b2_orid = DDORD.or_id INNER JOIN DDTORD ON DDORD.OR_TOID =
DDTORD.TO_ID WHERE DOBOM2.b2_ordnum = ''order number here from result
of outer select) AS Total" _
& "FROM DDTORD WHERE to_trak2id IN (39, 40, 41) AND to_ordtype = 's'
AND to_status = 'c' GROUP BY to_ordnum, to_orddate ORDER BY to_ordnum
DESC"

The outter Select statement returns various amounts of order numbers
represented by 'to_ordnum' in the outer Select clause which has to
meet the critera in the outer WHERE clause. I would like to place
these numbers selected into the inner WHERE clause for the inner
select statement where DOMBOM2.b2_ordnum = ?the order selected by
outer select statement.

I have tried placing to_ordnum into that location but the SQL2000
server does not process it.

Any suggestions, ideas?

Thank you,

BrettOn 5 Nov 2004 10:34:48 -0800, brett wrote:

> Here is my SQL string:
> "SELECT to_ordnum, to_orddate," _
> & "(SELECT SUM((DDPROD.pr_stanmat * DDPROD.pr_prfact) *
> (DOBOM2.b2_quant * DDORD.or_quant)) FROM DDPROD INNER JOIN DOBOM2 ON
> DDPROD.pr_prodnum = DOBOM2.b2_prodnum INNER JOIN DDORD ON
> DOBOM2.b2_orid = DDORD.or_id INNER JOIN DDTORD ON DDORD.OR_TOID =
> DDTORD.TO_ID WHERE DOBOM2.b2_ordnum = ''order number here from result
> of outer select) AS Total" _
> & "FROM DDTORD WHERE to_trak2id IN (39, 40, 41) AND to_ordtype = 's'
> AND to_status = 'c' GROUP BY to_ordnum, to_orddate ORDER BY to_ordnum
> DESC"
> The outter Select statement returns various amounts of order numbers
> represented by 'to_ordnum' in the outer Select clause which has to
> meet the critera in the outer WHERE clause. I would like to place
> these numbers selected into the inner WHERE clause for the inner
> select statement where DOMBOM2.b2_ordnum = ?the order selected by
> outer select statement.
> I have tried placing to_ordnum into that location but the SQL2000
> server does not process it.
> Any suggestions, ideas?
> Thank you,
> Brett

God, what a mess you posted. If you have a SQL question, try to get the
SQL separated from the client code that is sending it.

The confusion seems to be because you have DDTORD both in the outer select
and the inner select. You should use a table alias to differentiate them.

Here's one possibility, reformatted so it can be read:

SELECT
TBL1.to_ordnum,
TBL1.to_orddate,
( SELECT
SUM(
( DDPROD.pr_stanmat * DDPROD.pr_prfact)
* ( DOBOM2.b2_quant * DDORD.or_quant)
)
FROM DDPROD
INNER JOIN DOBOM2
ON DDPROD.pr_prodnum = DOBOM2.b2_prodnum
INNER JOIN DDORD
ON DOBOM2.b2_orid = DDORD.or_id
INNER JOIN DDTORD AS TBL2
ON DDORD.or_toid = TBL2.to_id
WHERE DOBOM2.b2_ordnum = TBL1.to_ordnum
) AS Total
FROM DDTORD AS TBL1
WHERE TBL1.to_trak2id IN (39, 40, 41)
AND TBL1.to_ordtype = 's'
AND TBL1.to_status = 'c'
GROUP BY TBL1.to_ordnum, TBL1.to_orddate
ORDER BY TBL1.to_ordnum DESC

However, without seeing DDL and sample inserts, it's impossible to test
whether this is what you meant.