December 17, 2011 at 7:44 am
Hi all,
I am having a problem with SSRS 2005 as following
Scenario: following is a sample table data. I am supposed to create a report with matrix in it and have a row group on day (Order Date) and column group on Month (Order Date) and the value as Order Price.
Order ID Order Date Order Price
1.......11-01-2011......5000
2.......11-03-2011......4600
3.......11-16-2011......7700
4.......11-30-2011......2450
5.......12-05-2011......4980
6.......12-11-2011......1000
7.......12-16-2011......6500
8.......12-12-2011......3000
9.......12-15-2011......1900
10......12-16-2011......3000
Problem: I created a report with a Matrix and grouped as specified in the scenario and I am getting results as following for the above table
Day November December
1...... 5000 .........0
3...... 4600 .........0
5...... 0 .........4980
11..... 0 .........1000
12..... 0 .........3000
15..... 0 .........1900
16..... 7770 .........9500
30..... 2450 ..........0
But my client wants every day(1 to 31) to show up on the report even though there were no orders placed, with 0 in the values for Months!
Please help me fixing this issue. Many thanks in advance
December 17, 2011 at 8:30 am
Use a calendar table and a LEFT JOIN to your order table.
December 17, 2011 at 8:31 am
Your query will need to return a 0 for any day that does not have data then.
Show us your query and we may be able to help produce the desired result.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply