SLAs in Standard Listing Reports - Part Deux(2)

SLAs in Standard Listing Reports - Part Deux(2)

SLA data in Standard Listing Reports

This blog is a follow up to a topic presented at xChange2015.  It presents a 3rd solution to the dilemma of how to use native SBM reports to show listings of items alongside current SLA data.  While the two solutions offered in previous blog entry (How can I do SLA Reporting via Standard SBM listing Reports?) offered viable solutions, there were some limitations to both.

 

Why?

v Only view SLAs on Incident by Incident basis

v Bring SLAs into Incident Dashboards

v Customizable out of box SLA Reporting



The approach I'm proposing below will provide some additional benefits and flexibility to generate many different listing reports.  

1.     First, it allows you to display more than just primary table data but also show SLA data with it which can be sorted and highlighted.  

2.     Second, another benefit is an item might have more than one active SLA (ie. Time to Respond and Time to Restore for an Incident) and this will display data for both SLAs.  This approach accounts for multiple SLAs.

3.     Third, the underlying SLAs themselves have all this great date and time information for ‘time to next level’ and ‘time until violation’ so why not show calculations based on such useful detail?


How?

v SLA Data in Aux table

v Kept Current via DB Triggers

v SBM Join Reports

 

Below is a high level description of the pieces needed to implement SLA listing reports.  

1.     Create a new auxiliary table SLAs where data from SLA_ITEM can be pulled in and kept current.
 b2ap3_thumbnail_SLA-Aux.PNG

2.     Implement Insert and Update DB triggers against the SLA_ITEM table to insert into the new SLAs as well as keep it current.   The example I included limits the tables from which I would like to receive real time SLA data which limits the size of the auxiliary table.  Great caution must be taken to limit when the update trigger is called.  I chose changes to specific fields since these rows get updated with every cycle of the SLA processing which is I believe something like 30secs in our environment.  The triggers included in the solution were built in a SQL Server DB but similar triggers could be written in Oracle.

3.     Create a report against the SLAs table and Join with your desired primary table.   This example just shows relationships with Incidents but could easily be extended to work with SRs or any other table.  You will just need to create a Single Relational field for each primary table for which you’d like to capture SLA data.  Due to some limitations with join reports and sorting you may need to also include sub-relational fields like I did for Incident Priority.  This was done since I wanted to sort not just by SLA times or Risk Levels but also by Priority of the Incident.   The report examples shown below use a custom html template as well as some js/css for extra manipulation.  

Examples: (due to low resolution also included in zip)

b2ap3_thumbnail_SLA-NextLevel.PNG
b2ap3_thumbnail_SLA-TimeToViolation_big.png


 This only shows a couple basic options which were initially delivered.  However now that we have easier access to the SLA data there are many more possibilities to deliver based upon that data.  I'm interested for others to share ideas or thoughts not yet considered.  For those who got to see the live demonstration at xChange2015 thank you for your participation and patience in getting this blog out there.

 

Results?

 v Focus on most Urgent & Relevant Incidents
 v Flexibility to sort by Incident Attributes AND SLA Risk Level
 
v Display Actual Times

Ø Until Next Risk Level
 Ø Until Risk in Violation

v ROI – Improved SLA performance è TBD…..

   


***[Special Thanks to Sam Sabra for the custom template and supporting js/css]***

Keep in mind that while we have tested this and began using in our Production environment it comes without warranty or support.  So buyer beware.  All rights reserved, and most lefts too.

Supporting Files located in the following zip.

 Custom-SLA-Listing.zip

b2ap3_thumbnail_SLA.png

What's new in SBM Composer 11.0: Endpoints
What's new in SBM Composer 11.0: Forms

Related Posts

Comments 4

 
Derek George on Thursday, 10 September 2015 11:53

After seeing the SBM 11 part 3 sneak peek this may be accomplished without the DB triggers by simply using the new Virtual Aux table views. I'd have to test it out but certainly more options for getting system and not system table data into an Aux table where standard reporting can be utilized.

