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.
- Excel 2010 or later
- PowerPivot add-in for Excel 2010 (http://www.microsoft.com/en-us/bi/powerpivot.aspx) or the built-in add-in in Office 2013 Professional Plus (http://office.microsoft.com/en-us/excel-help/start-powerpivot-in-excel-2013-add-in-HA102837097.aspx)
1. After installing or Enabling PowerPivot, select PowerPivot Tab in Excel and then “PowerPivot Window”
2. Select “File” –> “Get External Data from Database” –> “From Analysis Services or PowerPivot”
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.
4. Select “Design” in the “Table Import Wizard”
5. Select the Cube you want to work with
6. Drag and Drop dimension attributes you want to be part of your PivotTable
7. Drag and Drop measures you want to be part of your PivotTable
8. Add optional filters, for example Incident Status Closed and Resolved
9. Select OK to Close the Designer, and then select Finish to close the “Table Import Wizard”
10. Select a row under “Add Column” and insert the the formula “=Year(<date attibute name>)”. For example “=Year([IncidentDimResolvedDateResolvedDate])”
11. Rename the column with something appropriate
12. Repeat step 10 and 11 replacing Year with Month
13. Check DataType of every imported measure and attribute and change if it is not correct
14. Go back to Excel in the PowerPivot Tab and Select PivotTable
15. Using “PowerPivot Filed List”, drag and drop dimension attributes and measures to rows, columns and values to get the desired layout
16. (Optional) It is possible to add calculated measures and create KPI to extend your PivotTable.
17. To be able to filter data easily by Year and Month, add the previous calculated attributes (year and month) to the Slicers list.
The following is a sample obtained using previous instructions:
Italian version of this post is available here