CODEWITHSUNDEEP
sql-serverreporting-servicesssrs-2008
jbaxter87
jbaxter87

Reputation: 21

SSRS - Group by Date expressions

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?

Upvotes: 2

Views: 1182

Answers (2)

Erik
Erik

Reputation: 161

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'

Upvotes: 0

Nathan
Nathan

Reputation: 12314

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.

Upvotes: 1

Related Questions