After seeing the SBM 11 part 3 sneak peek this may be accomplished without the DB triggers by simply using the new Virtual Aux table views. I'd have to test it out but certainly more options for getting system and not system table data into an Aux table where standard reporting can be utilized.
David Berner on Sunday, 01 November 2015 04:26

Thanks for this detailed post. It would be great if someone made a follow up post on how to achieve this using advanced reporting in SBM 11

Thanks for this detailed post. It would be great if someone made a follow up post on how to achieve this using advanced reporting in SBM 11
Derek George on Monday, 02 November 2015 12:43

I don't have an immediate need to replace the triggers currently being used with View Definitions. However as I have time I may experiment with this in parallel with the goal of no longer needing the small amount of overhead generated by the triggers and duplicate data.

Should be easy enough to do using similar sql to the triggers. I've been experimenting with and have implemented several views already in 10.1.5.2. This shouldn't be difficult but there may also be some trade-offs or limitations of reporting against the views vs an actual aux table built via triggers. One that comes to mind is the listing report for a view, the link first takes you to the view field details before being able to click on say the underlying Incident relational field. In the current approach we are able to click to view the Incident. There may be ways around it and there may be other things uncovered once we give it a try. Maybe a follow up blog post at some point.

I don't have an immediate need to replace the triggers currently being used with View Definitions. However as I have time I may experiment with this in parallel with the goal of no longer needing the small amount of overhead generated by the triggers and duplicate data. Should be easy enough to do using similar sql to the triggers. I've been experimenting with and have implemented several views already in 10.1.5.2. This shouldn't be difficult but there may also be some trade-offs or limitations of reporting against the views vs an actual aux table built via triggers. One that comes to mind is the listing report for a view, the link first takes you to the view field details before being able to click on say the underlying Incident relational field. In the current approach we are able to click to view the Incident. There may be ways around it and there may be other things uncovered once we give it a try. Maybe a follow up blog post at some point.
Ashish Kochaar on Saturday, 22 July 2017 07:44

Very Thanks, Derek for providing us this valuable post.
I am happy to see this.
If possible then make some keywords like this in your post so that this would be reached the maximum audience.
Chase Bank Locations
Bank of America Location Near Me
US Bank Locations
Wells Fargo Location Near Me
TD Bank Nearest to You
TCF Bank Nearest to You
Regions Bank Nearest To You
SunTrust Routing Number Nearest to You
PNC Routing Number Location Near Me
CitiBank Locations

Very Thanks, Derek for providing us this valuable post. I am happy to see this. If possible then make some keywords like this in your post so that this would be reached the maximum audience. [url=http://allbankroutingnumber.com/jp-morgan-chase-bank-routing-number-location-near-me]Chase Bank Locations[/url] [url=http://allbankroutingnumber.com/bank-of-america-routing-number-and-locations-near-me]Bank of America Location Near Me[/url] [url=http://allbankroutingnumber.com/us-bank-routing-number-and-locations-near-me]US Bank Locations[/url] [url=http://allbankroutingnumber.com/wells-fargo-bank-routing-number-and-locations-near-me] Wells Fargo Location Near Me[/url] [url=http://allbankroutingnumber.com/td-bank-routing-number-and-locations-near-me] TD Bank Nearest to You[/url] [url=http://allbankroutingnumber.com/tcf-bank-routing-number-and-locations-near-me] TCF Bank Nearest to You [/url] [url=http://allbankroutingnumber.com/regions-bank-routing-number-locations-near-me] Regions Bank Nearest To You [/url] [url=http://allbankroutingnumber.com/suntrust-bank-routing-number-locations-near-me] SunTrust Routing Number Nearest to You [/url] [url=http://allbankroutingnumber.com/pnc-bank-routingtransit-number-locations-near-me] PNC Routing Number Location Near Me[/url] [url=http://allbankroutingnumber.com/citibank-routing-number-locations-near-me] CitiBank Locations [/url]