Adding an Image to the Report

Adding an Image to the Report

You may want to add a static image to the report, for example, a company or department logo.

To add an image to the report

1.   Open the Toolbox, click the Image item, and drag it onto the design area. The Image Properties dialog box appears. 2.   In the Image Properties dialog box, provide a name for the image (which will not be displayed), and an optional tooltip (displayed when the users hovers the pointer over the image). The Select the image source box has the following three options: ·      External: For images that exist on the report server or another external source. If you select this option, you must provide a URL to the image location in the Use this image field. If the image is changed at its source, the image in the report will change dynamically. ·      Embedded: For images that do not have to be changed. Click the Import button and browse to the image location. The image is embedded in the report and will not change dynamically. ·      Database: For images stored in the database. Provide the path of the image in the Use this field box. In the Use this MIME type box, select the appropriate MIME type for the image. 3.   The Size, Visibility, Action, and Border tabs in the Image Properties dialog box enable you to change the appearance of the image in the report, and also enable you to set the image as a link to another report or URL.

For more information about how to use images in reports, see Images in SQL Server Books Online (http://go.microsoft.com/fwlink/?LinkID=207938)

Adding an Additional Dataset to the Report

You may want to add another set of data to the same report, using a different dataset. This lets you consolidate reports into a single document for ease of reference.

To add an additional dataset to the report

1.   In the Report Data window, click New, and then click Dataset. The Dataset Properties dialog box appears. 2.   In the Name field, enter a name for the dataset or use the default. 3.   In the Data source field, select the data source, usually DataWarehouseMain. 4.   In the Query field, enter the query. This can  either a query created earlier or click the Query Designer button to create a query. When you have finished, click OK. 5.   A second dataset will appear in the Report Data window. From the Toolbox, select a Table, Matrix, Chart, or Gauge, depending on how you want to represent the data. Drag the selected control onto the design surface, and a new data item will appear in the report. 6.   From the Report Data window, drag the desired data fields onto the Table, Matrix, Chart, or Gauge tool, as appropriate.

Changing the Data in the Report

Changing the Data in the Report

To change the data that’s displayed in the report, you must change the query.

To change the query

1.   In the Report Data tree, right-click the dataset, by default this is DataSet1, and then select Query. This starts the Query Designer. 2.   Within the Query Designer, click the Edit As Text button to edit the query directly in the window. Click the Import… button to use a query that you saved previously. The ! button runs the query and displays the results in the lower pane of the Query Designer. If the report does not show the data that you expected, you can revise the query and test it without recreating the report.

Changing the Appearance of the Report

Within the report dataset, you can change the rows, columns, or individual cells. When you click any cell in the dataset, borders will appear around the table. You can click the border to select a row or a column. To select a specific cell in the table, click in that cell. The properties for the row, column, or cell you selected are displayed in the Properties window. By default, this window is located at the lower-right corner of the interface.

To change the name of a column, click in the column heading and edit the text in the text box. You can also change the font, background color, text color, and many other properties of the text box with the Properties window. Drag the borders of the column to change the column’s size. Reorder the columns by dragging them to the new desired position.

To enable sorting in table columns

1.   Right-click the column to which you want to add sorting, and click Text Box Properties. The Text Box Properties window opens. 2.   Click the Interactive Sorting tab. Select the check box Enable interactive sorting on this text box. You can select to sort by detail rows or by groups. Select the column to sort by, and then click OK.

Note: If you want the report to sort on a particular field by default, add an ORDER BY clause to the query.

Adding a Chart to the Report

Instead of, or in addition to, the table or matrix you may want to include a chart in the report for a visual representation of the data.

To add a chart to the report

1.   You may need to expand the report area to provide sufficient room for the chart by using the resize handles. 2.   Click View, and then click Toolbox. The toolbox dialog box opens; you may want to pin the toolbox in place. In the toolbox, click Chart, and then click in the report to place the chart, or drag to size the chart in the report space. 3.   The Select Chart Type dialog box appears, giving you various chart choices. Select the type of chart you want to add, and then click OK. The chart appears on the design surface. 4.   Drag the chart in the report to move it or use the resize controls to resize it. 5.   Click the area labeled Axis Title to change the text on the axis. 6.   To add data fields to the report, select the Report Data pane (click View, and then Report Data if the Report Data pane is no longer visible), and expand the dataset to show the data fields the query retrieves. On the design surface, click the chart that you added. This reveals three areas around the border of the chart as follows: a.   Drop data fields here: Drag data fields here that you want to represent as values in the chart (for example, the height of the bars in a bar graph). b.   Drop category fields here: Drag data fields here for which you want to present the values (for example, the items on the x-axis in a bar graph). c.   Drop series fields here: These fields are optional. Drag data fields here to add an extra dimension to the chart (for example, if a bar graph has multiple data fields for a single category field). 7.   With the chart selected, the Properties window lets you define the properties of the chart itself, such as the colors, borders, and size.

Using a Report Builder

After you have a query that retrieves the data that you need, you are ready to create a report. There are several tools that can be used to create a report: Microsoft Business Intelligence Development Studio (BIDS), Microsoft Visual Studio, Report Builder, or Microsoft Excel. This guide uses BIDS because it is already installed with Microsoft SQL Server and integrates seamlessly with Microsoft SQL Server Reporting Services (SSRS).

Creating a New Report Project

The easiest way to create a new report is to use the report wizard, which creates a simple report and populates it with a basic data display that you select. You can also create a report server project, which creates a blank report project that you then populate with a report and the data that you want to display.

To create a new report project by using the report wizard

1.   Open BIDS, click File, click New, and then click Project. 2.   In the New Project window, in the Project Types pane, make sure that Business Intelligence Projects is selected. In the Templates pane, select Report Server Project Wizard. Enter a name for the project, and then click OK. 3.   The Report Wizard starts. On the welcome page, click Next. 4.   On the Select the Data Source page, enter DataWarehouseMain as the name, leave the Type as Microsoft SQL Server, and make sure that you select the Make this a shared data source check box. Click the Edit button. 5.   The Connection Properties dialog box appears. In the Server Name field, enter the name of the server. In the Log on to the server box, enter the appropriate credentials, as discussed in Setting up the Environment. In the Connect to a database box, select the Select or enter a database name option. In the drop-down box, select the OperationsManagerDW database, and then click OK. The wizard will reappear with the Connection String box filled in. Click Next. 6.   The Design the Query page of the wizard appears. In the Query string box, if you have created the query already, as described in Creating Custom Queries, paste the query into the Query String field. If you have not yet created a query, click the Query Builder button to start the Query Designer. 7.   Within the Query Designer, the default view is the graphical query designer. For more information about how to design a query visually, see the guide to Query Designer (http://go.microsoft.com/fwlink/?LinkID=207899). Click the Edit As Text button to edit the query directly in the window. Click the Import… button to use a query that you saved previously. The ! button runs the query and displays the results in the lower pane of the Query Designer. If the report does not show the data that you expected, you can revise the query and test it without recreating the report. After you have created the query, click OK. 8.   You are returned to the Design the Query page of the wizard. Click Next. 9.   On the Select the Report Type page of the wizard, select Tabular or Matrix. A tabular report returns columns; a matrix report uses an intersection of columns and rows for a more precise view of the data. Click Next. 10.  On the Design the Table page of the wizard, select how you want the data in the table to be organized. The options are as follows: ·      The Page option indicates the fields that appear at the page level of the report. ·      The Group option indicates the fields by which to group the data in the table. ·      The Details option indicates the fields that are displayed in the details section of the table. To change the order of the fields in any group, select a field, and then click the up button or the down button. You can also accept the defaults by clicking Next. 11.  On the Choose the Table Style page of the wizard there is a list of several styles that affect color and layout choices. Select the visual style you prefer for the table. Click Next. 12.  On the Choose the Deployment Location page of the wizard, select where the report will be deployed. By default, the URL for the Operations Manager reporting server is entered. However, you should confirm that this URL is correct. Enter a name for the Deployment folder or accept the default. 13.  On the Completing the Wizard page, enter a name for the report. This will be displayed on the report page. You can review the report summary data on this page. If you want to adjust any of the settings, click the Back button and make the appropriate changes. If you select the Preview report check box, the editor will open in the Preview tab instead of the Design tab. Click Finish to continue. 14.  The basic layout of the report appears in the main window of BIDS. You will see the following two tabs: ·      The Design tab, which shows the layout of the report with the fields returned from the query in place ·      The Preview tab, which shows what the data looks like in the report

To create a report without using the report wizard

1.   Open BIDS, click File, and then click New Project. 2.   In the New Project window, in the Project Types pane, make sure that Business Intelligence Projects is selected. In the Templates pane, select Report Server Project. Enter a name for the project, and then click OK. 3.   The project is created, but still shows the Start page as there is no report in the project yet. In the menu bar, click Project, and then click Add New Item. The Add New Item dialog box appears. In the Templates pane, select Report, and then click Add. The design surface appears in the main window with the Design tab selected, which shows a blank report. 4.   You must add a data source and a dataset to your report in order to display any data. In the Report Data pane, click New, and then click Data Source. The Data Source Properties dialog box appears. Provide a name for the data source (DataWarehouseMain is recommended) or accept the default. If you have not created a shared data source, select Embedded connection; for the Type, select Microsoft SQL Server, and for the Connection string enter the connection information for the OperationsManagerDW database. If you created a shared data source previously, as discussed in Setting up the Environment, select Use shared data source reference, and then select the shared data source in the box. Click OK. 5.   In the Report Data pane, click New, and then click Dataset. Enter a name for the dataset or use the default. Select the Data source, usually DataWarehouseMain, and then enter the query. This can either a query created earlier, or click the Query Designer button and create the query using the Graphical Query Designer. When you have finished, click OK. 6.   The dataset appears in the Report Data window with the available dataset fields retrieved by the query displayed below the dataset name. 7.   Click View, and then click Toolbox to display the Toolbox dialog box. The Toolbox contains several standard items that can be used in reports. From the Toolbox, select a Table, Matrix, Chart, or Gauge, depending on how you want to represent the data, and drag it onto the design surface of the report. From the Report Data window, drag the desired fields onto the Table, Matrix, Chart, or Gauge tool, as appropriate. 8.   Check the appearance of the report by clicking the Preview tab.

The Performance Report

The Performance Report

The Performance report is similar to the Availability report in that it retrieves data for a specified time period on specific entities and retrieves the information from various views. The purpose of the Performance report is to report on the change of a value over time.

The query retrieves some basic information from the vPerfDaily view, which, in this query, has the alias of vPerf:

SELECT  

vPerf.DateTime,

vPerf.SampleCount,

vPerf.AverageValue,

vPerf.MinValue,

vPerf.MaxValue,

vPerf.StandardDeviation,

The query also retrieves the performance rules located in the vPerformanceRuleInstance view:

vPerformanceRuleInstance.RuleRowId,

vPerformanceRuleInstance.InstanceName,

As in the other queries, data is retrieved from the vManagedEntity view, in particular the ManagedEntityRowId, although the GUID, default name, and path are also retrieved:

vManagedEntity.ManagedEntityRowId,

vManagedEntity.ManagedEntityGuid,

vManagedEntity.ManagedEntityDefaultName,

vManagedEntity.Path,

As in the Availability query, the query retrieves a display name if it exists:

ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName)

AS DisplayName,

To display management group information, the query accesses the vManagementGroup view:

vManagementGroup.ManagementGroupGuid,

vManagementGroup.ManagementGroupDefaultName,

The query also accesses the vRule and vPerformanceRule views:

vRule.RuleGuid,

ISNULL(vDisplayStringRule.Name,vRule.RuleDefaultName) AS RuleDisplayName,

vPerformanceRule.MultiInstanceInd,

The query also retrieves group information from the temporary table #OptionList:

OptionList.[Group],

OptionList.GroupTitle,

OptionList.Position,

OptionList.ChartScale,

OptionList.ChartType,

OptionList.ChartColor,

OptionList.OptionXml,

vManagedEntityTypeImage.Image

The FROM clause in the Performance query is more complex than in the Alert or Availability queries. It begins with the information retrieved from vPerfDaily:

FROM Perf.vPerfDaily as vPerf

The query joins that to the performance rule view, using PerformanceRuleInstanceRowId as the foreign key:

INNER JOIN   vPerformanceRuleInstance

ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId

The query then joins that to the vManagedEntity view, again using ManagedEntityRowId as the key:

INNER JOIN   vManagedEntity

ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRow

Once joined to the vManagedEntity view, the query joins to the vManagedEntityType view to retrieve the type for the managed entity:

INNER JOIN   vManagedEntityType

ON vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId

The query also joins to the vManagementGroup view using the ManagementGroupRowId as the key:

INNER JOIN   vManagementGroup

ON vManagementGroup.ManagementGroupRowId = vManagedEntity.ManagementGroupRowId

To retrieve performance information, the query needs to join the vRule and vPerformanceRule views to the vPerformanceRuleInstance view using the RuleRowId as the key in each case.

INNER JOIN   vRule

ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId

INNER JOIN   vPerformanceRule

ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId

The query needs to join to the #InstanceList:

INNER JOIN   #InstanceList AS InstanceList

ON (vPerformanceRuleInstance.PerformanceRuleInstanceRowId = InstanceList.PerformanceRuleInstanceRowId)

The query needs to join to the #OptionList:

INNER JOIN   #OptionList As OptionList

ON (InstanceList.Position = OptionList.Position)

Finally, the query joins the ObjectList to the InstanceList and to the vManagedEntity view using the ManagedEntityRowId as the key:

INNER JOIN   #ObjectList AS ObjectList

ON (InstanceList.Position = ObjectList.Position)

AND (vManagedEntity.ManagedEntityRowId = ObjectList.ManagedEntityRowId)

The query then retrieves the image to use for the entity type:

LEFT OUTER JOIN  vManagedEntityTypeImage

ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityTypeImage.ManagedEntityTypeRowId

AND  vManagedEntityTypeImage.ImageCategory = N'u16x16Icon'

Finally, the query joins the vDisplayString and vDisplayStringRule views to retrieve the necessary display strings for the report. The @LanguageCode is a parameter specified by the report designer.

LEFT OUTER JOIN   vDisplayString

ON vManagedEntityType.ManagedEntityTypeGuid = vDisplayString.ElementGuid

AND vDisplayString.LanguageCode = @LanguageCode

LEFT OUTER JOIN  vDisplayString vDisplayStringRule

ON vRule.RuleGuid = vDisplayStringRule.ElementGuid

AND vDisplayStringRule.LanguageCode = @LanguageCode

The WHERE clause is similar to the one in the Availability report. It retrieves all the data that falls between the two dates specified in the date picker:

WHERE   (vPerf.DateTime >= DATEADD(hh, DATEPART(hh, @StartDate), convert(varchar(8), @StartDate, 112)))

AND (vPerf.DateTime < DATEADD(hh, DATEPART(hh, @EndDate), convert(varchar(8), @EndDate, 112)))

The important points to note in this query are that the ManagedEntityRowId is a useful foreign key, and the format of the WHERE clause is similar to that used in the Availability report.

Here’s what the complete query looks like

SELECT  

vPerf.DateTime,

vPerf.SampleCount,

vPerf.AverageValue,

vPerf.MinValue,

vPerf.MaxValue,

vPerf.StandardDeviation,

vPerformanceRuleInstance.RuleRowId,

vPerformanceRuleInstance.InstanceName,

vManagedEntity.ManagedEntityRowId,

vManagedEntity.ManagedEntityGuid,

vManagedEntity.ManagedEntityDefaultName,

vManagedEntity.Path,

ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName)

AS DisplayName,

vManagementGroup.ManagementGroupGuid,

vManagementGroup.ManagementGroupDefaultName,

vRule.RuleGuid,

ISNULL(vDisplayStringRule.Name,vRule.RuleDefaultName) AS RuleDisplayName,

vPerformanceRule.MultiInstanceInd,

OptionList.[Group],

OptionList.GroupTitle,

OptionList.Position,

OptionList.ChartScale,

OptionList.ChartType,

OptionList.ChartColor,

OptionList.OptionXml,

vManagedEntityTypeImage.Image

FROM     Perf.vPerfDaily as vPerf

INNER JOIN   vPerformanceRuleInstance

ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId

INNER JOIN   vManagedEntity

ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId

INNER JOIN   vManagedEntityType

ON vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId

INNER JOIN   vManagementGroup

ON vManagementGroup.ManagementGroupRowId = vManagedEntity.ManagementGroupRowId

INNER JOIN   vRule

ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId

INNER JOIN   vPerformanceRule

ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId

INNER JOIN   #InstanceList AS InstanceList

ON (vPerformanceRuleInstance.PerformanceRuleInstanceRowId = InstanceList.PerformanceRuleInstanceRowId)

INNER JOIN   #OptionList As OptionList

ON (InstanceList.Position = OptionList.Position)

INNER JOIN   #ObjectList AS ObjectList

ON (InstanceList.Position = ObjectList.Position)

AND (vManagedEntity.ManagedEntityRowId = ObjectList.ManagedEntityRowId)

LEFT OUTER JOIN  vManagedEntityTypeImage

ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityTypeImage.ManagedEntityTypeRowId

AND  vManagedEntityTypeImage.ImageCategory = N'u16x16Icon'

LEFT OUTER JOIN   vDisplayString

ON vManagedEntityType.ManagedEntityTypeGuid = vDisplayString.ElementGuid

AND vDisplayString.LanguageCode = @LanguageCode

LEFT OUTER JOIN  vDisplayString vDisplayStringRule

ON vRule.RuleGuid = vDisplayStringRule.ElementGuid

AND vDisplayStringRule.LanguageCode = @LanguageCode

WHERE   (vPerf.DateTime >= DATEADD(hh, DATEPART(hh, @StartDate), convert(varchar(8), @StartDate, 112)))

AND (vPerf.DateTime < DATEADD(hh, DATEPART(hh, @EndDate), convert(varchar(8), @EndDate, 112)))

The Availability Report

The Availability Report

In contrast to the Alert report, the Availability report draws information from several different views. The Availability report shows the time in any given state (Red, Yellow, Green, White, Disabled, Planned Maintenance, and Unplanned Maintenance) for the monitored entities.

This report returns availability data, which is a state condition, so the information is in the vState views. The query starts off by selecting the amount of time in each state:

SELECTvState.InRedStateMilliseconds,

vState.InYellowStateMilliseconds,

vState.InGreenStateMilliseconds,

vState.InWhiteStateMilliseconds,

vState.InDisabledStateMilliseconds,

vState.InPlannedMaintenanceMilliseconds,

vState.InUnplannedMaintentanceMilliseconds,

vState.HealthServiceUnavailableMilliseconds

The report also needs to show which entities the information is associated with. Therefore, the query retrieves the name, ID, and path for the managed entities in the report. The ManagedEntityRowId is again important because it’s used as a foreign key later.

vManagedEntity.ManagedEntityRowId,

vManagedEntity.ManagedEntityDefaultName,

vManagedEntity.ManagedEntityGuid,

vManagedEntity.Path,

The query also retrieves the type of the managed entity by retrieving the GUID for that entity:

vManagedEntityType.ManagedEntityTypeGuid,

The GUID for the managed entity type is enough to identify it but the report also displays the name for that type. If the management pack author did not include a display name, this field will not be available so the stored procedure uses an ISNULL statement to retrieve the name if it is available. If not, it retrieves the default name instead:

ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName) AS DisplayName

The Availability report also displays an icon for each managed entity in the table so the query retrieves that image also:

vManagedEntityTypeImage.Image

In addition to the managed entity, the query also retrieves the monitor data:

vManagedEntityMonitor.ManagedEntityMonitorRowId,

The query also retrieves the display name for the monitor, just as it did for the managed entity. If the name isn’t available, the ISNULL statement substitutes the default monitor name.

ISNULL(vDisplayStringMonitor.Name,vMonitor.MonitorDefaultName) AS MonitorDisplayName,

The following two items are related to entity monitor dependencies:

vManagedEntityMonitorDependency.ParentManagedEntityRowId,

vManagedEntityMonitorDependency.[Level],

If the entities being monitoring are selected by Management Group, the query retrieves the ID and name of the group also:

vManagementGroup.ManagementGroupGuid,

vManagementGroup.ManagementGroupDefaultName

The SELECT clause retrieves all the data for display in the report.  The next step is to determine where to select the data from. This information is with state data, which means you have a choice between vStateDailyFull and vStateHourlyFull. The Microsoft Generic Report Library uses vStateDailyFull:

FROM vStateDailyFull as vState

The query joins the vManagedEntityMonitor view to the state view in order to retrieve the monitors for the managed entities. The ManagedEntityRowId is the foreign key used:

INNER JOIN vManagedEntityMonitor ON

vState.ManagedEntityMonitorRowId = vManagedEntityMonitor.ManagedEntityRowId

The query also joins the vManagedEntity view. This connects the specified monitors to the managed entities, again using ManagedEntityRowId as the foreign key:

INNER JOIN vManagedEntity ON

vManagedEntityMonitor.ManagedEntityRowID = vManagedEntity.ManagedEntityRowId

The query then joins the vManagedEntityType view using ManagedEntityTypeRowId as the foreign key:

INNER JOIN vManagedEntityType ON

vManagedEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId

Next, the query joins the vMonitor view to the state data. MonitorRowId is the key:

INNER JOIN vMonitor ON vState.MonitorRowId = vMonitor.MonitorRowId

The query also joins the dependencies:

INNER JOIN #ManagedEntityMonitorDependency AS vManagedEntityMonitorDependency ON

vState.ManagedEntityMonitorRowId = vManagedEntityMonitorDependency.ChildManagedEntityMonitorRowId

Finally, the query joins the management groups to their managed entities using the ManagementGroupRowId as the foreign key:

INNER JOIN vManagementGroup ON

vManagedEntity.ManagementGroupRowId = vManagementGroup.ManagementGroupRowId

The query then retrieves the image to use for the entity type:

LEFT OUTER JOIN  vManagedEntityTypeImage

ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityTypeImage.ManagedEntityTypeRowId

AND  vManagedEntityTypeImage.ImageCategory = N'u16x16Icon'

The last few joins retrieve the display strings for the managed entity and the monitor:

LEFT OUTER JOIN vDisplayString ON

vManagedEntityType.ManagedEntityTypeGuid = vDisplaySTring.ElementGuid AND

vDisplayString.LanguageCode = @LanguageCode

LEFT OUTER JOIN vDisplayString vDisplayStringMonitor ON

vMonitor.MonitorGuid = vDisplayStringMonitor.ElementGuid AND

vDisplayStringMonitor.LanguageCode = @LanguageCode

The WHERE clause is the next part of the query:

WHERE (vState.DateTime >= DATEADD(hh, DATEPART(hh, @StartDate) + 1, convert (varchar(8), @StartDate, 112))) AND

(vState.DateTime < DATEADD (hh, DATEPART(hh, @EndDate) +1, convert(varchar(8) @EndDate, 112))) AND

(vState.Date BETWEEN DATEADD(day, -1, @StartDate) AND DATEADD(day, 1, @EndDate))

This clause retrieves all data that falls between the two dates that the report user specified in the date picker. @StartDate and @EndDate are the parameters from the date picker.

1.   DATEPART(hh, @StartDate) returns the hour part of the @StartDate variable. This value is increased by 1 to return the next full hour after the time specified in @StartDate.

2.   convert(varchar(8), @StartDate, 112) converts the @StartDate parameter to character data (varchar(8)) in the format yyyymmdd; the 112 specifies that format.

3.   DATEADD(hh, DATEPART(hh, @StartDate) + 1, convert (varchar(8), @StartDate, 112)) adds the specified hour to the specified date, in yyyymmdd format, rounded up to the next hour.

The effect of the entire WHERE clause is to specify the vState data collected between the specified hours. This WHERE clause can be useful in many different reports, and is used throughout the Microsoft Generic Report Library.

Unlike the Alert Report, the Availability report uses information from several different views, most of which can be joined on the ManagedEntityRowId. Also note how the vManagedEntityType and vMonitor views are joined if you want to include that information in your custom report.

Here’s what the complete query looks like:

SELECT

vState.DateTime,

vState.InRedStateMilliseconds,

vState.InYellowStateMilliseconds,

vState.InGreenStateMilliseconds,

vState.InWhiteStateMilliseconds,

vState.InDisabledStateMilliseconds,

vState.InPlannedMaintenanceMilliseconds,

vState.InUnplannedMaintentanceMilliseconds,

vState.HealthServiceUnavailableMilliseconds

vState.IntervalDurationMilliseconds,

vManagedEntity.ManagedEntityRowId,

vManagedEntityMonitor.ManagedEntityMonitorRowId,

vManagedEntityTypeImage.Image,

vManagedEntity.ManagedEntityDefaultName,

vManagedEntityType.ManagedEntityTypeGuid,

vManagedEntityMonitorDependency.ParentManagedEntityRowId,

vManagedEntityMonitorDependency.[Level],

ISNULL(vDisplayStringMonitor.Name,vMonitor.MonitorDefaultName) AS MonitorDisplayName,

vManagedEntity.ManagedEntityGuid,

vManagedEntity.Path,

ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName) AS DisplayName

vManagementGroup.ManagementGroupGuid,

vManagementGroup.ManagementGroupDefaultName

FROM vStateDailyFull as vState

INNER JOIN vManagedEntityMonitor ON

vState.ManagedEntityMonitorRowId = vManagedEntityMonitor.ManagedEntityRowId

INNER JOIN vManagedEntity ON

vManagedEntityMonitor.ManagedEntityRowID = vManagedEntity.ManagedEntityRowId

INNER JOIN vManagedEntityType ON

vManagedEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId

INNER JOIN vMonitor ON

vState.MonitorRowId = vMonitor.MonitorRowId

INNER JOIN #ManagedEntityMonitorDependency AS vManagedEntityMonitorDependency ON

vState.ManagedEntityMonitorRowId = vManagedEntityMonitorDependency.ChildManagedEntityMonitorRowId

INNER JOIN vManagementGroup ON

vManagedEntity.ManagementGroupRowId = vManagementGroup.ManagementGroupRowId

LEFT OUTER JOIN vManagedEntityTypeImage ON

vManagedEntity.ManagedEntityTypeRowId = vManagedEntityTypeImage.ManagedEntityTypeRowId AND

vManagedEntityTypeImage.ImageCategory = N’u16x16Icon’

LEFT OUTER JOIN vDisplayString ON

vManagedEntityType.ManagedEntityTypeGuid = vDisplaySTring.ElementGuid AND

vDisplayString.LanguageCode = @LanguageCode

LEFT OUTER JOIN vDisplayString vDisplayStringMonitor ON

vMonitor.MonitorGuid = vDisplayStringMonitor.ElementGuid AND

vDisplayStringMonitor.LanguageCode = @LanguageCode

WHERE (vState.DateTime >= DATEADD(hh, DATEPART(hh, @StartDate) + 1, convert (varchar(8), @StartDate, 112))) AND

(vState.DateTime < DATEADD (hh, DATEPART(hh, @EndDate) +1, convert(varchar(8) @EndDate, 112))) AND

(vState.Date BETWEEN DATEADD(day, -1, @StartDate) AND DATEADD(day, 1, @EndDate))