locked
Group by Prev and current year RRS feed

  • Question

  • I broke my head...it sounded so easy..but

    I have a table with Years listed, like Year below and I want to create a new group   "Combined Year" using expression or whatever which includes current and prev year, so it would always show 2 years , like 2015 and 2014, 2014 and 2013,2013 and 2012. Is this humanly possible in SSRS?

    Year       Product      Sales     

    2015        ABC         $ 22         

    2015        XYZ         $ 33

    2014        KLM        $ 44

    2014        NOP        $ 44

    2013        ABC       $11



    • Edited by BrBa Thursday, January 22, 2015 9:02 PM
    Thursday, January 22, 2015 9:00 PM

Answers

  • Yes, it should be but not using SSRS Previous function. That cannot be used in a grouping expression. You could use CTE (Common Table Expressions) to get what you want.

    I started with this basic TSQL query against a TFS reporting warehouse table:

    SELECT
      DATEPART(yyyy, DimWorkItem.System_CreatedDate) AS Year
      ,COUNT(DimWorkItem.System_Id) AS Count
      ,ROW_NUMBER() OVER (ORDER BY DATEPART(yyyy, DimWorkItem.System_CreatedDate) DESC) AS RN
    FROM
      DimWorkItem
    GROUP BY
      DATEPART(yyyy, DimWorkItem.System_CreatedDate)


    Notice the ROW_NUMBER function. It is important to getting the results you want. This Query returns:

      Year Count RN
      2015 68096 1
      2014 2695890 2
      2013 1564128 3
      2012 1496050 4
      2011 1668560 5
      2010 213176 6

    Now modifying the query to use the original as a CTE source:

    WITH CTE AS
    (SELECT
      DATEPART(yyyy, DimWorkItem.System_CreatedDate) AS Year
      ,COUNT(DimWorkItem.System_Id) AS Count
      ,ROW_NUMBER() OVER (ORDER BY DATEPART(yyyy, DimWorkItem.System_CreatedDate) DESC) AS RN
    FROM
      DimWorkItem
    GROUP BY
      DATEPART(yyyy, DimWorkItem.System_CreatedDate))
    
    SELECT CAST(CTE1.Year AS VARCHAR(4))+'/'+CAST(CTE2.Year AS VARCHAR(4)) AS YearRange, CTE1.Count+CTE2.Count AS Count
    FROM CTE CTE1
      INNER JOIN CTE CTE2
        ON CTE1.RN = CTE2.RN-1
    ORDER BY
      CTE1.Year DESC

    I get the results I am looking for directly in the dataset:

     YearRange Count
    2015/2014 2763986
    2014/2013 4260018
    2013/2012 3060178
    2012/2011 3164610
    2011/2010 1881736


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.


    Thursday, January 22, 2015 10:35 PM

All replies

  • Yes, it should be but not using SSRS Previous function. That cannot be used in a grouping expression. You could use CTE (Common Table Expressions) to get what you want.

    I started with this basic TSQL query against a TFS reporting warehouse table:

    SELECT
      DATEPART(yyyy, DimWorkItem.System_CreatedDate) AS Year
      ,COUNT(DimWorkItem.System_Id) AS Count
      ,ROW_NUMBER() OVER (ORDER BY DATEPART(yyyy, DimWorkItem.System_CreatedDate) DESC) AS RN
    FROM
      DimWorkItem
    GROUP BY
      DATEPART(yyyy, DimWorkItem.System_CreatedDate)


    Notice the ROW_NUMBER function. It is important to getting the results you want. This Query returns:

      Year Count RN
      2015 68096 1
      2014 2695890 2
      2013 1564128 3
      2012 1496050 4
      2011 1668560 5
      2010 213176 6

    Now modifying the query to use the original as a CTE source:

    WITH CTE AS
    (SELECT
      DATEPART(yyyy, DimWorkItem.System_CreatedDate) AS Year
      ,COUNT(DimWorkItem.System_Id) AS Count
      ,ROW_NUMBER() OVER (ORDER BY DATEPART(yyyy, DimWorkItem.System_CreatedDate) DESC) AS RN
    FROM
      DimWorkItem
    GROUP BY
      DATEPART(yyyy, DimWorkItem.System_CreatedDate))
    
    SELECT CAST(CTE1.Year AS VARCHAR(4))+'/'+CAST(CTE2.Year AS VARCHAR(4)) AS YearRange, CTE1.Count+CTE2.Count AS Count
    FROM CTE CTE1
      INNER JOIN CTE CTE2
        ON CTE1.RN = CTE2.RN-1
    ORDER BY
      CTE1.Year DESC

    I get the results I am looking for directly in the dataset:

     YearRange Count
    2015/2014 2763986
    2014/2013 4260018
    2013/2012 3060178
    2012/2011 3164610
    2011/2010 1881736


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.


    Thursday, January 22, 2015 10:35 PM
  • Thank you, this worked!
    Tuesday, January 27, 2015 6:34 PM