SSRS pivot dynamic columns

ssrs-2008-r2

I've tried to port this from a similar post, Return a column per date in a range
however I haven't been able to quite get it to do what I need… Please find some example code below :

What i'm after is summing the QTYOrdered between two dates looking back over a 6 week period. I'm not quite able to get this to work and need help! 😉 I'm sure @Bluefeet would be able to sort this out in a min!

    DROP TABLE #Sales
DROP TABLE #TempDates

CREATE TABLE #Sales
    (
      ID INT ,
      ItemID NVARCHAR(6) ,
      [Description] NVARCHAR(10) ,
      QTYOrdered NUMERIC ,
      ReceiptDate DATETIME
    )

INSERT  INTO #Sales
        ( ID ,
          ItemID ,
          [Description] ,
          QTYOrdered ,
          ReceiptDate
        )
VALUES  ( 1 , -- ID - int
          N'AAL100' , -- ItemID - nvarchar(6) ,
          'Ice Cream' , -- Description -- nvarchar(10) ,
          21 , -- QTYOrdered - numeric
          DATEADD(WEEK, -1, GETDATE())  -- ReceiptDate - datetime
        )

INSERT  INTO #Sales
        ( ID ,
          ItemID ,
          [Description] ,
          QTYOrdered ,
          ReceiptDate
        )
VALUES  ( 2 , -- ID - int
          N'AAL100' , -- ItemID - nvarchar(6)
          'Ice Cream' , -- Description -- nvarchar(10) ,
          40 , -- QTYOrdered - numeric
          DATEADD(WEEK, -1, GETDATE())  -- ReceiptDate - datetime
        )

INSERT  INTO #Sales
        ( ID ,
          ItemID ,
          [Description] ,
          QTYOrdered ,
          ReceiptDate
        )
VALUES  ( 3 , -- ID - int
          N'AAL100' , -- ItemID - nvarchar(6)
          'Ice Cream' , -- Description -- nvarchar(10) ,
          50 , -- QTYOrdered - numeric
          DATEADD(WEEK, -3, GETDATE())  -- ReceiptDate - datetime
        )

INSERT  INTO #Sales
        ( ID ,
          ItemID ,
          [Description] ,
          QTYOrdered ,
          ReceiptDate
        )
VALUES  ( 4 , -- ID - int
          N'AAL101' , -- ItemID - nvarchar(6)
          'Burger' , -- Description -- nvarchar(10) ,
          43 , -- QTYOrdered - numeric
          DATEADD(WEEK, -3, GETDATE())  -- ReceiptDate - datetime
        )

INSERT  INTO #Sales
        ( ID ,
          ItemID ,
          [Description] ,
          QTYOrdered ,
          ReceiptDate
        )
VALUES  ( 5 , -- ID - int
          N'AAL101' , -- ItemID - nvarchar(6)
          'Burger' , -- Description -- nvarchar(10) ,
          42 , -- QTYOrdered - numeric
          DATEADD(WEEK, -5, GETDATE())  -- ReceiptDate - datetime
        )

INSERT  INTO #Sales
        ( ID ,
          ItemID ,
          [Description] ,
          QTYOrdered ,
          ReceiptDate
        )
VALUES  ( 6 , -- ID - int
          N'AAL101' , -- ItemID - nvarchar(6)
          'Burger' , -- Description -- nvarchar(10) ,
          76 , -- QTYOrdered - numeric
          DATEADD(WEEK, -5, GETDATE())  -- ReceiptDate - datetime
        )

INSERT  INTO #Sales
        ( ID ,
          ItemID ,
          [Description] ,
          QTYOrdered ,
          ReceiptDate
        )
VALUES  ( 7 , -- ID - int
          N'AAL102' , -- ItemID - nvarchar(6)
          'Pizza' , -- Description -- nvarchar(10) ,
          65 , -- QTYOrdered - numeric
          DATEADD(WEEK, -7, GETDATE())  -- ReceiptDate - datetime
        )

INSERT  INTO #Sales
        ( ID ,
          ItemID ,
          [Description] ,
          QTYOrdered ,
          ReceiptDate
        )  
VALUES  ( 8 , -- ID - int
          N'AAL102' , -- ItemID - nvarchar(6)
          'Pizza' , -- Description -- nvarchar(10) ,
          34 , -- QTYOrdered - numeric
          DATEADD(WEEK, -7, GETDATE())  -- ReceiptDate - datetime
        )

INSERT  INTO #Sales
        ( ID ,
          ItemID ,
          [Description] ,  
          QTYOrdered ,
          ReceiptDate
        )
VALUES  ( 9 , -- ID - int
          N'AAL102' , -- ItemID - nvarchar(6)
          'Pizza' , -- Description -- nvarchar(10) ,
          56 , -- QTYOrdered - numeric
          DATEADD(WEEK, -9, GETDATE())  -- ReceiptDate - datetime
        )

INSERT  INTO #Sales
        ( ID ,
          ItemID ,
          [Description] ,
          QTYOrdered ,
          ReceiptDate
        )  
VALUES  ( 10 , -- ID - int
          N'AAL103' , -- ItemID - nvarchar(6)
          'Burrito' , -- Description -- nvarchar(10) ,
          75 , -- QTYOrdered - numeric
          DATEADD(WEEK, -9, GETDATE())  -- ReceiptDate - datetime
        )
SELECT  *
FROM    #Sales

DECLARE @Cols AS NVARCHAR(MAX) ,
    @Query AS NVARCHAR(MAX);
WITH    CTE ( DateList, MaxDate )
          AS ( SELECT   DATEADD(WEEK, -6, CONVERT(DATE, GETDATE())) AS DateList ,
                        MAX(ReceiptDate) MaxDate
               FROM     #Sales
               UNION ALL
               SELECT   DATEADD(WEEK, 1, DateList) ,
                        MaxDate
               FROM     CTE
               WHERE    CTE.DateList < CTE.MaxDate
             )
    SELECT  C.DateList
    INTO    #TempDates
    FROM    CTE C 

SELECT  @Cols = STUFF((SELECT DISTINCT 
                                ',' + QUOTENAME(CONVERT(CHAR(10), DateList, 120))
                       FROM     #TempDates
        FOR           XML PATH('') ,
                          TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SET @Query = 'SELECT 
        ITEMID ,
        [Description] ,
        ' + @Cols
    + ' FROM 
             ( SELECT   
                        ITEMID ,
                        [Description] ,
                        QTYOrdered ,
                        CONVERT(CHAR(10), DateList, 120) PivotDate
                FROM    #TempDates D
                        INNER JOIN #Sales S ON ReceiptDate BETWEEN DateList and DATEADD(DAY, 6, DateList)
        ) X 
             PIVOT 
            (
                SUM(QTYOrdered)
                FOR PivotDate in (' + @Cols + ') 
            ) P ' 
PRINT @Query 
EXECUTE(@query)

Example desired output : 

ID  ITEMID  Description 2014-09-15  2014-09-22  2014-09-29  2014-10-06  2014-10-13  2014-10-20

1   AAL100  Ice Cream   0   0   0   0   50  0
2   AAL101  Burger      0   0   0   118 43  0
3   AAL102  Pizza       0   56  34  0   99  0
4   AAL103  Burrito     0   75  0   0   0   0

Excuse formatting… I don't know how to format text in the little editor window…

@bluefeet your fiddle seems close however when I run against my actual data i'm getting multiple lines and some of the summed qty's don't seem to be correct…

enter image description here

Best Answer

As suggested in my comment I would not PIVOT the data within the SQL and let SSRS do the PIVOTING by using a matrix. Why? Because your report will not be flexible should you choose to allow users to specify their own number of periods.

This kind of report is much easier with a Date dimension table so you can easily figure out the week start/end date between a set of dates, this is what happens in the first part of the solution but if you had a Date table permanently stored it would be as case of simply querying that table.

The CTEs named N1 through to N5 are creating a tally table, a table full of numbers, which I use to generate the CTE DateAndWeek which contains a list of dates and the week start date, based on the @@DATEFIRST value.

WITH N1 AS
(
    SELECT 1 AS N
    UNION ALL
    SELECT 1 AS N
)
, N2 AS
(
    SELECT A.N FROM N1 AS A
    CROSS JOIN N1 AS B
)
, N3 AS
(
    SELECT A.N FROM N2 AS A
    CROSS JOIN N2 AS B
)
, N4 AS
(
    SELECT A.N FROM N3 AS A
    CROSS JOIN N3 AS B
)
, N5 AS
(
    SELECT A.N FROM N4 AS A
    CROSS JOIN N4 AS B
)
, DateAndWeek AS
(
    SELECT TOP 365
        DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), '20140101') AS [Date]
        , DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
            + (1-DATEPART(WEEKDAY, DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), '20140101'))), '20140101') AS WeekStartDate
    FROM
        N5
)

Depending on your requirements you may or may not need to create dummy/blank rows for items which have no sales during the period and this is what happens in the ReportTemplate CTE. It returns a dataset with all the weeks within the period specified and performs a 'CROSS JOIN' against all unique ItemID and Description values.

, ReportTemplate AS
(
    SELECT DISTINCT
        WeekStartDate
        , ItemId
        , Description

    FROM
        DateAndWeek
        CROSS JOIN
        (
            SELECT DISTINCT
                ItemID
                , Description

            FROM
                #Sales
        ) AS Sales

    WHERE
        [Date] BETWEEN @StartDate AND @EndDate
)

The final CTE perform the AGGREGATION of your sale data by JOINING to the DateAndWeek CTE to figure out which week the sale belongs in.

, SalesInPeriod AS
(
    SELECT
        ItemID
        , WeekStartDate
        , SUM(QTYOrdered) AS Total

    FROM
        #Sales
        JOIN DateAndWeek
            ON  CAST(#Sales.ReceiptDate AS DATE) = DateAndWeek.Date

    WHERE
        DateAndWeek.Date BETWEEN @StartDate AND @EndDate

    GROUP BY
        ItemID
        , WeekStartDate
)

Finally, LEFT JOIN the ReportTemplate and SalesInPeriod CTEs to get the result set for your report.

Here is the full solution

IF OBJECT_ID('tempdb..#Sales', 'U') IS NOT NULL
    DROP TABLE #Sales
GO

DECLARE
    @StartDate      DATE = DATEADD(WEEK, -6, CAST(GETDATE() AS DATE))
    , @EndDate      DATE = CAST(GETDATE() AS DATE)


CREATE TABLE #Sales
(
    ID              INT
    , ItemID        NVARCHAR(6)
    , [Description] NVARCHAR(10)
    , QTYOrdered    NUMERIC
    , ReceiptDate   DATETIME
)

INSERT INTO #Sales
VALUES 
(1, N'AAL100', 'Ice Cream', 21 , DATEADD(WEEK, -1, GETDATE()))
, (2, N'AAL100', 'Ice Cream', 40 , DATEADD(WEEK, -1, GETDATE()))
, (3, N'AAL100', 'Ice Cream', 50 , DATEADD(WEEK, -3, GETDATE()))
, (4, N'AAL101', 'Burger', 43 , DATEADD(WEEK, -3, GETDATE()))
, (5, N'AAL101', 'Burger', 42 , DATEADD(WEEK, -5, GETDATE()))
, (6, N'AAL101', 'Burger', 76 , DATEADD(WEEK, -5, GETDATE()))
, (7, N'AAL102', 'Pizza', 76 , DATEADD(WEEK, -7, GETDATE()))
, (8, N'AAL102', 'Pizza', 34 , DATEADD(WEEK, -7, GETDATE()))
, (9, N'AAL103', 'Burrito', 56 , DATEADD(WEEK, -9, GETDATE()))
, (10, N'AAL103', 'Burrito', 75 , DATEADD(WEEK, -9, GETDATE()))
;

WITH N1 AS
(
    SELECT 1 AS N
    UNION ALL
    SELECT 1 AS N
)
, N2 AS
(
    SELECT A.N FROM N1 AS A
    CROSS JOIN N1 AS B
)
, N3 AS
(
    SELECT A.N FROM N2 AS A
    CROSS JOIN N2 AS B
)
, N4 AS
(
    SELECT A.N FROM N3 AS A
    CROSS JOIN N3 AS B
)
, N5 AS
(
    SELECT A.N FROM N4 AS A
    CROSS JOIN N4 AS B
)
, DateAndWeek AS
(
    SELECT TOP 365
        DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), '20140101') AS [Date]
        , DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
            + (1-DATEPART(WEEKDAY, DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), '20140101'))), '20140101') AS WeekStartDate
    FROM
        N5
)
, ReportTemplate AS
(
    SELECT DISTINCT
        WeekStartDate
        , ItemId
        , Description

    FROM
        DateAndWeek
        CROSS JOIN
        (
            SELECT DISTINCT
                ItemID
                , Description

            FROM
                #Sales
        ) AS Sales

    WHERE
        [Date] BETWEEN @StartDate AND @EndDate
)
, SalesInPeriod AS
(
    SELECT
        ItemID
        , WeekStartDate
        , SUM(QTYOrdered) AS Total

    FROM
        #Sales
        JOIN DateAndWeek
            ON  CAST(#Sales.ReceiptDate AS DATE) = DateAndWeek.Date

    WHERE
        DateAndWeek.Date BETWEEN @StartDate AND @EndDate

    GROUP BY
        ItemID
        , WeekStartDate
)

SELECT
    ReportTemplate.WeekStartDate
    , ReportTemplate.ItemID
    , ReportTemplate.Description
    , SalesInPeriod.Total

FROM
    ReportTemplate
    LEFT JOIN SalesInPeriod
        ON  ReportTemplate.ItemID = SalesInPeriod.ItemID
            AND ReportTemplate.WeekStartDate = SalesInPeriod.WeekStartDate
Related Question