Historical Monthly Sales report

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Historical Monthly Sales report

Author

Topic

agenda9533
Starting Member

16 Posts

Posted - 2005-08-16 : 13:01:51

I have a sales table( id, date , techid, amount)
id date techid amount
-----------------------------------------
1 01/01/2005 tsmith 99.99
....
1054 08/16/2005 jborrow 29.99

I need to create historical report&graph with monthly totals (amount) for every agent: Jan,Feb...Aug.

Jan Feb... Aug
tsmith 15898.44 69352.88 586311.55

How can I aggregate daily data in order to get historical monthly breakdown.
Is there any function or option (in Report designer)?

Thank you!

walterlee78
Starting Member

4 Posts

Posted - 2005-08-16 : 16:04:02

use a sql statement like

SELECT techid, SUM(amount), month(date)
FROM ...
GROUP BY techid, month(date)

Then Use a matrix

agenda9533
Starting Member

16 Posts

Posted - 2005-08-16 : 17:47:17

Is there any possibility to change date breakdown dynamically.
Something like Parameter allowing choosing daily or weekly or monthly Total breakdown?????
THANK YOU.

quote:
Originally posted by walterlee78

use a sql statement like

SELECT techid, SUM(amount), month(date)
FROM ...
GROUP BY techid, month(date)

Then Use a matrix

gcowhsu
Starting Member

38 Posts

Posted - 2005-08-16 : 18:15:34

Yeah you have to use the iif statement in the datasource command.

iif(parameter=weekly, run weekly query, iif(parameter = monthly, run monthly query, iif(parameter = yearly, run yearly query, )))

something like that. YOu can probably also do it in the table or matrix, but i'm not sure.

agenda9533
Starting Member

16 Posts

Posted - 2005-08-17 : 16:25:56

Coolio! Thank you!

quote:
Originally posted by gcowhsu

Yeah you have to use the iif statement in the datasource command.

iif(parameter=weekly, run weekly query, iif(parameter = monthly, run monthly query, iif(parameter = yearly, run yearly query, )))

something like that. YOu can probably also do it in the table or matrix, but i'm not sure.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources