How to Force Zero Data Points in Date Charts (SSRS)

My team was recently challenged with producing a line chart that uses CRM data to provide counts of records over a period of time. What we found is that if there is not data for a given day, it is quite hard to force the line to have a zero data point instead of completely ignoring the given day. This post explains one of the solutions we found.

It is quite natural to want to report in chart data such as “Show me per day the count of cases that are open on that specific day”. However, the requirement was also that if there are no cases open on a given day, the X axis should still contain the label for that day but the data point should be zero.


The problem: If you don’t have any records for a given date, the report will automatically exclude the date from the X axis as there is simply no datapoint (notice that empty or null datapoint is not the same as not having a datapoint at all!). This is what out chart looked like initially:





Solution Attempt #1: We played with the chart properties and the X axis interval to force the chart to show the X axis per day, even if no data exists for a particular day. This was quite simple, but the result is not as expected:


Note that the line above is extrapolated over 4-Mar-12, but we expected the line to go to zero on that day so this solution did not meet our requirements.



Solution Attempt #2: Now we played with the EmtpyPoint and EmptyPointValue properties of the chart as follows:



However, it unfortunately did not have any effect on our chart because we simply did not have data points on 4-Mar-12, which is not the same as having a null or empty data point. Thus, this solution attempt did not help!


Solution Attempt #3 (CRM Only): Since this report is inside CRM, we tried to register a plugin on RetrieveMultiple which would generate some empty records for ech missing day in the date interval. However, we soon learned that CRM reports execute SQL directly and bypass the CRM pipeline which means that no plugins are ever triggered when retrieving CRM data for reports! Even though we were using FetchXML as the data source for the reports, the plugins were not triggered on either the RetrieveMultiple or the Execute message.



Solution Attempt #4: At this point it was clear that we needed a datapoint for each possible day, and if we want to line to go to zero we would need an empty/null data point for those days for which there are no records. We were using FetchXML data source for this report (CRM) which was creating some restrictions because we could not manipulate the data retrieved. So we had to change the data source to SQL to be able to build a smarter and more complex query which could “generate” the empty data points for those days for which there were no records. After applying this solution, we finally got the expected result:

Solution details:

So how do we generate empty data-points when there is no record for that particular day? We do this in 2 steps:

1. Generate a “DateRanges” table that contains a record for each day in the interval that the graph wants to display:

-- Select the Begin Interval based on Data
DECLARE @DateFrom DATETIME
SET @DateFrom = (select top(1) ava_date from ('+@CRM_FilteredIncident+') AS ED order by ava_date asc)

-- Select the End Interval based on Data
DECLARE @DateTo DATETIME
SET @DateTo = (select top(1) ava_date from ('+@CRM_FilteredIncident+') AS ED order by ava_date desc)

-- Generate a table with one entry per day
;WITH DateRanges AS
(
SELECT @DateFrom AS 'DateValue'
UNION ALL
SELECT DATEADD(DAY, 1, DateValue)
FROM DateRanges
WHERE DateValue < @DateTo
)

2. Do an outer join with your data, so then for each possible day, there is going to be at least one record. These commands should be the query in your report DataSet.

SELECT
ED.ava_date,
ED.ava_datetype,
ED.ava_datetypename,
DateRanges.DateValue
FROM
DateRanges
left outer join ('+@CRM_FilteredIncident+') AS ED on ED.ava_date = DateRanges.DateValue

It is important when you define your chart’s YValue that you use Count on a field that is not the date field generated (since the count will never be zero). In the Category Groups you would need to select the generated dates so each day will have an entry in the X axis:





Also note that we are using parameterized query that makes use of @CRM_FilteredIncident which is used for supporting pre-filtering data in CRM reports. Otherwise you would just have your table name instead of ('+@CRM_FilteredIncident+').