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 i开发者_如何学Gos 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?
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.
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'
精彩评论