This report provides list of dates CurrentYear CurrentRelativeDay, CurrentYear-1 CurrentRelativeDay, CurrentYear-2 CurrentRelativeDay 0 RSDS_EnterpriseDW None c1a25229-f807-4ca9-80ce-3fc7a9985060 RSDS_EnterpriseDW =Mid( Parameters!prmTerm.Value ,Instr(Parameters!prmTerm.Value, "&") + 2 ,Len(Parameters!prmTerm.Value) - Instr(Parameters!prmTerm.Value, "&") - 2 ) --@prmTerm is passed into the report. Expected format example --is "20131" (without quotes) IF 2=3 BEGIN SELECT CAST(NULL AS date) AS [CY], CAST(NULL AS date) AS [CY_1], CAST(NULL AS date) AS [CY_2] END DECLARE @Term_CY VARCHAR(6) = @prmTerm DECLARE @Term_CY1 VARCHAR(6) DECLARE @Term_CY2 VARCHAR(6) DECLARE @RelativeDayNumber INT SET @Term_CY1 = cast(cast(LEFT(@Term_CY,4) AS INT) - 1 AS VARCHAR(4)) + right(@Term_CY, (len(@Term_CY) - 4)) SET @Term_CY2 = cast(cast(LEFT(@Term_CY,4) AS INT) - 2 AS VARCHAR(4)) + right(@Term_CY, (len(@Term_CY) - 4)) --Set the RelativeDayNumber based on DimDate.IsCurrentDay SELECT @RelativeDayNumber = RelativeDayNumber FROM dbo.DimRelativeDay drd WHERE drd.Term = @Term_CY AND RelativeDayDate = (SELECT FullDate FROM DimDate WHERE IsCurrentDay = 'Y') SELECT [CY] AS [CY], [CY - 1] AS [CY_1], [CY - 2] AS [CY_2] FROM ( SELECT 'CY' AS YearLabel, RelativeDayDate FROM dbo.DimRelativeDay drd WHERE drd.Term = @Term_CY AND RelativeDayNumber = @RelativeDayNumber UNION ALL SELECT 'CY - 1' AS YearLabel, RelativeDayDate FROM dbo.DimRelativeDay drd WHERE drd.Term = @Term_CY1 AND RelativeDayNumber = @RelativeDayNumber UNION ALL SELECT 'CY - 2' AS YearLabel, RelativeDayDate FROM dbo.DimRelativeDay drd WHERE drd.Term = @Term_CY2 AND RelativeDayNumber = @RelativeDayNumber ) AS DateSet PIVOT (max(RelativeDayDate) FOR YearLabel IN ([CY], [CY - 1], [CY - 2]) ) AS Pvt CY System.DateTime CY_1 System.DateTime CY_2 System.DateTime RSDS_EnterpriseDW SELECT BackgroundAlternateColor AS HeaderBackgroundColor, BackgroundColor, Band1Color, Band2Color, Band3Color, Band4Color, Band5Color, Band6Color FROM Configuration_Color WHERE (ReportType = 'Enrollment') HeaderBackgroundColor System.String BackgroundColor System.String Band1Color System.String Band2Color System.String Band3Color System.String Band4Color System.String Band5Color System.String Band6Color System.String 0.97916in 0.95833in 0.91666in 0.2in true true Relative Day Dates for Current Date Textbox7 =First(Fields!HeaderBackgroundColor.Value, "ConfigurationColor") 2pt 2pt 2pt 2pt 3 0.23958in true true Current Year Textbox1 =First(Fields!HeaderBackgroundColor.Value, "ConfigurationColor") 2pt 2pt 2pt 2pt true true Current Year - 1 Textbox3 =First(Fields!HeaderBackgroundColor.Value, "ConfigurationColor") 2pt 2pt 2pt 2pt true true Current Year - 2 Textbox5 =First(Fields!HeaderBackgroundColor.Value, "ConfigurationColor") 2pt 2pt 2pt 2pt 0.23958in true true =Fields!CY.Value CY 2pt 2pt 2pt 2pt true true =Fields!CY_1.Value CY_1 2pt 2pt 2pt 2pt true true =Fields!CY_2.Value CY_2 2pt 2pt 2pt 2pt After After CurrentDayRelativeDayDates 0.67916in 2.85415in 0.67916in