none
Month by Project Grid with totals

    Question

  • Hi,

    I am trying to produce the following output from an SQL statement:

                  Project A    Project B    Project C    Total
    JAN         12             1               3               16
    FEB          4              3               5               12
    MAR         4              4               4               12

    I have started with the following statement - is there an easy to achieve this using SQL functions or simple TSQL syntax? The query will be executed by an SSRS report which doesn't support all SQL functionality.

    SELECT 
    COUNT([ItemId]) AS Risks, 
    Project, 
    Created
    FROM Risks  
    GROUP BY [Project] , created
    Any guidance appreciated. Thanks
    Monday, May 14, 2012 3:07 PM

Answers

  • Check 

    use AllTests
    create table Risks ( Project varchar(100),ItemID int,Created datetime)
    Insert into Risks 
    values ('A',12,getdate()),
     ('A',10,getdate()-14),
      ('b',12,getdate()-30),
       ('b',11,getdate()-70),
        ('c',12,getdate()-50),
         ('c',10,getdate()-45),
         ('c',12, DATEADD(year,-2, current_timestamp))
    
    declare @SQL nvarchar(max), @Cols nvarchar(max)
    
    select @Cols =  stuff( 
    ((select ', ' + quotename(Project) from 
    (select distinct Project from Risks) X
    ORDER BY Project for XML PATH(''),type).value('.','nvarchar(max)'))
    ,1,2,'')
    
    
    set @SQL = 
    ';with cte as (select Project, ItemID, datepart(year, Created) as [Year], 
    LEFT(datename(month, Created),3) as [MonthName]
    
     from Risks)
    
    select *, ' + replace(@Cols, '], [','] + [') + ' as Total
    
    from cte PIVOT (count(ItemID) for Project IN (' + @cols +')) pvt'
    
    print @SQL -- testing
    
    execute(@SQL)

    Also, you may also want to keep yearmonth column if you want to sort properly, so you may want to add convert(varchar(6), Created,112) as [Year Month] into the query and sort by [Year Month] (add order by after pvt 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Proposed as answer by Iric WenModerator Wednesday, May 16, 2012 3:19 AM
    • Marked as answer by _c Friday, May 18, 2012 9:50 AM
    Tuesday, May 15, 2012 12:22 PM
    Moderator

All replies

  • I am doing the same thing here, 

    select 
    DATEADD(Month, datediff(month,'19000101', b.[Completion Date]),'19000101') As myMonth ----Month bucket
    ,Count(*) As CountRecords ----Record count
     from ........

    .......

    GROUP BY DATEDIFF(MONTH, '19000101',b.[Completion Date]) order by myMonth

    take a look at this also

    http://www.craigsmullins.com/ssu_0899.htm


    • Edited by SBolton Monday, May 14, 2012 3:27 PM
    Monday, May 14, 2012 3:25 PM
  • This is very simple in T-SQL, but the SSRS has already matrix functionality built-in, so you may not need T-SQL solution.

    However, for T-SQL if you know your projects in advance, it's a simple PIVOT query, e.g. (assuming your data all in one year):

    ;with cte as (select Project, ItemID, LEFT(datename(month, Created),3) as [MonthName] from Risks)
    
    select *, [Project A] + [Project B] + [Project C] as Total
    
    from cte PIVOT (count(ItemID) for Project IN ([Project A],[Project B],[Project C])) pvt




    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, May 14, 2012 3:32 PM
    Moderator
  • Thanks Naomi - the pivot axis look good, although I'm getting zero values for each project... I'll recheck my data.

    Sorry edit.... my fault. My Project column contains many projects so I cannot keep them static - is there a way to change the last line so that it can feed from the Project column dynamically? (I was just giving an example).

    • Edited by _c Monday, May 14, 2012 4:30 PM edit
    Monday, May 14, 2012 4:26 PM
  • Yes, it's possible, however, the result will also be dynamic.

    declare @SQL nvarchar(max), @Cols nvarchar(max)
    
    select @Cols = stuff((select ', ' + quotename(Project)
    
    from (select distinct Project from Risks) X
    ORDER BY Project for XML PATH(''),type).value('.','nvarchar(max)')),1,2,'')
    
    
    set @SQL = 
    ';with cte as (select Project, ItemID, LEFT(datename(month, Created),3) as [MonthName] from Risks)
    
    select *, ' + replace(@Cols, '], [','] + [') + ' as Total
    
    from cte PIVOT (count(ItemID) for Project IN (' + @cols +')) pvt'
    
    print @SQL -- testing
    
    execute(@SQL)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    Monday, May 14, 2012 4:47 PM
    Moderator
  • I'm seeing a 'The stuff function requires 4 arguments(s) - and not having much luck in correcting.



    • Edited by _c Tuesday, May 15, 2012 8:11 AM spelling
    Tuesday, May 15, 2012 7:45 AM
  • there is a comma missing. I have prepared a complete example here

    create table Risks ( Project varchar(100),ItemID int,Created datetime)
    Insert into Risks 
    values ('A',12,getdate()),
     ('A',10,getdate()-14),
      ('b',12,getdate()-30),
       ('b',11,getdate()-70),
        ('c',12,getdate()-50),
         ('c',10,getdate()-45)
    
    declare @SQL nvarchar(max), @Cols nvarchar(max)
    
    select @Cols =  stuff( 
    ((select ', ' + quotename(Project) from 
    (select distinct Project from Risks) X
    ORDER BY Project for XML PATH(''),type).value('.','nvarchar(max)'))
    ,1,2,'')
    
    
    set @SQL = 
    ';with cte as (select Project, ItemID, LEFT(datename(month, Created),3) as [MonthName] from Risks)
    
    select *, ' + replace(@Cols, '], [','] + [') + ' as Total
    
    from cte PIVOT (count(ItemID) for Project IN (' + @cols +')) pvt'
    
    print @SQL -- testing
    
    execute(@SQL)




    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com


    • Edited by Shamas Saeed Tuesday, May 15, 2012 8:48 AM Code block
    Tuesday, May 15, 2012 8:47 AM
  • Thanks Shamas - where would I put the ORDER BY clause so that I get the results in Month order?

    And is it possible to also split by year as I assume the current statement would return all risks per month even if they were raised in different years?

    Tuesday, May 15, 2012 9:19 AM
  • Check 

    use AllTests
    create table Risks ( Project varchar(100),ItemID int,Created datetime)
    Insert into Risks 
    values ('A',12,getdate()),
     ('A',10,getdate()-14),
      ('b',12,getdate()-30),
       ('b',11,getdate()-70),
        ('c',12,getdate()-50),
         ('c',10,getdate()-45),
         ('c',12, DATEADD(year,-2, current_timestamp))
    
    declare @SQL nvarchar(max), @Cols nvarchar(max)
    
    select @Cols =  stuff( 
    ((select ', ' + quotename(Project) from 
    (select distinct Project from Risks) X
    ORDER BY Project for XML PATH(''),type).value('.','nvarchar(max)'))
    ,1,2,'')
    
    
    set @SQL = 
    ';with cte as (select Project, ItemID, datepart(year, Created) as [Year], 
    LEFT(datename(month, Created),3) as [MonthName]
    
     from Risks)
    
    select *, ' + replace(@Cols, '], [','] + [') + ' as Total
    
    from cte PIVOT (count(ItemID) for Project IN (' + @cols +')) pvt'
    
    print @SQL -- testing
    
    execute(@SQL)

    Also, you may also want to keep yearmonth column if you want to sort properly, so you may want to add convert(varchar(6), Created,112) as [Year Month] into the query and sort by [Year Month] (add order by after pvt 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Proposed as answer by Iric WenModerator Wednesday, May 16, 2012 3:19 AM
    • Marked as answer by _c Friday, May 18, 2012 9:50 AM
    Tuesday, May 15, 2012 12:22 PM
    Moderator
  • Thanks Naomi, that makes sense.

    Can I ask you one more question around this (I can post in the SSRS forum) - your SQL works for me however when creating a dataset in SSRS it dyncamically generates all of the project names as columns i.e. if I had 15 projects I would see then as selectable column names. Of course I'd like the returned value to be Project so that I can group by it within an SSRS matrix - do you know how I change the above to achieve this?


    Tuesday, May 15, 2012 12:36 PM
  • There are two things to remember

    - If you are using SSRS then use Matrix report and use simple query it will convert Project value to columns.

    - If you need to use Pivot then return result set in a tablix on SSRS and add Group by on Date, Month column so that you can get sum of project values.


    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

    Tuesday, May 15, 2012 12:59 PM
  • In SSRS you don't need dynamic PIVOT at all. You need to use matrix and it will allow you to group by Month and put Project into the columns.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, May 15, 2012 1:11 PM
    Moderator
  • In SSRS you don't need dynamic PIVOT at all. You need to use matrix and it will allow you to group by Month and put Project into the columns.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    I am unable to put Project into any column as it doesn't exist as a column - my column choices for project are literally all of the individual project names.

    Tuesday, May 15, 2012 2:03 PM
  • Looking at the link where you can put your product column

    http://arcanecode.com/2010/07/07/creating-a-matrix-report-in-sql-server-2008-reporting-services/


    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

    Tuesday, May 15, 2012 2:10 PM
  • Is there a none PIVOT way to achieve the same output via a tablix as I cannot get the PIVOT to return a Project name column?
    Tuesday, May 15, 2012 3:11 PM
  • Take a look at this tutorial http://arcanecode.com/2010/07/07/creating-a-matrix-report-in-sql-server-2008-reporting-services/

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, May 15, 2012 3:23 PM
    Moderator
  • Yes. If you want to filter your query by Project ID then you need to add filter on this line

    select @Cols =  stuff( 
    ((select ', ' + quotename(Project) from 
    (select distinct Project from Risks where Project = @Project) X
    ORDER BY Project for XML PATH(''),type).value('.','nvarchar(max)'))
    ,1,2,'')


    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

    Tuesday, May 15, 2012 3:36 PM
  • Hi - its not a filter that I'm after - I just want to be able to select the Project column rather than the Project list being dynamically generated at design time.

    Maybe I can just use a select / group by and add into a tablix - can the pivot be converted into a regular SQL statement which groups and counts? 

    Tuesday, May 15, 2012 5:44 PM
  • Ok. this link will help you to change your pivot query to unpivot sql

    http://shamas-saeed.blogspot.com/2012/01/pivot-and-unpivot-with-example-using.html

    create table Risks ( Project varchar(100),ItemID int,Created datetime)
    Insert into Risks 
    values ('A',12,getdate()),
     ('A',10,getdate()-14),
      ('b',12,getdate()-30),
       ('b',11,getdate()-70),
        ('c',12,getdate()-50),
         ('c',10,getdate()-45),
         ('c',12, DATEADD(year,-2, current_timestamp))
    
    declare @SQL nvarchar(max), @Cols nvarchar(max)
    
    select @Cols =  stuff( 
    ((select ', ' + quotename(Project) from 
    (select distinct Project from Risks) X
    ORDER BY Project for XML PATH(''),type).value('.','nvarchar(max)'))
    ,1,2,'')
    
    
    set @SQL = 
    ';with cte as (select Project, ItemID, datepart(year, Created) as [Year], 
    LEFT(datename(month, Created),3) as [MonthName]
    
     from Risks)
    
    select *, ' + replace(@Cols, '], [','] + [') + ' as Total
    
    from cte PIVOT (count(ItemID) for Project IN (' + @cols +')) pvt'
    
    print @SQL -- testing
    
    execute(@SQL)
    
    select Project, ItemID, datepart(year, Created) as [Year], 
    LEFT(datename(month, Created),3) as [MonthName]
     from Risks

    This is your pivot and unpivot query. is this helpful?


    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

    Wednesday, May 16, 2012 6:11 AM
  • Guess I'll just GROUP and display in a tabllix, this is what I have:

     SELECT 
    COUNT([ItemId]) AS Risks, 
    Project, 
    datepart(year, Created) as [Year], 
    LEFT(datename(month, Created),3) as [MonthName]
    FROM wraid_Risks  
    GROUP BY [Project] , created
    But this doesn't group all of my projects - I'm seeing individual lines?

    Wednesday, May 16, 2012 11:44 AM
  • I suggest to start a new thread in SSRS forum on this topic. 

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, May 16, 2012 1:02 PM
    Moderator