Home Analytics

Links in SQL Table Chart

alex_kokinalex_kokin Member IT Monkey ✭

Has anyone been able to get results in a SQL Table chart be clickable? I have this qurey but it does not like the link.


Select

'<a href=''https://selfservice/Incident/Edit/' + wi.Source_ID + '/''>' + wi.Source_ID + '</a>' AS 'Ticket_ID'

,wi.Source_ID

,wi.Title

--,w.created

--,wi.firstassigneddate

--,wi.resolveddate

,CAST(w.Created AS Date) AS 'Created Date'

,CAST(wi.FirstAssignedDate as date) AS 'First Assigned Date'

,CAST(wi.ResolvedDate AS Date) AS 'Resolved_Date'

,DateDiff(Hour, wi.FirstAssignedDate, wi.ResolvedDate) AS 'Hours_To_Close'

,DateDiff(Minute, wi.FirstAssignedDate, wi.ResolvedDate) AS 'Minute_To_Close'

,DateDiff(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) AS 'Total Downtime'

,CASE

WHEN (DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) / 60) < 10 and (DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) / 60) >= 0 THEN RIGHT('00' + CAST((DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) / 60) as varchar(2)), 2) + ':' + RIGHT('00' + CAST(DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) % 60 as varchar(2)), 2)

ELSE CAST((DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) / 60) as varchar(10)) + ':' + RIGHT('00' + CAST(ABS(DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) % 60) as varchar(2)), 2)

END as [Time down]

FROM

ServiceManagement_New.cachert.csnCached_MT_System_WorkItem_Incident AS wi

LEFT JOIN ServiceManagement_New.dbo.WorkItem AS w ON w.WorkItemID = wi.Source_Id

WHERE 1 = 1

AND wi.Impact like 'P1 - Critical'

AND w.created >= '2021-01-01'

--AND @createdFilter

