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

Answer 1

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

Answer 2

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

Answer 3

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).

Answer 4

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

Answer 5

I'm seeing a 'The stuff function requires 4 arguments(s) - and not having much luck in correcting.

Answer 6

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

Answer 7

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?

Answer 8

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

Answer 9

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?

Answer 10

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

Answer 11

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

Answer 12

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.

Answer 13

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

Answer 14

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?

Answer 15

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

Answer 16

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

Answer 17

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? 

Answer 18

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

Answer 19

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?

Answer 20

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