Links in SQL Table Chart

alex_kokin

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

Brian_Wiest

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_kokin

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_Wiest

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_kokin

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_Wiest

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_kokin

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_Wiest

Does it work in SSMS without the createddate filter?

alex_kokin

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

Brian_Wiest

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_kokin

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_Wiest

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_kokin

That worked, thank you...

alex_kokin

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_Wiest

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_kokin

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

Brian_Wiest

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

alex_kokin

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_kokin

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?