SSRS Show Dates on Chart with Zero Counts

The users wanted a chart that had a count by day of a set of events. Some days did not have events and they wanted to see these days on the chart even though the days had a zero count. I tried to accomplish this with a CTE but I couldn’t get the CTE to return the data I wanted. So instead I looked for an SSRS solution.

My output looked like so:

So then in SSRS I created a stacked bar chart, and did the following.

  1. Right Click on the Horizonal Axis and click Horizontal Axis Properties
  2. Change the Axis Type to Scalar (Number/Dates)
  3. Change the Interval to 1
  4. Change the Intervalt Type to Number
  5. Click OK

Executed the report and it is working as planned.