Analysing sales trends in Matrix 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

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Analysing sales trends in Matrix report

Author

Topic

davidhole
Starting Member

1 Post

Posted - 2008-09-03 : 06:04:46

We have the following data in SQL 2005,
Posting Date Item No. Qty
01/07/2008 12:00 AM 001 3
01/07/2008 12:00 AM 001 4
01/07/2008 12:00 AM 002 9
21/07/2008 12:00 AM 003 3
22/07/2008 12:00 AM 003 1
01/08/2008 12:00 AM 001 2
01/08/2008 12:00 AM 002 2
01/08/2008 12:00 AM 001 1
01/08/2008 12:00 AM 003 3
02/08/2008 12:00 AM 001 2
02/08/2008 12:00 AM 003 1
02/08/2008 12:00 AM 002 2
02/08/2008 12:00 AM 001 2
03/08/2008 12:00 AM 001 1
03/08/2008 12:00 AM 001 2

What I’m trying to achieve is a matrix report that will show the sum of the qty grouped my month so we can analyse sales trends, there will also be a startdate and enddate filter on posting date so we can pull in the last year’s results or the last 3 months or whatever date range we choose.

July 08 Aug 08 Sep 08
Item No.
001 7 10
002 9 4
003 4 4

Will Riley
Starting Member

10 Posts

Posted - 2008-09-04 : 17:21:38

I'd change your SQL to add a "MonthYear" filed to the dataset and group your columns using that in the matrix... ie.

SELECT DataDate, Item, Sales, CAST(year(datadate) AS varchar(4))+'-'+cast(month(datadate) as varchar(2)) as 'MonthYear'
from YourTable

Cheers,

Will
http://wills-blog.com

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-09-05 : 05:02:33

Good solution. If they want to print it, you'll just need to have a mechanism to control the number of cols (yr/mth) in your date range so it doesn't blow-out your page size.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources