Stay Classy, With System Center Ramblings from San Diego

Blog by Antoni Hanus for Useful System Center Ops Mgr and System Center Service Mgr info. This is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified in the Terms of Use.

How to create an Incident Dashboard using Excel in System Center 2012 SP1 - Service Manager (SCSM) 2012

How to create an Incident Dashboard using Excel in System Center 2012 SP1 - Service Manager (SCSM) 2012

  • Comments 8
  • Likes

This blog walks through the steps needed to create a dashboard in Excel that looks like this:

clip_image001

HIGH-LEVEL STEPS:

1) From the Service Manager Console in the data Warehouse pane and the Cubes node, click on a cube and select ‘Analyze Cube in Excel’

2) Create multiple sheets in the excel workbook – each one with a PivotTable and chart

3) Create a new sheet in the excel workbook and copy and paste each of your charts from the sheets you created them on to the new ‘dashboard’ sheet

4) Publish by either storing it in a Service Manager ‘Analysis Library’ so it can be accessed via others by the console, or publish to SharePoint.

NOTE: You may need to add users (preferably via an AD group membership) to the SCDW_Report_Readers Role in SQL Analysis Services, and grant the role access to the cubes and data sources as described here so users have permissions to open up the report and refresh it:

DETAILED STEPS:

1) In the Service Manager console, navigate to Data Warehouse>Cubes and click Service Manager Work Items Cube, then click on ‘Analyze Cube in Excel’

2A) In the first excel sheet, create the ‘Active Incidents by Classification’ Report:

a) Under the PivotTable Fields pane on the right-hand side, Change the Show Fields dropdown on the right-hand side to IncidentDim:

 

image

b) Check ‘Incidents Active’

image
c) Scroll down to IncidentDim_IncidentClassification, expand ‘More Fields and check ‘IncidentClassificationValue’:

image
d) Click on the Data ribbon and then click the properties button.  Ensure ‘Refresh data when opening the file’ is selected.

image
e) Click on the Pivot Table and then in the Ribbon click PivotTable Tools>ANALYZE and the PivotChart button

image
f) Choose a bar type chart.

image

How the areas in the bottom right should be populated:
Filters – Blank
Legend – Blank
Rows  - IncidentClassificationValue
Values – Incidents Active

so it looks like this:

 

image

 

2B) In a new Excel Sheet, Create the ‘All Incidents by Status’ report

a) Create a new worksheet and then click Insert, then choose PivotTable on the far left of the ribbon
b) Check the ‘Use an external data source’ radio button and then click ‘choose connection’
c) Choose the Connection that has an icon with a cube on it and click open.

image


d) This will open up a new pivot table so you can create a second table selection and chart.
e) Under the PivotTable Fields pane on the right-hand side, Change the Show Fields dropdown on the right-hand side to IncidentDim
f) Check ‘IncidentDimCount’ (This is the total number of incidents in the Warehouse)
g) Scroll down to IncidentDim_IncidentStatus, expand ‘More Fields and check ‘IncidentStatusValue
h) Click on the Data ribbon and then click the properties button.  Ensure ‘Refresh data when opening the file’ is selected.
i) Click on the Pivot table and then in the Ribbon click PivotChartTools>analyze and the PivotChart button
j) Choose a Pie Chart.

How the areas in the bottom right should be populated:
Filters – Blank
Legend – Blank
Axis  - IncidentStatusValue
Values – IncidentDimCount

image

 

2C) In a new Excel Sheet, Create the ‘Incident SLA Status’ report

