BMC Remedy Assigned Group Incident Transfer(s) Query

 Computer, Software  Comments Off on BMC Remedy Assigned Group Incident Transfer(s) Query
Jan 222015
 

Had a request from a customer for a report showing the number of Remedy incidents transferred from his managed group to other groups during a given date range.
I figured out a pseudo lead/lag query for SQL Server 2008. This creates an output that aligns the current row with the previous row. This allows you to see FROM which group an incident was transferred TO.

--- Just for testing; these will be the parameters for the SSRS report.
declare @StartDate datetime
declare @EndDate datetime
set @StartDate = '1/1/2015'
set @EndDate = '1/15/2015'

-- Get Incidents from Assignment Log where ET did something and put them into a temporary table.
select distinct(incident_number)
into #tempincidents
from View_HPD_Help_Desk_Assignment_Log
where assigned_group = 'Enterprise Telecomm'
and submit_date between @StartDate and @EndDate
order by incident_number

Here’s the magic, query the Assignment Log again using the Incident numbers from above and assign a row number.

select
incident_number
, Assignee
, assigned_group
, submit_date
, ROW_NUMBER() over(order by incident_number, submit_date) Serial
into #tempxfersA
from View_HPD_Help_Desk_Assignment_Log
where Incident_Number in (select * from #tempincidents)

select a.Incident_Number
-- 'Previous' Assigned Groups
, b.Assigned_Group as AGroup
, b.Assignee as AssigneeB
, b.Serial as SerialB
-- 'Current' Assigned Groups
, a.Assigned_Group as BGroup
, A.Assignee
, a.Serial
into #tempxfersB
from #tempxfersA a
-- Join the table to itself matching the Row number minus 1 and Incident number for quality.
left join #tempxfersA b on a.Serial - 1 = b.Serial and a.Incident_Number = b.Incident_Number
order by a.Incident_Number, a.submit_date

Your query output should look like
Incident Number, ‘First Assigned Group’, Assignee, Row # from Table ‘A’, ‘Second Assigned Group’, Assignee, Row # from Table ‘B’

Lets count them up… This returns the overall counts for Enterprise Telecomm incident transfers to another group.
select
--- Count where Enterprise Telecomm transferred an incident to another group.
COUNT(Case when AGroup = 'Enterprise Telecomm' and BGroup <> 'Enterprise Telecomm' then 1 end) as GroupXfers ,
--- While we are at it, count where a member of Enterprise Telecom transfered (re-assigned) an incident to someone else in the Telecomm group.
COUNT(Case when AGroup = 'Enterprise Telecomm' and BGroup = 'Enterprise Telecomm' and Assignee <> AssigneeB then 1 end) as InternalXfers
from #tempxfersB

And now let’s tally up transfers by the Assignee.
select
AssigneeB,
COUNT(Case when AGroup = 'Enterprise Telecomm' and BGroup <> 'Enterprise Telecomm' then 1 end) as GroupXfers ,
COUNT(Case when AGroup = 'Enterprise Telecomm' and BGroup = 'Enterprise Telecomm' and Assignee <> AssigneeB then 1 end) as InternalXfers
from #tempxfersB
group by AssigneeB

Caveats: This just shows transfers to other groups and not necessarily for resolution. In my case, transfers can happen multiple times between groups on one incident without resolution.

TransferTicketCounts

Adding StripLines with Report Builder

 Computer, Sharepoint, Software  Comments Off on Adding StripLines with Report Builder
Feb 062014
 

I have a Forecast/Trending Chart that has past and future dates. I needed to denote Today with a vertical StripLine on the chart. (Click on images for larger version.)

Forecast



You can add a Marker with an expression easily.

MarkerExpress



In your series properties; create an expression for your Marker. In my scenario, it was: =IIF(Fields!Date.value = Today(), “Star10”, “None”)
This sets a Star at the Today point on the X axis.
This is just OK, but I needed to make this a little clearer based on the X-Axis interval.

To add a StripLine, click on your X Axis to bring up the properties window and look for the StripLine section under appearance.
Open the ChartStripLine Collection Editor and Add a new stripline.
Give your line a Color and set BorderStyle to Solid. By default it’s Default and won’t show up on your chart.
Set your IntervalOffsetType to Days.
In IntervalOffset; Use an expression to calculate the number of days between the minimum date in your dataset and Today.
The expression in this scenario is: =DateDiff(DateInterval.Day,min(Fields!date.Value), Today())+1



StripEditor