Most out of the box reports are designed to answer a specific question at a given point in time. However, business requirements change frequently and limit the effectiveness of out of the box reporting. These changes require the constant tuning and even recreation of standard reports. By using IT Analytics for Microsoft System Center, another option is creating a drill down report that shows a high level summary of key data that also allows the user to click on the report to view detailed information.
In this post, we will show you how to quickly and easily create your own custom drill down report using IT Analytics and SQL Server Report Builder.
For this use case, our reporting requirement is to list the number of machines that require a software update and the percent complaint. We also want the ability to click on the required computers count to see a list the computers that require that software update.
To make this drill down report possible, we will follow a three step process: create an overview report, a details report, and finally the necessary parameters.
Overview Report
Using the steps outlined in a previous post, we create a report that includes the software update, the count of required computers, and the count of installed computers. Lastly, we create a calculated field showing overall compliance for a bulletin.
Here is a preview of the report.
Details Report
For the Details Report, we include the bulletin name, computer name and required computer count:
Here is a preview of the report:
Parameters
To make the drilldown work, we need to add a parameter on both reports to pass the correct value to present the correct information.
On the Patch_L1 report, in the "Required Computer Count" column, we right click to bring up the "Text Box Properties" dialog box:
For now, we leave the default options and only edit the "Actions" section:
In "Actions," we click "Go to Report" and select the "patchdetails" report.
We also add a parameter called "pSoftwareUpdate" and the following formula:
="[Software Update].[Software Update - Bulletin ID].&[" +Fields!Software_Update___Bulletin_ID.Value + "]"
This formula will contain the same values as presented in the report.
Next, in the "patchdetails" report, we will add a new parameter:
On the first page, we will give the parameter a name, title, data type and make the parameter visible.
Next, we will set the available values. We will get the values from a query to the dataset so the values match. The parameter dataset is the same dataset used for the report, the value field is set to "ParameterValue" and the label field is "ParameterCaption." The reason we recommend these settings is to match the data as it exists within IT Analytics.
To maintain consistency, we set the default value to "All." The formula used below is a direct reference to the IT Analytics cube:
Last, we leave the default settings:
Report Review
In the overview report, we now see that our cursor changes when we hover over the "Required Computer Count" number.
Here is the resulting details report:
Summary
Using the power of Report Builder and the available data within IT Analytics, we were able to build out a detailed drilldown report in a very short time without writing any additional code or SQL queries. Our admins are able to present data quickly and easily, while our management team is informed and can navigate the data at their convenience!