Functional Reports: Making a Report Into a Management Tool
Introduction
Some of the examples in this article refer to data elements and interfaces specific to the Team Foundation Server. The data warehouse is a relatively standard relational database. The web management tools for TFS are also included in some of the examples. While the examples may be specific to TFS, the techniques used in this article are not and so can be adapted for use with other data sources and management tools.
Overview
SQL Server Reporting is a powerful tool. By knowing the capabilities and taking advantage of them you can change a simple report into a management tool. Why settle for displaying charts and tables when you can provide client-side sorting and links into a management interface so users can not only view but act on the data being presented? Have you ever reviewed a report and had a question about a particular record? You can add dynamic email links to your reports as well to make it easier for your report users to contact the right person to ask.
Discussion
For the purpose of this discussion, we are writing a "Pipeline" report from TFS data that displays a variety of information intended to help manage a work pipeline. The report is organized into 8 primary sections. Some of these sections have sub-sections.
Adding Function to the Report
There are various ways of making a report functional for the report user. They range from visual cues presented in an intuitive fashion to interactivity. Overdoing these features can defeat the whole purpose, making the report functional. The niftiest feature is useless if no one uses it. Target the functionality to meet the target audience's needs.
Process Guidance
A report is not just data presented to an end user. A report is the representation of a process. The criteria we use to create our datasets and filter our charts and tables describe the rules for the underlying process. The layout and sequencing of the information in the report will describe milestones and workflows associated with the process. When you document your report, you document your process. Microsoft calls this "Process Guidance". An organizational wiki can be a great place to store your report process guidance. Be sure to add a prominent link in your report to your process guidance.
Visual Cues
Sometimes a report becomes less useful because the important data is obscured by the surrounding information. In the example "Pipeline" report, our first section is Milestones. This sections displays information, such as start and end dates, for the previous 10 sprints, the current sprint, and the next sprint. The repetitive nature of the data displayed for each sprint can make it difficult for the report user to distinguish one from the other. Furthermore, while the other sprint information provides context and possibly exposes trends, the primary focus should be on the current sprint.
In this section, add alternating background colors to each sprint group to help end users to distinguish where one sprint ends and the next begins. The following expression is added to the table cells' background property using expression builder in order to alternate the background color from group to group:
=IIf(RunningValue(Fields!GroupField.Value,CountDistinct, Nothing) MOD 2 = 1, "Gainsboro", "White")) where GroupField is the RowGroup field (SprintID).
The RunningValue of the CountDistinct aggregate will return a sequentially increasing integer value for each row group. The Mod 2 operation divides that integer by 2 resulting in a return value of zero when the RunningValue is even and 1 when the RunningValue is odd. So for odd group rows, the background color will be gainsboro and for even group rows it will be white.
In addition to helping the user to distinguish between the successive sprints, we want to call attention to the current sprint. This could be done by bolding or italicising the text or a number of other ways. To accent the current sprint with a third background color, maybe a pale green, the background color formula above will be altered as follows:
=IIf(Fields!IterationPath.Value=Parameters!IterationPath.Value,"#D4FFD4",IIf(RunningValue(Fields!GroupField.Value,CountDistinct, Nothing) MOD 2 = 1, "Gainsboro", "White"))
The original expression that alternates background color from group to group is now wrapped in an outer IIf statement. In this example report the current sprint is set in a parameter, IterationPath. Whenever a groups iteration path matches the IterationPath parameter value, it is considered to be the current sprint and the background color of the row group's cells is changed to "#D4FFD4", which is a pale green.
There are numerous ways of providing visual cues and it is easy to overdo it. Keep the report clean.
Report Navigation
Long reports can be difficult to use if you don't provide an easy means to navigate the report. One such tool is a menu bar with links to bookmarks embedded in the report. There are two parts to setting up report navigation using bookmarks.
- First create the bookmarks. In this example, the report has 8 sections. Some of these sections may have hundreds of records in them which could result in a multipage report. To add a book mark to any report item (table, text box, etc.), select the item. In the "Other" section of the Property pane (in Categorized view) type in the desired name into the bookmark property or select a value from the dropdown list. Expression builder allows the bookmark to be dynamically created from a formula.
- With a book mark created for each element, you can create the navigation bar. This can be a set of text boxes or a table or… whatever works. If a table, matrix or list is used, it must have an assigned dataset, whether you use it or not. Add the appropriate text to each text box/cell. This should be descriptive to the report's target users. It can be static text or dynamically created using expression builder, just like the bookmarks created earlier. Add an action by right-clicking the textbox/cell, selecting textbox properties, then selecting the Action tab. Set the action to "Go to bookmark". Use the same value here as you set for the bookmark name. This process is repeated for each of the 8 sections. Placing the menu bar in the page header ensures it remains available from any page of your report.
- You may also wish to add a "Return to Top" link to return the user back to the menu bar. "Return to Top" links should also use the Bookmarking technique described above.
Interactive Sorting
In a table, matrix, or list, right-click the column header and choose Text Box Properties. Select the Interactive Sorting tab from the left side of the Text Box Properties dialog. Now toggle the "Enable interactive sorting on this text box" checkbox on. Select a Sort by field or function, click OK and you have turned on interactive sorting on that column of data.
Linking
The pipeline report contains numerous references to TFS work items. To allow the report user to manage the work items that are being reported on, we will create hyperlinks anywhere that the work item id is displayed in the report. It is typically pretty simple to provide deep links into Team Web Access, the management interface for TFS, to edit a work item. All that is needed is the web address for the TFS project and the System_Id of the desired work item. Right-click the table cell you wish to execute the link from and choose Text Box Properties. Select the Action tab from the left column of the dialog box. Under "Enable as an action:" choose Go to URL. Click the function (fx) button next to the Select URL dropdown list and enter a formula like:
="http://TFSServerName:8080/tfs/" + Fields!CollectionName.Value + "/" + Fields!ProjectNodeName.Value + "/_workItems#_a=edit&id=" + CStr(Fields!System_Id.Value)"
This assumes that the fields CollectionName, ProjectNodeName, and System_Id are contained in your dataset. Note that since System_Id is a numeric field and we are concatenating it to a string, it must be converted to a string. Another thing worth noting is that using the Action property does not affect the presentation of the data in the cell. Users expect a visual cue to tell them if a block of text is a hyperlink. Blue, underlined text is traditional but whatever format you choose, use the same for all hyperlinks in the report.
Context-Sensitive Email Links
Creating a context-sensitive email link is similar to work item linking. It all comes down to knowing how to put together the url string. You will again use the Action property of the text box and set it to go to a URL. Email links can take a little extra work however. Many of the pipeline report dataset queries include the Assigned To or Changed By name (LastName, First MI) but not the email. Some datasets join to the DimPerson table in the TFS warehouse to retrieve the email address but this may not always be the case. You can reconstruct the email address from the name, however. Whichever method you use, you must begin the email "URL" with "mailto:" Since the formula is much simpler when you already have the email address, I will discuss the latter approach here but the same URL syntax is used for both. You can use the below formula to build an email address from an active directory name:
="MailTo:"+Replace(Trim(Split(Fields!System_AssignedTo.Value,",")(1))," ", "_")+"_"+(Split(Fields!System_AssignedTo.Value,",")(0))+"@Domain.com"
This formula splits the name string at the comma and uses the second element of the resulting array, with all spaces replaced by "_", as the first part of the email address. It appends the first element of the array to the end and adds "@Dell.com". The result is that "Last, First MI" becomes "First_MI_Last@Domain.com", a valid email address. You can get more creative by adding querystring parameters to construct the subject or even the body of the email:
+"?Subject=Status Update Needed for "+Fields!System_WorkItemType.Value+" ("+CStr(Fields!System_Id.Value)+")"
The above addition will add a subject line that looks like "Status Update Needed for Team Backlog Item (101111)". To add some default text to the email body, construct the text in a similar manner used to construct other pieces of the email URL and start this piece with "&body=" to have the trailing string inserted into the email body.
Summary
This is only the tip of the iceberg. There are many other enhancements that can greatly improve the value and usefulness of a report. Use your imagination to please your customers.