IT Monkey:   Join the Cireson Community today for your chance to win $50!

How to click-through to incident in chart detail items

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