Creating Reports for System Center Service Manager and Orchestrator - Microsoft System Center Reporting - page 307

How to do it...
In this recipe, we will create a report that shows a chart of the number of incidents created
over time. We will also create a report that lists the computers that we have in our Service
Manager CMDB.
Creating a Work Item report
Perform the following steps:
1. Start Report Builder.
2. Under New Report , select Chart Wizard .
3. Select Create a dataset and click on Next .
4. Select the DWDataMart data source connection. If it is not in the list, click on
Browse… to browse to the data source in SQL Server Reporting Services. Click
on Next .
5. If prompted for data source credentials, select Use the current Windows user and
then click on OK .
6. Click on Edit as Text and type the following SQL query in the query window:
SELECT
CONVERT(varchar(7), CreatedDate, 126) AS
MonthCreated,
COUNT(*) AS IncidentCount
FROM
IncidentDimvw
WHERE
IsDeleted = 0
GROUP BY
CONVERT(varchar(7), CreatedDate, 126)
ORDER BY
1
7. Click on Next , select the Line chart type, and then click on Next .
8. Drag the MonthCreated field and drop it in the Categories area.
9. Drag the IncidentCount field and drop it in the Values area.
10. Click on Next , choose the desired chart style, and then click on Finish .