Home› All Other Feature Requests and Ideas
We appreciate you taking the time to vote and add your suggestions to make our products awesome! Your request will be submitted to the community for review and inclusion into the backlog.

We recommend reviewing what is submitted before posting, in case your idea has already been submitted by another community member. If it has been submitted, vote for that existing feature request (by clicking the up arrow) to increase its opportunity of being added to Cireson solutions.

For more information around feature requests in the Cireson Community click here.
Options

Add Unique WorkItem Value to Billable Time Table

Daniel_Polivka1Daniel_Polivka1 Customer IT Monkey ✭
in All Other Feature Requests and Ideas

I'd like to see a value in the Billable Time Table in the ServiceManagement DB that allows for easy lookup of the Work Item ID that the billable time is related to. Right now, these two tables don't share any commonality between themselves, and it seems like a simple oversight that would be capable of producing literally any reports and analytics surrounding billable time.

  1. workitem
  2. cachert.csnCached_MT_System_WorkItem_BillableTime


1
Up Down
1 votes

Active · Last Updated

Comments

  • Options
    Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭
    edited November 2022

    @Daniel_Polivka1 - The Cached_MT_System_WorkItem_BillableTime table isn't purposely built in the same way that the WorkItem table is. It is just a cache of the MT_System$WorkItem$BillableTime table from ServiceManager. Your best bet for matching billable time to work items is using ServiceManager:

    SELECT i.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C 'WorkItemId'
,bt.TimeInMinutes_D82FF0BA_E571_28A3_9E56_422AE26C69E0 'TimeInMinutes'
,u.DisplayName 'WorkingUser'
FROM
MT_System$WorkItem$BillableTime bt WITH (NOLOCK)
JOIN Relationship wr WITH (NOLOCK)
ON wr.TargetEntityId = bt.BaseManagedEntityId
JOIN MT_System$WorkItem$Incident i WITH (NOLOCK)
ON wr.SourceEntityId = i.BaseManagedEntityId
JOIN Relationship ur WITH (NOLOCK)
ON ur.SourceEntityId = bt.BaseManagedEntityId
JOIN MT_System$Domain$User u WITH (NOLOCK)
ON ur.TargetEntityId = u.BaseManagedEntityId


  • Options
    Daniel_Polivka1Daniel_Polivka1 Customer IT Monkey ✭
    edited November 2022

    Thank you!! I have been playing in the ServiceManagement DB quite a bit, but haven't really plunged into the SCSM DB yet. I wanted to get all the work items that could have billable time, so i did a quick and dirty UNION ALL with your query. Not sure if there's a better way, but your piece gave me that step I was missing. Thanks again!

    SELECT 
IR.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C as 'ID',
BT.TimeInMinutes_D82FF0BA_E571_28A3_9E56_422AE26C69E0 as 'Time Worked',
USR.DisplayName as 'Analyst',
BT.LastUpdated_A33608CA_CD35_3278_8D5D_0F44B01717F1 as 'Last Updated'
FROM 
MT_System$WorkItem$BillableTime as BT WITH (NOLOCK)
JOIN Relationship as WR WITH (NOLOCK) ON WR.TargetEntityId = BT.BaseManagedEntityId
JOIN MT_System$WorkItem$Incident as IR WITH (NOLOCK) ON WR.SourceEntityId = IR.BaseManagedEntityId
JOIN Relationship as UR WITH (NOLOCK) ON UR.SourceEntityId = BT.BaseManagedEntityId
JOIN MT_System$Domain$User as USR WITH (NOLOCK) ON ur.TargetEntityId = USR.BaseManagedEntityId

UNION ALL

SELECT 
CR.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C as 'ID',
BT.TimeInMinutes_D82FF0BA_E571_28A3_9E56_422AE26C69E0 as 'Time Worked',
USR.DisplayName as 'Analyst',
BT.LastUpdated_A33608CA_CD35_3278_8D5D_0F44B01717F1 as 'Last Updated'
FROM 
MT_System$WorkItem$BillableTime as BT WITH (NOLOCK)
JOIN Relationship as WR WITH (NOLOCK) ON WR.TargetEntityId = BT.BaseManagedEntityId
JOIN MT_System$WorkItem$ChangeRequest as CR WITH (NOLOCK) ON WR.SourceEntityId = CR.BaseManagedEntityId
JOIN Relationship as UR WITH (NOLOCK) ON UR.SourceEntityId = BT.BaseManagedEntityId
JOIN MT_System$Domain$User as USR WITH (NOLOCK) ON ur.TargetEntityId = USR.BaseManagedEntityId

UNION ALL

SELECT 
PR.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C as 'ID',
BT.TimeInMinutes_D82FF0BA_E571_28A3_9E56_422AE26C69E0 as 'Time Worked',
USR.DisplayName as 'Analyst',
BT.LastUpdated_A33608CA_CD35_3278_8D5D_0F44B01717F1 as 'Last Updated'
FROM 
MT_System$WorkItem$BillableTime as BT WITH (NOLOCK)
JOIN Relationship as WR WITH (NOLOCK) ON WR.TargetEntityId = BT.BaseManagedEntityId
JOIN MT_System$WorkItem$Problem as PR WITH (NOLOCK) ON WR.SourceEntityId = PR.BaseManagedEntityId
JOIN Relationship as UR WITH (NOLOCK) ON UR.SourceEntityId = BT.BaseManagedEntityId
JOIN MT_System$Domain$User as USR WITH (NOLOCK) ON ur.TargetEntityId = USR.BaseManagedEntityId

UNION ALL

SELECT 
SR.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C as 'ID',
BT.TimeInMinutes_D82FF0BA_E571_28A3_9E56_422AE26C69E0 as 'Time Worked',
USR.DisplayName as 'Analyst',
BT.LastUpdated_A33608CA_CD35_3278_8D5D_0F44B01717F1 as 'Last Updated'
FROM 
MT_System$WorkItem$BillableTime as BT WITH (NOLOCK)
JOIN Relationship as WR WITH (NOLOCK) ON WR.TargetEntityId = BT.BaseManagedEntityId
JOIN MT_System$WorkItem$ServiceRequest as SR WITH (NOLOCK) ON WR.SourceEntityId = SR.BaseManagedEntityId
JOIN Relationship as UR WITH (NOLOCK) ON UR.SourceEntityId = BT.BaseManagedEntityId
JOIN MT_System$Domain$User as USR WITH (NOLOCK) ON ur.TargetEntityId = USR.BaseManagedEntityId
Sign In or Register to comment.