How to write SSRS report that compares end of month values?

Question

I have an SSRS (SQL Server 2008 R2) report that shows aggregated information about items that meet a certain criteria at a specific point in time based on a reportDate parameter. This report is used to calculate month-end values.

I need to extend the report to show a comparison against previous (or subsequent) months based on a numberOfMonths parameter. For example with reportDate=2015-01-01 and numberOfMonths=4 I would expect to get a report with that compares the results of Jan 1st, Feb 1st, Mar 1st and Apr 1st.

I don't think I can use a range b/c some items that meet the criteria during parts of that range may not meet it at the month-end date.

The best analogy I can think of is an AR report where you would sum the outstanding invoices at the end of the month grouped by customer. An invoice created during a month but paid before the end of the month would not show up anywhere, but an invoice created Jan 15th and paid on Mar 15th would be included in the sum for the Feb 1st and Mar 1st columns.

What's the best way to accomplish this in SSRS?

This sounds like something you will need to do either in your data source (e.g. aggregating data in a new table, if this is a data warehouse or reporting database) or on the fly in your query for the report dataset. I don't think you will be able to achieve what you're describing in the report design itself. — Nathan Griffiths
@Nathan: I thought of doing it in the query but wasn't sure of the right terms to search for. Does this type of query have a common name? Could you point me to an example? — Jens Ehrich

Jens Ehrich Jens Ehrich · Accepted Answer · 2016-05-12T19:53:36

I was unable to find a native SSRS solution, so instead used a stored procedure that loops through the months:

@reportDate datetime,
@numberOfMonths int

DECLARE @results TABLE
(
  ID int,
  Column2 nvarchar(50),
  Column3 nvarchar(50),
)

-- get the end of month values for each month
WHILE @numberOfMonths > 0
BEGIN
  DECLARE @date datetime
  SET @date = DATEADD(month, DATEDIFF(month, 0, @reportDate) - @numberOfMonths + 2, 0)

  INSERT INTO @results
  SELECT       ID, Column1, Column2
  FROM         MyTable
  WHERE        ConditionToIncludeInThisPeriod

  SET @numberOfMonths -= 1
END

How to write SSRS report that compares end of month values?

1 Answers