Report Data Set - CurrentDayRelativeDayDates

Property	Value
DataSetName	CurrentDayRelativeDayDates
Data Source Reference
Collation
Case Sensitivity
Accent Sensitivity
Is MDX	False
Query Command Type
Query Data Source Name	RSDS_EnterpriseDW
Query Timeout
Use Generic Designer
Kanatype Sensitivity
Width Sensitivity
Integrated Security

--@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

Field Name	Field Type	Field Value
BackgroundColor	System.String
Band1Color	System.String
Band2Color	System.String
Band3Color	System.String
Band4Color	System.String
Band5Color	System.String
Band6Color	System.String
CY	System.DateTime
CY_1	System.DateTime
CY_2	System.DateTime
HeaderBackgroundColor	System.String

Parameter Name	Parameter Value
@prmTerm	=Mid( Parameters!prmTerm.Value ,Instr(Parameters!prmTerm.Value, "&") + 2 ,Len(Parameters!prmTerm.Value) - Instr(Parameters!prmTerm.Value, "&") - 2 )