a) Create a new worksheet and then click Insert, then choose PivotTable on the far left of the ribbon
b) Check the ‘Use an external data source’ radio button and then click ‘choose connection’
c) Choose the Connection that has an icon with a cube on it and click open.
d) This will open up a new pivot table so you can create a second table selection and chart.
e) Under the PivotTable Fields pane on the right-hand side, Change the Show Fields dropdown on the right-hand side to Incident SLA Instance Information
f) Check ‘IncidentSLAInstanceInformation’ at the top
g) Scroll down to SLAInstanceStatus, expand ‘More Fields and check ‘SLAInstanceStatusValue
h) Click on the Data ribbon and then click the properties button.  Ensure ‘Refresh data when opening the file’ is selected.
i) Click on the Pivot table and then in the Ribbon click PivotChartTools>analyze and the PivotChart button
j) Choose a Pie chart .

How the areas in the bottom right should be populated:
Filters – Blank
Legend– Blank
Rows- SLAInstanceStatusValue
Values – IncidentSLAInstanceInformation

 

image

2D) In a new Excel Sheet, Create the ‘All Incidents by Source’ report

a) Create a new worksheet and then click Insert, then choose PivotTable on the far left of the ribbon
b) Check the ‘Use an external data source’ radio button and then click ‘choose connection’
c) Choose the Connection that has an icon with a cube on it and click open.
d) This will open up a new pivot table so you can create a second table selection and chart
e) Under the PivotTable Fields pane on the right-hand side, Change the Show Fields dropdown on the right-hand side to IncidentDim
f) Check ‘IncidentDimCount’ (This is the total number of incidents in the Warehouse)
g) Scroll down to IncidentDim_IncidentSource, expand ‘More Fields and check ‘IncidentSourceValue’
h) Click on the Data ribbon and then click the properties button.  Ensure ‘Refresh data when opening the file’ is selected.
i) Click on the Pivot table and then in the Ribbon click PivotChartTools>analyze and the PivotChart button
j) Choose a Pie chart.

How the areas in the bottom right should be populated:
Filters – Blank
Rows– Blank
Axis  - IncidentSourceValue
Values – IncidentDimCount

 

image

 

2E) In a new Excel Sheet, Create the ‘Active Incidents by Support group’ report

a) Create a new worksheet and then click Insert, then choose PivotTable on the far left of the ribbon
b) Check the ‘Use an external data source’ radio button and then click ‘choose connection’
c) Choose the Connection that has an icon with a cube on it and click open.
d) This will open up a new pivot table so you can create a second table selection and chart
e) Under the PivotTable Fields pane on the right-hand side, Change the Show Fields dropdown on the right-hand side to IncidentDim
f) Check ‘Incidents Active ’ (This is the total number of Active incidents in the Warehouse)
g) Scroll down to IncidentDim_IncidentTierQueues, expand ‘More Fields and check ‘IncidentTierQueuesValue
h) Click on the Data ribbon and then click the properties button.  Ensure ‘Refresh data when opening the file’ is selected.
i) Click on the Pivot table and then in the Ribbon click PivotChartTools>analyze and the PivotChart button
j) Choose a Pie chart.

How the areas in the bottom right should be populated:
Filters – Blank
Legend – Blank
Rows  - IncidentTierQueuesValue
Values – Incidents Active

image

2F) In a new Excel Sheet, Create the ‘Incidents by resolved Date’ report

a) Create a new worksheet and then click Insert, then choose PivotTable on the far left of the ribbon
b) Check the ‘Use an external data source’ radio button and then click ‘choose connection’
c) Choose the Connection that has an icon with a cube on it and click open.
d) This will open up a new pivot table so you can create a second table selection and chart
e) Under the PivotTable Fields pane on the right-hand side, Change the Show Fields dropdown on the right-hand side to IncidentDim
f) Check ‘IncidentDimCount’ (This is the total number of incidents in the Warehouse)
g) Scroll down to IncidentDim, expand ‘More Fields and check ‘Resolved date’
h) Click on the Data ribbon and then click the properties button.  Ensure ‘Refresh data when opening the file’ is selected.
i) Click on the Pivot table and then in the Ribbon click PivotChartTools>analyze and the PivotChart button
j) Choose a Line

how the areas in the bottom right should be populated:
Filters – blank
Legend – blank
Rows  - Resolved Date
Values – IncidentDimCount

 

