In the first blog we just queried incidents and counted them by group. Something pretty simple.
Now I am going to query projects, select the assignment group first, then display it all in an SSRS report. But I am going to include the the latest work notes. I am going to use SQL reporting to design the the report the way I want it. Creating a dashboard of sorts listing the projects and their latest status and the date they were last updated.
There were some problems I ran in to when I attempted to do this. Open query is not really easy when it comes figuring out why a query failed, so it took some time to get the results I wanted. If you put an open query in SSRS it does not give you the field names. When you run a regular select statement in SSRS it will auto create all the field names. Drag and drop on the report, nice and easy. So this was one issue.
The issue I ran in to before that was getting the work notes. The work notes all exist in a table called sys_journal_field. I did not keep track of all the different methods I used to try to get the latest record, but none of them were working. Again, open query and not knowing exactly what the error is. In the end I was able to bring back only the latest record based on number and last updated on. I did this by adding row number in the initial query. Then when I queried the temp table I selected the max(rownumber).
Lets jump to the code.
First thing we need to do is create a temp table.
declare @temptable1 table ( [Project Name] varchar(500), [Project Lead] varchar(100), [Description] varchar(500), [LatestUpdate] varchar(1000), [Percent Complete] varchar(50) ,[start date] datetime, [end date] datetime ,[priority] varchar(8) ,[sys_id] varchar (50) ,[Assignment Group] varchar(40) , [Secondary Group] varchar(40) , [Sys_updated_on] datetime , [value] varchar(500) ,[Number] varchar(500) ,[RowNumber] varchar(10) ); DECLARE @TSQL1 varchar(max)
Include all the fields that you will want to use in your report.
Next we will query the data. In the pervious blog post I said you need to use double ticks ‘’, an example would be dv_assignment_group as ”Assisgnment Group”. With temp tables we need to use 4 ticks.
DECLARE @TSQL1 varchar(max) SELECT @TSQL1 = 'SELECT * FROM OPENQUERY (TESTSERVICE ,''select pm_project.name as ''''Project Name'''', pm_project.dv_project_lead as ''''Project Lead'''' ,task.short_description as ''''Description'''', pm_project.u_latest_update as ''''LatestUpdate'''', CASE WHEN pm_project.percent_complete = null THEN ''''0'''' ELSE floor(pm_project.percent_complete) END as ''''Percent Complete'''' ,pm_project.planned_Start_date as ''''Start Date'''' ,pm_project.planned_End_date as ''''End Date'''' ,case when task.priority = ''''1'''' then ''''Critical'''' when task.priority = ''''2'''' then ''''High'''' when task.priority = ''''3'''' then ''''Standard'''' end as ''''priority'''' ,task.sys_id ,task.dv_assignment_group as ''''Assignment Group'''' ,pm_project.u_secondary_group as ''''Secondary Group'''' ,sys_journal_field.Sys_updated_on ,sys_journal_field.value ,task.Number ,ROW_NUMBER() OVER(PARTITION BY task.number ORDER BY sys_journal_field.Sys_updated_on) AS RowNumber from pm_project join task on task.number = pm_project.number join sys_journal_field on task.sys_id = sys_journal_field .element_id where task.dv_state = ''''Open'''' order by sys_journal_field.Sys_updated_on asc '')' --GO insert @temptable1 EXEC (@TSQL1)
Now we need to query the temptable. When we query the temp table, SQL SSRS will recognize the fields.
-- select * from @temptable1 select ab.[Project Name],ab.[Project Lead], convert(varchar(20), Sys_updated_on,100) as NotesDate,value as Notes, ab.Number ,ab.[Percent Complete], ab.[start date] as start_date, ab.[end date] as End_Date,ab.[priority], ab.sys_id,ab.[Assignment Group], ab.[Secondary Group],ab.Description from @temptable1 as ab join (select number, max(rownumber) as number2 from @temptable1 group by number) as drv on ab.number = drv.number and rownumber = drv.number2 where ab.[Assignment Group] in (@Group) group by ab.[Project Name],ab.[Project Lead], Sys_updated_on,value, ab.number,ab.[Percent Complete], ab.[start date],ab.[end date],ab.[priority], ab.sys_id,ab.[Assignment Group], ab.[Secondary Group],ab.Description
The last thing I did for the SSRS report is add a drop down to choose the assignment group. This way I can get all the groups or only specific ones. As a manager I might want to only see what’s going on with my team.
declare @temptable2 table ( [Assignment Group] varchar(40) ); DECLARE @TSQL2 varchar(max) SELECT @TSQL2 = 'SELECT * FROM OPENQUERY (TESTSERVICE ,'' select task.dv_assignment_group as ''''Assignment Group'''' from pm_project join task '')' --GO insert @temptable2 EXEC (@TSQL2) -- select * from @temptable2 select distinct ab.[Assignment Group] from @temptable2 as ab
Please let me know if there is a better way to do this. I know its not super clean. It was fun learning how to get the data (sometimes).