CODEWITHSUNDEEP
sql-serverdatereporting-servicesaggregationssrs-2014
3N1GM4
3N1GM4

Reputation: 3351

SSRS report with stored procedure Dataset and aggregation by month - how to handle "empty" months?

I have a SSRS report which calls a stored procedure to return a two columns dataset - one column is a Date and the other is a calculated column I created to show the month in the format MMM-YYYY.

Using this dataset I have created an aggregated bar chart report which shows the number of records per month for each month value which has at least one record in the dataset, however I would like to be able to include on the chart any months within the range of the dataset even if they do not contain any records.

So for example, where at the moment I see the following data points on my chart:

Month        Count
-----        -----
Oct-2015     2
Nov-2015     5
Jan-2016     3
Feb-2016     6

Dec-2015 is missing because my Dataset didn't return any records for it, so I would like to be able to plot a zero Count value on the chart for this month.

Now I know how I would normally do this in SQL, with some kind of calendar table LEFT JOINed in so that I can return the months with zero counts, but can I achieve the same in SSRS with a stored procedure as the target of the main dataset?

Upvotes: 1

Views: 382

Answers (1)

Pete Rennard-Cumming
Pete Rennard-Cumming

Reputation: 1618

If your grouping is on a date/time (or a number), you can make the axis type for it Scalar.

  • Create your chart, with Count as the value and Month as the Category Group
  • Right-click the grouped axis (horizontal in my example below) and go to the Axis Properties
  • Change the Axis type to Scalar, and change the Interval type to Months
  • Still in the axis properties, change the Number Category to Date, and the Type to Jan 2000 (or use a custom format)

month chart design month chart preview

My first instinct was to aggregate by an actual date, converted to the first of the month, instead of your MMM-yyyy string. However, it appears that SSRS (2008 R2 in my case) recognises text in the format MMM-yyyy as a valid date, so your existing procedure shouldn't need changing.

Upvotes: 1

Related Questions