image

2G) In a new Excel Sheet, Create the ‘Incidents By Created Date’ report

a) Create a new worksheet and then click Insert, then choose PivotTable on the far left of the ribbon
b) Check the ‘Use an external data source’ radio button and then click ‘choose connection’
c) Choose the Connection that has an icon with a cube on it and click open.
d) This will open up a new pivot table so you can create a second table selection and chart
e) Under the PivotTable Fields pane on the right-hand side, Change the Show Fields dropdown on the right-hand side to IncidentDim
f) Check ‘IncidentDimCount’ (This is the total number of incidents in the Warehouse)
g) Scroll down to IncidentDim, expand ‘More Fields and check ‘Created date’
h) Click on the Data ribbon and then click the properties button.  Ensure ‘Refresh data when opening the file’ is selected.
i) Click on the Pivot table and then in the Ribbon click PivotChartTools>analyze and the PivotChart button
j) Choose a Line

 

How the areas in the bottom right should be populated:
Filters – blank
Legend – blank
Rows  - Created Date
Values – IncidentDimCount

image

3) On a new Excel Sheet, Create the dashboard

a) Create a new worksheet and rename it at the bottom left to ‘dashboard’
b) Copy and paste your charts form the other sheets to this worksheet.
c) Click the ‘View’ ribbon and uncheck formula bar, gridlines, and headings.

image
d) Click the ‘insert’ ribbon and choose textbox, and type a title such as Incident Dashboard at the top.

image

4) Publish by either storing it in a Service Manager ‘Analysis Library’ so it can be accessed via others by the console, or publish to SharePoint.

To use the Analysis Library, Create a share where you want to store reports and then specify this share in the Service Manager console underneath data Warehouse>Analysis Library:

image

Ensure that the Users who will be opening up reports on this share have access to it .

REPEATED NOTE: You may need to add users (preferably via an AD group membership) to the SCDW_Report_Readers and grant the role access to the cubes and data sources as described here so they have permissions to open up the report and refresh it:

 

ADDITIONAL REFERENCES:

This is a great blog post that talks about publishing to SharePoint using Excel web services: http://blog.scsmfaq.ch/2012/02/06/data-cubes-in-service-manager-2012-part-23-publishing-reports/

MMS 2013 - Advanced Real World System Center Data Warehouse and Reporting: http://channel9.msdn.com/Events/MMS/2013/SD-B316

Service Manager 2012: System Center Data Warehouse, Reporting, and Dashboards http://channel9.msdn.com/Events/MMS/2012/SD-B312

This wiki page contains lots of good links too: http://social.technet.microsoft.com/wiki/contents/articles/15608.system-center-service-manager-data-warehouse-and-reporting.aspx

Creating Reports Using Performance Point: http://blog.concurrency.com/infrastructure/creating-scsm-2012-reports-using-performancepoint/

Comments
  • This was a great post.

    I'd be very interested to see other step by step reporting examples like this in the future for both OLAP and SQL.

  • Really great post. But I am struggeling to get for example a filter enabled for the last two reports. For example to show only the last 90 days. Hope you can help there ...

    Thanks

    markus

  • Thanks Markus, please take a look at this, and I suggest you try the MP referenced here to see if it meets your needs: blogs.technet.com/.../incidents-or-service-requests-sliced-by-months-quarters.aspx Cheers, Antoni

  • Good Info. I'm wondering if there is a way to grant someone access to Analyze Cubes in Excel without giving them access to the rest of the Data Warehouse pane

  • Jesse, you need to give perms in SQL analysis services as described in the blog post I link to in the note above.  This will not give permissions to the rest of reporting Cheers, Antoni

  • Thanks Antoni, great post.  What I am finding though is that it is showing me all Incidents that have been in a particular status at some point in their life,  not those that are in that status now.  Am I doing something wrong or do I need to do something different to get that information?

  • Thank you for sharing this it got me started!

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment