SSRS 2005 empty rows problem - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

SSRS 2005 empty rows problem

Author

Topic

Danny_SQL_9876
Starting Member

8 Posts

Posted - 2011-12-16 : 18:26:02

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
Please help me fixing this issue. Many thanks in advance

pduffin
Yak Posting Veteran

68 Posts

Posted - 2011-12-16 : 19:23:09

Create a table with a date entry for everyday. Left join from this table to the data and use an isnull for the order price

Likes to run, hates the runs!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources