Year/Month filter and aggregation in an Excel Pivot Table in SCSM 2012


Update: there’s now an alternative method directly from the product group that creates a new dimension in Service Manager SystemCenterWorkItem cube: http://blogs.technet.com/b/servicemanager/archive/2012/12/07/incidents-or-service-requests-sliced-by-months-quarters.aspx

 

At the time of this writing, it seems that is not possible to filter or aggregate workitems by date selecting months and years. For example you cannot easily filter incident created in the last month, you have to select every day one by one to achieve this goal. From googling around it seems that this is a BUG in Service Manager and a Fix will be available soon. The following is a step by step guide to implement a workaround while waiting for the fix.

Requirements :

 

1. After installing or Enabling PowerPivot, select PowerPivot Tab in Excel and then “PowerPivot Window

image

 

2. Select “File” –> “Get External Data from Database” –> “From Analysis Services or PowerPivot” 

image

 

3. Insert the name of the SQL Analisys Server hosting the Service Manager DB and then select the correct database from the drop-down list.

image

 

4. Select “Design” in the “Table Import Wizard

image

 

5. Select the Cube you want to work with

image

 

6. Drag and Drop dimension attributes you want to be part of your PivotTable

image

 

7. Drag and Drop measures you want to be part of your PivotTable

image

 

8. Add optional filters, for example Incident Status Closed and Resolved

image

 

9. Select OK to Close the Designer, and then select Finish to close the “Table Import Wizard”

image

 

10. Select a row under “Add Column” and insert the the formula “=Year(<date attibute name>)”. For  example “=Year([IncidentDimResolvedDateResolvedDate])”

image

 

11. Rename the column with something appropriate

image

 

12. Repeat step 10 and 11 replacing Year with Month

image

 

13. Check DataType of every imported measure and attribute and change if it is not correct

image

 

14. Go back to Excel in the PowerPivot Tab and Select PivotTable

image

 

15. Using “PowerPivot Filed List”, drag and drop dimension attributes and measures to rows, columns and values to get the desired layout

image

 

16. (Optional) It is possible to add calculated measures and create KPI to extend your PivotTable.

image

image

image

 

17. To be able to filter data easily by Year and Month, add the previous calculated attributes (year and month) to the Slicers list.

image

 

 

The following is a sample obtained using previous instructions:

 

image

 

Italian version of this post is available here

– Fabrizio

Advertisements

  1. #1 by Fabrizio Guaitolini on December 11, 2012 - 2:33 pm

  1. NeWay Technologies – Weekly Newsletter #20 – December 6, 2012NeWay | NeWay

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: