I have been working on a SSRS report that produces financial data for a rolling 20 month period. The SQL behind the report is pretty straight forward. The challenge was dynamically setting each of the column names on the report to accurately reflect the month/year data presented by the SQL.
This task is made a little simpler since the column names are derived from dates, making it possible to calculate the values of the column names.
The other challenge with this process is that the report can be run for any specific date. The obvious solution, and the one I used, is to pass a date parameter to a SQL stored procedure (You could have used straight SQL in SSRS and used the parameter as part of the WHERE clause – I just prefer to use stored procedures). The fun part of this getting SQL Server and SSRS to play nice together (I know this sounds obvious and should be straight forward.)
Steps
1. Create the stored procedure allowing for an input parameter to accept the date.
USE someDB
GO
IF OBJECT_ID('someDB.dbo.uspSampleProc') IS NOT NULL
DROP PROCEDURE uspSampleProc
GO
CREATE PROCEDURE [dbo].[uspSampleProc]
( @BaseDate DATETIME
)
AS
...
2. Create the report template in SSRS
3. Create the report parameter and associate with the SQL parameter.
To get there:
- Go to the Data page for the report
- Click the ellipse (“…”) next to the Dataset Drop down
- Then select the Parameters tab in the Dataset dialog box
5. Edit the expression in the column heading text box(es) to reflect the rolling month/year indicated by the report data.
=RIGHT("0" & CSTR(Month(DateAdd("M", 1,Parameters!BaseDate.Value))),2) & "-" & YEAR(DateAdd("M", 1, Parameters!BaseDate.Value))
What’s going on here?
The above example would be used in the second column header label on the report.
Using the report date parameter as the base, it is possible to calculate the text value for the column headers.
The column name will show the month and the year that the column represents. EX: 01-2008
To ensure that the month and year displayed are in synch with the rolling monthly data, two separate string functions are combined to form the final text.
The “Month” part
The first string function determines the month indicator. For this report, the month indicator needs to show with two characters. As the “Month” function in SSRS does not do this for us, it is necessary to add a leading “0” to the numeric identifier for months January through September.
To do this:
- Append a “0” to the left side of the numeric value that represents the month.
- Use the “Right” string function to pull the two right most characters from the string.
The second part of this string function determines the appropriate month to report. This is done using the “DateAdd” function. This function allows you to add or subtract from a date value to get anther date (more info on the DateAdd function here: MSDN SSRS DateAdd Function). The example shows that the BaseDate report parameter will be incremented by one month.
The “Year” Part
This string function determines the year that corresponds to the rolling month value. It utilizes the same “DateAdd” functionality as the “Month” string function. The only difference is that the year is extracted from the new date value using the “Year” function.
HEY! This expression will need to be applied to each column. Each implementation of this expression will need to be updated to correlate to the appropriate rolling month. Meaning: The units parameter of the DateAdd function will need to be updated to reflect the correct value. For Example, The third column header on the report, you would enter “3” for the units parameter of the DateAdd.
Done!
Thanks for listening.