MDW Overview Report for Data Collector (Server Activity) – MDW Report Series Part 2

By Bill Ramos, Program Manager, SQL Server Manageability Team

In this post, we’ll complete the MDW Overview report from part 1 of this series and then start into the makings of the Server Activity report as shown below.

The Server Activity report has 4 major sections that I’ll go through that includes the timeline; server thumbnails; SQL Server waits, and SQL Server activity. It’s going to take several blog postings to get though this report.

Adding Hyperlink to Server Activity Upload Time

Back to the completing the MDW Overview. The next step is to add hyperlinks to the collection set upload times that will launch the Server Activity report. The example below shows where I left off in part 1.

Creating the Blank Server Activity Report

The Server Activity report needs two key parameters:

1. ServerName (type Text) – Name of the server name \ instance reporting into the MDW.
2. EndTime (type Date/Time) – Interval End Time (UTC) for the report to display.

There are several other hidden parameters that I’ll get into as we build up the Server Activity report. Go ahead and create a new blank report in the same folder as the mdw_overview.rdl file and add two parameters. While you are at it, go ahead and add the UTCtoLocal function to the Report Code property from part 1 (see below) and then save the report as Server_Activity.rdl.

 Public Shared Function UTCtoLocal(ByVal UTCValue) As String

' Get the local time zone and a base Coordinated Universal 

' Time (UTC).

Dim localZone As TimeZone = TimeZone.CurrentTimeZone

If IsNothing(UTCValue) Or (Not IsDate(UTCValue)) Then

   Return Nothing

Else

   ' Calculate the local time and UTC offset.

   Dim localTime As DateTime = localZone.ToLocalTime(UTCValue)

   Dim localOffset As TimeSpan = localZone.GetUtcOffset(localTime)

   Return localTime.ToString()

End If

End Function

Adding the Hyperlink Action

Now, go back to the mdw_overview report and right click on the Server Activity <<expr>> text box that displays the last download time and select the Text Box Properties… command. You’ll navigate to the Action page and choose the Go to report option, specify the Server_Activity report, and add the two parameters as shown below.

You can now test out the link by running the mdw_overview report within the designer and then click on the Server Activity upload time. You should see blank Server_Activity report with the parameter values as shown below.

At this point, you can save your mdw_overview report and shut it down. In future posts, we’ll hook up Query Statistics and Disk Usage reports.

It’s time to take a break and get this post uploaded. I spend the last week picking apart the Server Activity data sources and report code and it looks like several posts will be needed to go through the report. Tomorrow, I’ll go over the report parameters and get into the details of creating the CPU thumbnail. No attachment in this post.

Technorati Tags: Data Collector,SQL Server 2008,SQL Server 2008 R2,SSMS Reports,MDW