2

Using SS reporting service 2008, I'm looking at setting up a default report on sale figures through the month grouping by date.

In theory it should look a little like:

Date         Total Sales    Qty 
----------------------------------
01/04/2011   $15.00         3
02/04/2011   $20.00         4
03/04/2011   $00.00         0
Etc

Problem I'm having is the days of the month that have no records are being skipped. (Like 03/04/2011 above). Is there a way to show the dates that have no records?

Nathan
  • 11,938
  • 12
  • 55
  • 62
jbaxter87
  • 21
  • 2

2 Answers2

1

There is no way to specifically do this in SSRS, but it can be accomplished in the SQL query.

You need to generate a temporary table (a permanent table in the database could also be good, if you are going to be doing this alot) with a list of sequential dates in it, and then join this onto the existing data query with an outer join. This way dates with no data will appear as null entries in the table after the join.

For example, if you had the following tables

DateList

Date
----
2011-01-01
2011-01-02
2011-01-03
2011-01-04
2011-01-05
etc...

SalesData

Date        Qty
----------------
2011-01-01  5
2011-01-02  4
2011-01-04  7

then you could use the following query to get the sales data with the null records for days where there are no sales recorded.

select 
    dl.Date, 
    sd.Qty
from SalesData sd 
    left outer join DateList dl on dl.Date = sd.Date


Date    Qty
--------------
2011-01-01  5
2011-01-02  4
2011-01-03  (null)
2011-01-04  7
2011-01-05  (null)
etc...

See this question for information on generating a list of dates to join on.

Community
  • 1
  • 1
Nathan
  • 11,938
  • 12
  • 55
  • 62
0

To expand on Nathan's answer, to avoid nulls your select statement could use isnull on the sd.QTY field:
isnull(sd.QTY,0) as 'Qty'

Erik
  • 161
  • 2
  • 12