Is there a way of showing Incident Classification full path in all areas (Category, DB etc)
Alfie_Thomas
Customer IT Monkey ✭
Hi guys,
I have followed these instructions and made the update to the default incident template so it now shows the full incident classification tree: Is there a way to view the full path for Incident Classification (Category column in Portal)? — Cireson Community
This however is still cutdown in the Category column in all views and database Category column in Cireson Analytics:
For some custom reporting I really need this column to show the full tree here. Does anyone know how to do this?
Thank you in advance,
Alfie.
Best Answer
-
Justin_Workman
Cireson Support Super IT Monkey ✭✭✭✭✭
@Alfie_Thomas - I don't think this will be possible in the CiresonAnalytics database because the enum hierarchy isn't stored in that database. However, it is stored in ServiceManagement so you are able to get the full path of the enum there. Here's a query that's just workitemId, title, and category:
with Enum as (
select e.EnumerationID,
ds.DisplayString as 'EnumDisplayString'
,e.ParentEnumerationID
,pds.DisplayString as 'ParentDisplayString'
from Enumeration e
join DisplayString ds
on e.EnumerationID = ds.ElementID
and ds.LocaleID = 'enu'
join DisplayString pds
on e.ParentEnumerationID = pds.ElementID
and pds.LocaleID = 'enu'
)
,cte as (
select EnumerationID, ParentEnumerationID, convert(varchar(max), concat(EnumDisplayString, '')) as path, 0 as parentcount
from Enum t
union all
select cte.EnumerationID, t.ParentEnumerationID, convert(varchar(max), concat(t.EnumDisplayString, '\', path)), parentcount + 1
from cte join
Enum t
on cte.ParentEnumerationID = t.EnumerationID
),
paths as(
select top (1) with ties *
from cte
order by row_number() over (partition by Enumerationid order by parentcount desc))
select
w.WorkItemId,
w.Title,
p.path as 'Category'
from workitem w
join paths p
on p.EnumerationID = w.CategoryId
You could add additional enum fields by joining to paths again.
0
Answers
@Alfie_Thomas - I don't think this will be possible in the CiresonAnalytics database because the enum hierarchy isn't stored in that database. However, it is stored in ServiceManagement so you are able to get the full path of the enum there. Here's a query that's just workitemId, title, and category:
with Enum as (
select e.EnumerationID,
ds.DisplayString as 'EnumDisplayString'
,e.ParentEnumerationID
,pds.DisplayString as 'ParentDisplayString'
from Enumeration e
join DisplayString ds
on e.EnumerationID = ds.ElementID
and ds.LocaleID = 'enu'
join DisplayString pds
on e.ParentEnumerationID = pds.ElementID
and pds.LocaleID = 'enu'
)
,cte as (
select EnumerationID, ParentEnumerationID, convert(varchar(max), concat(EnumDisplayString, '')) as path, 0 as parentcount
from Enum t
union all
select cte.EnumerationID, t.ParentEnumerationID, convert(varchar(max), concat(t.EnumDisplayString, '\', path)), parentcount + 1
from cte join
Enum t
on cte.ParentEnumerationID = t.EnumerationID
),
paths as(
select top (1) with ties *
from cte
order by row_number() over (partition by Enumerationid order by parentcount desc))
select
w.WorkItemId,
w.Title,
p.path as 'Category'
from workitem w
join paths p
on p.EnumerationID = w.CategoryId
You could add additional enum fields by joining to paths again.