How to click-through to incident in chart detail items

Stephane_Bouillon

Hi,

I have a simple SQL Table Chart widget where I have checked the "Display Selected Item Details" option. When I click on a column in the chart, I see the workitem details of the underlying SQL Query. However, when I click on a row in the Item Details part, it simply opens the search page. What do I need to do to click through straight to the incident itself ?

Brian_Wiest

This one burned me for some time as well.
The Cireson scripts look for specific wording on columns for its internal functions.
To get the click into trigger to work you have to made sure the Work Item Id is labeled like
SELECT wi.WorkItemId as [Id]
Case specific. Once in that format it will properly trigger to the matching work item.
Same is true for your date columns if you want localisations to work. 

Brian_Wiest

This one burned me for some time as well.
The Cireson scripts look for specific wording on columns for its internal functions.
To get the click into trigger to work you have to made sure the Work Item Id is labeled like
SELECT wi.WorkItemId as [Id]
Case specific. Once in that format it will properly trigger to the matching work item.
Same is true for your date columns if you want localisations to work. 

Stephane_Bouillon

Thank you so much Brian, that did it, you'd expect this to be at least case insensitive ...

Peter_Settle

@brian_wiest Hi Brian, could you look at the script below to help me with the displaying of the data please.

sorry for hijacking your question @stephane_bouillon

Brian_Wiest

@Peter_Settle
No Idea what you are asking for. The attached script returns two counts values to be used in SQL chart widgets. 

Peter_Settle

@Brian_Wiest sorry its a dashboard for change control to show the changes started ontime, all I can get is the figures, but would like to be able to drill through to the details.

Don't worry.

Brian_Wiest

Ok, I see, you can not mix SQL types one being a count as you have and then a second for the row details. 
What you will want to do is build a page with a few SQL widgets to have the various content displayed. 
Here is my SQL query for Change Management where we are looking at the stage value of the related activity to know where it is in the process. The key to this is your change process can only have one activity running at a time for the SQL to render correctly. 
HTH

john_doyle

Hi @Peter_Settle

If you want that query to display as a chart with a detail display, you will have to change the query so that it returns the Category and the Work Item Id. Something like this:

declare @IsScoped bit = 0<br>declare @UserId uniqueidentifier = 0x0;<br>IF(@IsScoped = 0)<br>&nbsp;&nbsp; BEGIN <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT (CASE&nbsp; <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHEN DATEDIFF(HOUR, (case when sq.ScheduledStartDate is null then wi.ScheduledStartDate else sq.ScheduledStartDate end)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , (case when sq.ActualStartDate is null then wi.ActualStartDate else sq.ActualStartDate end)) <= -1 THEN 'Early'&nbsp; <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHEN DATEDIFF(HOUR, (case when sq.ScheduledStartDate is null then wi.ScheduledStartDate else sq.ScheduledStartDate end)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , (case when sq.ActualStartDate is null then wi.ActualStartDate else sq.ActualStartDate end)) BETWEEN 0 AND 1 THEN 'On-Time'&nbsp; <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ELSE 'Late' END) Category<br>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;,wi.WorkItemId [Id]<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [WorkItem] wi<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INNER JOIN (<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cr.WorkItemId<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , cr.ParentWorkItemId<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , cr.ScheduledStartDate<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , cr.ActualStartDate <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [WorkItem] cr<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cr.WorkItemId like 'MA%'<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and cr.title = 'Implement the Change.'<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) sq on sq.ParentWorkItemId = wi.WorkItemId&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br>&nbsp;&nbsp;&nbsp; &nbsp; WHERE <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; wi.ClassId IN ('E6C9CF6E-D7FE-1B5D-216C-C3F5D2C7670C')<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND wi.ScheduledStartDate IS NOT NULL <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND wi.ActualStartDate IS NOT NULL<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND DATEDIFF(DAY, wi.ScheduledStartDate, GETUTCDATE()) BETWEEN 0 AND 30<br>&nbsp;&nbsp; END<br>ELSE<br>&nbsp;&nbsp; BEGIN <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT (CASE&nbsp; <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHEN DATEDIFF(HOUR, (case when sq.ScheduledStartDate is null then wi.ScheduledStartDate else sq.ScheduledStartDate end)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , (case when sq.ActualStartDate is null then wi.ActualStartDate else sq.ActualStartDate end)) <= -1 THEN 'Early'&nbsp; <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHEN DATEDIFF(HOUR, (case when sq.ScheduledStartDate is null then wi.ScheduledStartDate else sq.ScheduledStartDate end)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , (case when sq.ActualStartDate is null then wi.ActualStartDate else sq.ActualStartDate end)) BETWEEN 0 AND 1 THEN 'On-Time'&nbsp; <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ELSE 'Late' END) Category<br>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;,wi.WorkItemId [Id]<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [WorkItem] wi<br>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;INNER JOIN [Access_CI$User_WorkItem] a ON wi.Id = a.WorkItemId AND a.UserId = @UserId<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INNER JOIN (<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cr.WorkItemId<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , cr.ParentWorkItemId<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , cr.ScheduledStartDate<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , cr.ActualStartDate <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [WorkItem] cr<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cr.WorkItemId like 'MA%'<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and cr.title = 'Implement the Change.'<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) sq on sq.ParentWorkItemId = wi.WorkItemId&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br>&nbsp;&nbsp;&nbsp; &nbsp; WHERE <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; wi.ClassId IN ('E6C9CF6E-D7FE-1B5D-216C-C3F5D2C7670C')<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND wi.ScheduledStartDate IS NOT NULL <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND wi.ActualStartDate IS NOT NULL<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND DATEDIFF(DAY, wi.ScheduledStartDate, GETUTCDATE()) BETWEEN 0 AND 30<br>&nbsp;&nbsp; END

The portal should ask you which value you wish to group by. You can specify Category for this and the chart will show the number of work items in each category. You can click on each section to view the work items in that category.