Automated Reporting using SSRS

The SCSM reporting out-of-the-box is a underwhelming, so we augmented them with a series of manual reports, to both manage workload and also report progress to our stakeholders. A couple of months ago we undertook an exercise to automate these manual reports using SQL Server Reporting Services (SSRS) and the Data Warehouse (DW) database.

It required us to develop an intimate knowledge of the table/view relationships of the DW, which was the bulk of the effort. Once the queries were written, the actual development of the reports (achieved using Business Intelligence Development Studio), was relatively painless.

SSRS as a presentation medium is quite good, with an intuitive interface and easy ability to schedule and subscribe to reports.

The end result is the elimination of the manual effort on a daily, weekly and monthly basis to produce the reports…and the capability for users to help themselves to get the information they want.

Listing of reports

Listing of reports available on SSRS

Example report that is calculated in real-time

Example report that is calculated in real-time

Example of graphs

Example of graphs

Posted in Automation, Reporting | Tagged | Leave a comment

Solution! Sending logical notifications for Review Activities

One of the most frustrating elements of SCSM is the limited ability to include items from a parent Service Request in Review Activity notifications, and the bug with selecting the incorrect manager as a Reviewer:

  1. The name of the Affected User of the parent Service Request not being available to insert in the Review Activity notification
  2. The incorrect line manager being selected for Requests created in the Service Manager console – when Line Manager Should Review is set to True, the manager of the Created By user is selected instead of the Affected User‘s manager

Well, my friends at Olikka and I have developed a solution using Orchestrator that addresses these 2 frustrations. Our Runbook Activity captures the Affected User’s name and inserts into the title of the Review Activity, and adds the Affected User’s manager as a Reviewer in the same Review Activity. Now, when a notification is triggered (when the status of the Review Activity changes from Pending to In Progress), the correct manager is notified, and the person can now easily determine who is making the request.

Orchestrator Runbook

Orchestrator Runbook

  1. The Service Request template is created with the Runbook Activity inserted as the first Activity
  2. A Service Request is created, either in the SM console or via the Self-Service Portal
  3. The Runbook kicks off and updates the subsequent Review Activity:
    1. The name of the Affected User of the parent SR is captured and inserted into the Review Activity Title
    2. The Line Manager of the Affected User is determined and is inserted as a Reviewer
  4. After the Runbook Activity completes, the Review Activity status is set to In Progress, and the notification workflow is triggered.
  5. The Manager processes the approval or rejection
  6. The Review Activity is marked as complete.
Approval Process with Orchestrator

Approval Process with Orchestrator

Whilst it is disappointing that we had to use Orchestrator to address what should be standard functionality, but it’s pleasing that a manual workaround has been eliminated.

Here’s the Runbook for you to look at (use in a Dev/Test environment):

UPDATE: When you insert the Runbook Activity into a Service Request template, you have to specify the mapping value of the ServiceRequestID (which is the parent work item ID) with the Work Item ID:

Configuring the Runbook Activity

Configuring the Runbook Activity


Posted in Automation, Notifications | Tagged , , | 4 Comments

View All Activities in a Service Request

Following on from the great code found here to create a view that lists all Activities for a Change Request:

I created a similar View, but for Service Requests.

Here is the code:


Posted in Build | Tagged | 2 Comments

Service Manager QuickGuides

Hi Everyone,

To assist with training and awareness of the Service Manager tool, we developed a series of one page QuickGuides, aligned to processes, that enable users to quickly get up-to-speed with the basic operations of Service Manager.

Please feel free to use them, and as always, your feedback is welcome!



Posted in Training | Tagged | Leave a comment

Building the Service Catalog

Link: Service Requests Build Status
The building of Request Offerings in Service Manager is relatively straightforward, given the wizards provided.
But you still need to prepare the Service Requests you want to model. I created this input document to assist in tracking the template and request offering for each type of Service Requesst we wanted in the tool.

Posted in Build | Tagged , , | Leave a comment

Building Notifications

Link: Notifications Input

Service Manager doesn’t come with any notifications configured, so you have to do it all yourself. I chose to use the Notifications module (as opposed to Workflow) for the configuration of all our notifications.  It can quickly become unruly given the number of templates and subscriptions that are required.

I came up with this input document to help keep track of all the notifications required.  The sequentially numbering system is particularly useful to marry up the template with the subscription, and whilst having to maintain a separate document is a pain, it helps with the notification sprawl.

Posted in Build | Tagged , | Leave a comment

Service Manager Build Document

Link: Service Manager Build Document

Even though we had an “expedited” implementation, I wanted to make sure that every configuration we made was captured, so I created a Build Document which also served as guidance for other interested parties as to how the tool has been configured.

I couldn’t find a similar document although there are some useful blogs available, but for our implementation we didn’t set out to customise, only to configure, and most of the blogs talk about use of the Authoring Tool or PowerShell scripts which is something we wanted to avoid.

Requirements were captured as part of the tool selection process, and this document doesn’t replace the need for formal requirement elicitation.  The Build Document can be used at the start of implementation to guide the modification of various Service Manager elements.

It is provided as is with no warranties attached. I hope you find it useful.

Posted in Build | Tagged , , | Leave a comment