Answers

  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    edited April 2021

    You do not need to make the column clickable. Having the ID column the portal Javascript will open the corresponding ticket. The trick is having the proper column header. ID will not work but Id will. Most of the time you need to wrap the query so your AS values work. See https://community.cireson.com/discussion/comment/16787#Comment_16787 were an example is provided.

  • alex_kokinalex_kokin Member IT Monkey ✭

    What am I doing wrong?


    Select

      wi.Source_id AS 'Ticket_ID'

      ,wi.Title

    ,wi.Status

      ,Month(w.Created) AS 'CreatedMonth'

    ,CAST(wi.FirstAssignedDate AS DateTime) AS 'Assigned_Date'

      ,CAST(wi.ResolvedDate AS DateTime) AS 'Resolved_Date'

    ,DateDiff(Hour, wi.FirstAssignedDate, wi.ResolvedDate) AS 'Hours_To_Close'

      ,DateDiff(Minute, wi.FirstAssignedDate, wi.ResolvedDate) AS 'Minute_To_Close'

      ,DateDiff(Hour, wi.FirstAssignedDate, wi.ResolvedDate) AS 'Total Downtime'

    ,CASE

      WHEN (DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) / 60) < 10 and (DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) / 60) >= 0 THEN RIGHT('00' + CAST((DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) / 60) as varchar(2)), 2) + ':' + RIGHT('00' + CAST(DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) % 60 as varchar(2)), 2)

      ELSE CAST((DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) / 60) as varchar(10)) + ':' + RIGHT('00' + CAST(ABS(DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) % 60) as varchar(2)), 2)

      END as [Time down]

    FROM 

    ServiceManagement_New.cachert.csnCached_MT_System_WorkItem_Incident AS wi

    LEFT JOIN ServiceManagement_New.dbo.WorkItem AS w ON w.WorkItemID = wi.Source_Id

    WHERE 1 = 1

    AND wi.Impact like 'P1 - Critical' 

      AND w.created >= '2021-01-01'

      AND @createdFilter

  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭

    For the JavaScript to pick up the ID you have to wrap the query, and ID must be in the correct format

    Select * from XXX

    (Select

      wi.Source_id AS 'Id'

      ,wi.Title

    ,wi.Status .......

    ......WHERE 1 = 1

    AND wi.Impact like 'P1 - Critical' 

      AND w.created >= '2021-01-01') as XXX

    Where @createdFilter

  • alex_kokinalex_kokin Member IT Monkey ✭

    The above did not work for me. Can you please correct what I have here and show me the error of my ways please.


    SELECT * FROM

    (

    Select

    dbo.fnTicketLink(wi.Source_ID) AS 'Ticket_ID'

    ,wi.Source_ID

    ,wi.Title

    ,CAST(w.Created AS Date) AS 'Created Date'

    ,CAST(wi.FirstAssignedDate as date) AS 'First Assigned Date'

    ,CAST(wi.ResolvedDate AS Date) AS 'Resolved_Date'

    ,DateDiff(Hour, wi.FirstAssignedDate, wi.ResolvedDate) AS 'Hours_To_Close'

    ,DateDiff(Minute, wi.FirstAssignedDate, wi.ResolvedDate) AS 'Minute_To_Close'

    ,DateDiff(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) AS 'Total Downtime'

    ,CASE

    WHEN (DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) / 60) < 10 and (DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) / 60) >= 0 THEN RIGHT('00' + CAST((DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) / 60) as varchar(2)), 2) + ':' + RIGHT('00' + CAST(DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) % 60 as varchar(2)), 2)

    ELSE CAST((DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) / 60) as varchar(10)) + ':' + RIGHT('00' + CAST(ABS(DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) % 60) as varchar(2)), 2)

    END as [Time down]

    FROM

    ServiceManagement_New.cachert.csnCached_MT_System_WorkItem_Incident AS wi

    LEFT JOIN ServiceManagement_New.dbo.WorkItem AS w ON w.WorkItemID = wi.Source_Id

    WHERE 1 = 1

    AND wi.Impact like 'P1 - Critical'

    AND w.created >= '2021-01-01'

    ) as DerT

    WHERE @createdFilter

  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭

    dbo.fnTicketLink(wi.Source_ID) AS 'Ticket_ID' will not work

    Must use Id for the alias for the java script to pickup the Id of the work item you want to open so it must look like

    dbo.fnTicketLink(wi.Source_ID) AS 'Id'

  • alex_kokinalex_kokin Member IT Monkey ✭

    Thank you for the quick response. When I apply it in the portal, the query does not pull anything...


    SELECT * FROM


    (


    Select


    dbo.fnTicketLink(wi.Source_ID) AS 'Id'


    ,wi.Source_ID


    ,wi.Title


    ,CAST(w.Created AS Date) AS 'Created Date'


    ,CAST(wi.FirstAssignedDate as date) AS 'First Assigned Date'


    ,CAST(wi.ResolvedDate AS Date) AS 'Resolved_Date'


    ,DateDiff(Hour, wi.FirstAssignedDate, wi.ResolvedDate) AS 'Hours_To_Close'


    ,DateDiff(Minute, wi.FirstAssignedDate, wi.ResolvedDate) AS 'Minute_To_Close'


    ,DateDiff(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) AS 'Total Downtime'


    ,CASE


    WHEN (DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) / 60) < 10 and (DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) / 60) >= 0 THEN RIGHT('00' + CAST((DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) / 60) as varchar(2)), 2) + ':' + RIGHT('00' + CAST(DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) % 60 as varchar(2)), 2)


    ELSE CAST((DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) / 60) as varchar(10)) + ':' + RIGHT('00' + CAST(ABS(DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) % 60) as varchar(2)), 2)


    END as [Time down]


    FROM


    ServiceManagement_New.cachert.csnCached_MT_System_WorkItem_Incident AS wi


    LEFT JOIN ServiceManagement_New.dbo.WorkItem AS w ON w.WorkItemID = wi.Source_Id


    WHERE 1 = 1


    AND wi.Impact like 'P1 - Critical'


    AND w.created >= '2021-01-01'


    ) as DerT


    WHERE @createdFilter

  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭

    Does it work in SSMS without the createddate filter?

  • alex_kokinalex_kokin Member IT Monkey ✭

    No and this is going to be in a dashboard as a TSQ Table Widget.

  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭

    You need to work on the SQL so it works in SSMS. Then when you have the logic correct to display the content in SSMS then you can copy into the portal.

  • alex_kokinalex_kokin Member IT Monkey ✭
    edited April 2021

    Yeah I know but its not working in either. Unfortunately I am not much of a SQL guy.... Thank you very much for your help by the way.


    I just need the results to be clickable to open the actual ticket from the table... This is a table that works. for example.


    Select

      wi.Source_Id AS 'Ticket_ID'

      ,wi.Title

      ,CAST(wi.FirstAssignedDate AS DateTime) AS 'Assigned_Date'

      ,CAST(wi.ResolvedDate AS DateTime) AS 'Resolved_Date'

    ,DateDiff(Hour, wi.FirstAssignedDate, wi.ResolvedDate) AS 'Hours_To_Close'

      ,DateDiff(Minute, wi.FirstAssignedDate, wi.ResolvedDate) AS 'Minute_To_Close'

       

    ,CASE

      WHEN (DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) / 60) < 10 and (DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) / 60) >= 0 THEN RIGHT('00' + CAST((DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) / 60) as varchar(2)), 2) + ':' + RIGHT('00' + CAST(DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) % 60 as varchar(2)), 2)

      ELSE CAST((DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) / 60) as varchar(10)) + ':' + RIGHT('00' + CAST(ABS(DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) % 60) as varchar(2)), 2)

      END as [Time down]

    FROM 

    ServiceManagement_New.cachert.csnCached_MT_System_WorkItem_Incident AS wi

    LEFT JOIN ServiceManagement_New.dbo.WorkItem AS w ON w.WorkItemID = wi.Source_Id

    WHERE 1 = 1

    AND wi.Impact like 'P1 - Critical' 

      AND w.created >= '2021-01-01'

      AND @createdFilter

  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭

    Start simple then add the requirements

    This works for me in SSMS

    Select * From

    (Select

     wi.Source_Id AS 'Id'

     ,wi.Title

     ,CAST(wi.FirstAssignedDate AS DateTime) AS 'Assigned_Date'

     ,CAST(wi.ResolvedDate AS DateTime) AS 'Resolved_Date'

     ,DateDiff(Hour, wi.FirstAssignedDate, wi.ResolvedDate) AS 'Hours_To_Close'

     ,DateDiff(Minute, wi.FirstAssignedDate, wi.ResolvedDate) AS 'Minute_To_Close'

     ,CASE

     WHEN (DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) / 60) < 10 and (DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) / 60) >= 0 THEN RIGHT('00' + CAST((DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) / 60) as varchar(2)), 2) + ':' + RIGHT('00' + CAST(DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) % 60 as varchar(2)), 2)

     ELSE CAST((DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) / 60) as varchar(10)) + ':' + RIGHT('00' + CAST(ABS(DATEDIFF(MINUTE, wi.FirstAssignedDate, wi.ResolvedDate) % 60) as varchar(2)), 2)

     END as [Time down]

     FROM ServiceManagement.cachert.csnCached_MT_System_WorkItem_Incident AS wi

     LEFT JOIN ServiceManagement.dbo.WorkItem AS w ON w.WorkItemID = wi.Source_Id

     WHERE 1 = 1

     --AND wi.Impact like 'P1 - Critical'

     AND w.created >= '2021-01-01') Results

    Then verify that works in the portal.

    The Id will allow the javascript to work for the hyperlink into the specific work item

    Then when you add the @createdFilter that will only work if you have the createddate as a column in the results. (And it must have the proper as "createdDate" for the JS to see it)

  • alex_kokinalex_kokin Member IT Monkey ✭
    edited April 2021

    That worked, thank you...

  • alex_kokinalex_kokin Member IT Monkey ✭

    Can I borrow your expertise one more time? I have created a new TAB called RCA in the Incident form and the incident .js. I need to report on these fields in a dashboard also. How might I pull these extensions?



    /*********/

    /** TAB **/

    /*********/

    {

          name: "RCA",

          content: [

            {

              customFieldGroupList: [

                {

                  name: "RCA",

                  rows:[

                    {

                      columnFieldList: [

                         

                        { DataType: "String", PropertyDisplayName: "ISSUE", PropertyName: "RCA_ISSUE",},

    { DataType: "String", PropertyDisplayName: "IMPACT", PropertyName: "RCA_IMPACT",},

    { DataType: "Enum", PropertyDisplayName: "AFFECTED SYSTEMS", PropertyName: "Affected Systems", EnumId: 'e4ee206c-a502-fdab-ecb2-e0b042936aeb',},

    { DataType: "DateTime", PropertyDisplayName: "START", PropertyName: "RCA_Start",},

    { DataType: "DateTime", PropertyDisplayName: "STOP", PropertyName: "RCA_End",},

    { DataType: "Enum", PropertyDisplayName: "CAUSE", PropertyName: "RCA_CAUSE", EnumId: '9f19b929-0a53-4e88-8c44-b5ce34b8832a'},

    { DataType: "LongString", PropertyDisplayName: "CAUSE (Other)", PropertyName: "RCACAUSEDETAILS", MinLength: 0, MaxLength: 4000},

    { DataType: "LongString", PropertyDisplayName: "RESOLVED", PropertyName: "RCA_RESOLVED", MinLength: 0, MaxLength: 4000 },

    { DataType: "LongString", PropertyDisplayName: "NEXTSTEPS", PropertyName: "RCA_NEXTSTEP",MinLength: 0, MaxLength: 4000},

    { DataType: "LongString", PropertyDisplayName: "ADO Links", PropertyName: "ADO_Links",MinLength: 0, MaxLength: 4000}

                      ],

                    }

                  ]

                },

    {

                  name: "FileAttachments",

                  type: "fileAttachmentsDragDrop"

                },

                 

              ]

            }

          ]

        },

        /*********/

        /** TAB **/

        /*********/

  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭

    Just add a join to the script

     Left JOIN ServiceManager.dbo.MTV_System$WorkItem$Incident SMID on SMID.BaselineId_7557DE93_D437_6407_F110_E842C028CAB2 = wi.Id


    Then go into ServiceManager.dbo.MTV_System$WorkItem$Incident and do a top 10 or something to get all the columns names. There you can pull out your custom columns added.

  • alex_kokinalex_kokin Member IT Monkey ✭

    Not working because the RCA stuff is not in MT_System$WorkItem$Incident, but in MT_ClassExtension_14ffcecf_9125_47a4_99d1_215e3beeea56

  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭

    The MTV as it is a view should include the class extensions values

  • alex_kokinalex_kokin Member IT Monkey ✭
    edited April 2021

    Ok so this is working now, however the RCA_CAUSE and Affected System are unum and not pulling the result from the list. there.


    SELECT TOP 1000 WII.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C [ID] , WII.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 [Title]    

       , RCA.RCA_AFFECTED_SYSTEMS_8719F77D_2AF6_83C5_8800_EFD092EBB409 [Affected Systems]

       , RCA.RCA_CAUSE_13D3C870_5122_1316_2231_CB0B83F61C48 [Cause]

       , RCASE.RCA_Start_8116A750_0A4E_0D09_0B1A_68960DD84F00 [IN Start]

       , RCASE.RCA_End_87090F62_B20D_8CFF_0647_26C03100283A [IN Stop]

       , RCA_DURATION_727B6738_9468_CCF5_0C54_7167866E8FBB [Total Downtime]

    --   , * -- compute duration based on start/end?

      FROM MT_System$WorkItem$Incident WII

      LEFT JOIN MT_ClassExtension_14ffcecf_9125_47a4_99d1_215e3beeea56 RCA on WII.BaseManagedEntityId = RCA.BaseManagedEntityId

      LEFT JOIN [dbo].[MT_ClassExtension_e99b8ab0_e566_4c1c_bbfd_f6c272e3cd88] RCASE ON WII.BaseManagedEntityId = RCASE.BaseManagedEntityId

     

  • alex_kokinalex_kokin Member IT Monkey ✭


    SELECT TOP 1000 WII.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C [ID],    SMID.RCA_ISSUE_541F4E25_1029_904A_275E_3A31FA68F757 [Issue]      , SMID.AffectedSystems_22E088B9_ED89_BC5B_7C81_0BB044ED082A [Affected Systems]   , SMID. RCA_CAUSE_13D3C870_5122_1316_2231_CB0B83F61C48 [Cause]   ,CAST(RCA_Start_8116A750_0A4E_0D09_0B1A_68960DD84F00 AS DateTime) AS 'IN_Start'   ,CAST(RCA_End_87090F62_B20D_8CFF_0647_26C03100283A AS DateTime) AS 'IN_Stop'  --,DateDiff(Hour, RCA_Start_8116A750_0A4E_0D09_0B1A_68960DD84F00, RCA_End_87090F62_B20D_8CFF_0647_26C03100283A) AS 'Hours_To_Close'  --,DateDiff(Minute, RCA_Start_8116A750_0A4E_0D09_0B1A_68960DD84F00, RCA_End_87090F62_B20D_8CFF_0647_26C03100283A) AS 'Minute_To_Close'      ,CASE  WHEN (DATEDIFF(MINUTE, RCA_Start_8116A750_0A4E_0D09_0B1A_68960DD84F00, RCA_End_87090F62_B20D_8CFF_0647_26C03100283A) / 60) < 10 and (DATEDIFF(MINUTE, RCA_Start_8116A750_0A4E_0D09_0B1A_68960DD84F00, RCA_End_87090F62_B20D_8CFF_0647_26C03100283A) / 60) >= 0 THEN RIGHT('00' + CAST((DATEDIFF(MINUTE, RCA_Start_8116A750_0A4E_0D09_0B1A_68960DD84F00, RCA_End_87090F62_B20D_8CFF_0647_26C03100283A) / 60) as varchar(2)), 2) + ':' + RIGHT('00' + CAST(DATEDIFF(MINUTE, RCA_Start_8116A750_0A4E_0D09_0B1A_68960DD84F00, RCA_End_87090F62_B20D_8CFF_0647_26C03100283A) % 60 as varchar(2)), 2)  ELSE CAST((DATEDIFF(MINUTE, RCA_Start_8116A750_0A4E_0D09_0B1A_68960DD84F00, RCA_End_87090F62_B20D_8CFF_0647_26C03100283A) / 60) as varchar(10)) + ':' + RIGHT('00' + CAST(ABS(DATEDIFF(MINUTE, RCA_Start_8116A750_0A4E_0D09_0B1A_68960DD84F00, RCA_End_87090F62_B20D_8CFF_0647_26C03100283A) % 60) as varchar(2)), 2)  END as [Total_Downtime]  FROM MT_System$WorkItem$Incident WII Left JOIN ServiceManager.dbo.MTV_System$WorkItem$Incident SMID ON WII.BaseManagedEntityId = SMID.BaseManagedEntityId

     where RCA_CAUSE_13D3C870_5122_1316_2231_CB0B83F61C48 is not null


    Where in my script above would I apply the DisplayStringView stuff sow that the results show for the Affected system and the Cause columns?

Sign In or Register to comment.