sql server reporting services: Sum Question -- sql server reporting services

You're in the

sql server reporting services

group:

Sum Question

Ryan Mcbee
5/3/2007 1:10:01 PM

sql server reporting services: I am working on a report that is a vendor forecast and need help on adding
the same items qty's for a particular date. If the results are on the same
date, I need them to be summed up. Check out the results I have now and what
I am looking to do. What logic would I use to do this?

The results currently look like this;

Item Qty Forecast Date
xy 2 4/7/2007
xy 3 4/7/2007
xy 5 5/7/2007

The results that I am looking for should be like this;
Item Qty Forecast Date
xy 5 4/7/2007

Re: Sum Question

Andy Potter
5/3/2007 4:15:31 PM

You want to create a group based on unique date values. You sum function
would then use the date group name as the scope. Something like =
Sum(Fields!Qty.Value, "date_group_name")

--
Andy Potter
blog : http://sqlreportingservices.spaces.live.com
info@(NOSPAM)lakeclaireenterprises.com
[quoted text, click to view]

Re: Sum Question

BERKE Janos
5/3/2007 10:14:51 PM

Hi Ryan,

It should be done in the data tier. You should aggregate them in the stored
procedure like this:

select
item,
sum(qty) As Qty,
ForecastDate
from
table
group by
item,
ForecastDate

Regards,

Janos

[quoted text, click to view]

Re: Sum Question

Ayman
5/4/2007 9:30:04 AM

On May 3, 4:10 pm, Ryan Mcbee <RyanMc...@discussions.microsoft.com>
[quoted text, click to view]

Make sure that you remove the time from the end of the date other wise
you'll get a grouping like this
4/7/2007 00:00:00
4/7/2007 00:00:01
4/7/2007 00:00:02

etc. There are also fancy sum commands and grouping commands that can
come in handy within reporting services. Berke's way is also
convenient in that you don't have to mess with setting things up in
Reporting Services which can be frustrating sometimes.

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

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

View other messages in the sql server reporting services group.

home · blog · groups

Questions? Comments? Contact the dn