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

How to do it...
We will now create a dashboard-like report that displays the number of incidents created
and resolved today, this week, and this month. Also, we want to display the number of open
and unassigned incidents. Perform the following steps:
1. Start Report Builder.
2. Under New Report , select Blank Report .
3. Under Data Sources , add the newly created ServiceManager data source.
4. Under Datasets , add a dataset named DSCreatedResolved (this is shown in
the next screenshot). Select the Use a dataset embedded in my report option, se-
lect the ServiceManager data source, select Text query type, type the follow-
ing query and click on OK :
DECLARE @DateTime datetime = GETUTCDATE()
DECLARE @StartOfToday datetime = DATEADD(DAY,
DATEDIFF(DAY, '19000101', @DateTime), '19000101')
DECLARE @EndOfToday datetime = DATEADD(DAY,
DATEDIFF(DAY, '19000101', @DateTime) + 1, '19000101')
DECLARE @StartOfWeek datetime = DATEADD(WEEK,
DATEDIFF(WEEK, '19000101', @DateTime), '19000101') - 1
DECLARE @EndOfWeek datetime = DATEADD(WEEK,
DATEDIFF(WEEK, '19000101', @DateTime) + 1, '19000101')
- 1
DECLARE @StartOfMonth datetime = DATEADD(MONTH,
DATEDIFF(MONTH, '19000101', @DateTime), '19000101')
DECLARE @EndOfMonth datetime = DATEADD(MONTH,
DATEDIFF(MONTH, '19000101', @DateTime) + 1, '19000101')
SELECT
ISNULL(SUM(CASE WHEN
CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688
BETWEEN @StartOfToday AND @EndOfToday THEN 1 END),0)
AS CreatedToday,
ISNULL(SUM(CASE WHEN
CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688
BETWEEN @StartOfWeek AND @EndOfWeek THEN 1 END),0) AS
CreatedThisWeek,
ISNULL(SUM(CASE WHEN