Home Analytics

How to click-through to incident in chart detail items

Stephane_BouillonStephane_Bouillon Customer Advanced IT Monkey ✭✭✭
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 ?

Best Answer

Answers

  • Stephane_BouillonStephane_Bouillon Customer Advanced IT Monkey ✭✭✭
    Thank you so much Brian, that did it, you'd expect this to be at least case insensitive ...
  • Peter_SettlePeter_Settle Customer Advanced IT Monkey ✭✭✭

    @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_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    @Peter_Settle
    No Idea what you are asking for. The attached script returns two counts values to be used in SQL chart widgets. 
  • Peter_SettlePeter_Settle Customer Advanced IT Monkey ✭✭✭

    @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_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    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_doylejohn_doyle Cireson Support Ninja IT Monkey ✭✭✭✭
    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.


Sign In or Register to comment.