Welcome back! In the last part of this series we discussed the actual query needed to extract the data from the TFS relational data warehouse. In this post, we’ll see how to use this query in order to create an actual report.
Choosing a Report Authoring Tool
In order to actually produce our report we need a report authoring tool. This tool should allow us to design our report in a WYSIWYG manner and specify the query for getting the data. We would normally choose from the following options:
- Business Intelligence Development Studio (BIDS) – This tool is available from the SQL Server 2008 installation media. It is a special version of Microsoft Visual Studio 2008 which is tailored for database-oriented projects. As such, it supports the developer-oriented functionality of Visual Studio – managing projects, integrating with source-control systems, etc. BIDS is a good choice if you want to treat your report-authoring endeavors as development projects.
- Report Builder – A freely-available tool from Microsoft that is geared more towards power users who are not developers. It does not have the project-management features that Visual Studio has, so its UI is easier to use. ReportBuilder version 3 is needed for SQL Server 2008 R2 while version 2 is used for earlier versions.
It is important to note, though, that both BIDS and ReportBuilder offer the same capabilities as far as the job-at-hand is concerned – it’s just a question of how much additional functionality you would like your tool to contain. In order to stay as focused as possible, we’ll be using ReportBuilder version 3 for this post.
Creating a New Report
So let’s get going! Launch ReportBuilder from the Start Menu. If you get a Getting Started dialog, just choose Blank Report. Otherwise, you’re good to go.
This is what it looks like on my machine:
On the left we have a pane dealing with the data for this report. In the middle we have the layout editor. Finally, on the right there is the properties window for dealing with specific object properties.
Here are the steps we’ll be taking to create our Bug’s Life report:
1. Add a user parameter
2. Add a data source
3. Add a specific data set
4. Add a table for showing our data
Adding a User Parameter
In this step, we’ll add a user parameter to the report. This means that prior to running the report the user will be able to supply some values to customize the output. In our case, this is the list of bug ID numbers for which we would like to see the state transitions.
1. In the Report Data pane, right-click the Parameters folder and choose Add Parameter…. You should get the Report Parameter Properties Dialog.
2. Change the Name field to read bugIdsParam. For convenience, I gave the report parameter the same name as the parameter name that’s used in the SQL query.
3. Change the Prompt field to read Bug Ids:. This is what will be displayed to the user. Your dialog window should resemble this:
4. There are many options you can set for parameters. For example, you can set the available and/or default values of a parameter from queries or based on the values of other parameters. In our case we have no need for this functionality, so just click the OK button. You should have a new parameter in the Report Data pane:
Adding a Data Source
In this step, we’ll specify what the source for our data is. As you recall, we opted to use the TFS 2010 relational data warehouse, so we can use SQL. We now need to enter this information into the report.
1. In the Report Data pane, right-click the Data Sources folder and choose Add Data Source…. You should get the Data Source Properties dialog.
2. In the dialog, choose the Tfs2010ReportDS data source. This is the relational data warehouse. If it is not available in the list of data sources, click the Browse button and enter the name of your reporting server in the Name field. This will typically be something like http://<TFS Server Name>/ReportServer. Once you connect to the server, the available data sources are typically located directly under the ReportServer folder.
3. For clarity, change the name of the data source from the default DataSource1 to Tfs2010ReportDS. This is especially useful if you’re using both the relational and OLAP data sources and you need to differentiate between them. Your dialog window should resemble this:
4. Click OK. You should now have a new data source in the Report Data pane:
Adding a Data Set
In this step, we’ll add an actual data set – that is, a set of data that we would like to do something with. As mentioned before, data sets can be used for determining the values of parameters. In our case, however, we would like a data set for the much more basic purpose of simply showing it in the report. This means we’ll have to define the data set as containing the query we developed earlier. So let’s do it:
1. In the Report Data pane, click the Tfs2010ReportDS data source (under the Data Sources folder) and choose Add Dataset…. You should get the Dataset Properties dialog.
2. Change the Name field to read dsBugs.
3. Copy the SQL query from the previous post into the Query field. Alternatively, you may click the Query Designer button in order to develop your query directly from ReportBuilder. This is useful when you have need to develop quick and simple queries on-the-spot, but I would recommend that you use SQL Server Management Studio to develop your queries ahead of time. The designer is useful, however, for making modifications and corrections once the initial query is in place. Your dialog window should resemble this:
4. In the left-hand side of the dialog, choose the Parameters item. This will allow us to connect parameters in your dataset (i.e., the SQL query) to the parameters we defined a the report level (i.e., the values that need to come from the user). Note that ReportBuilder has already recognized that the query requires a parameter, and is offering you the chance to connect this parameter at the report level. Choose the value [@bugIdsParams] from the combo box under the Parameter Values column. Your dialog window should resemble this:
5. Click the OK button. You should now have a new data set in the Report Data pane:
Note that you now have access to each field in the original query.
Adding a Table for Showing Data
And now for the fun part! We’ve done all the behind-the-scenes work for connecting our report to user-specified parameters and queries. Let’s now add the dataset to the report!
1. In the ribbon bar at the top of screen, move to the Insert tab.
2. Click the Table ribbon button and choose Table Wizard…. You should now get the table wizard.
3. In the Choose a dataset wizard page, choose the dataset you would like to add to your report. We currently have only one (our dsBugs dataset) so select it and click the Next button.
4. In the Arrange fields wizard page, drag the System_Id field to the Row Groups list box and all other fields to the Values list box. Your wizard page should look like this:
In essence, what we have done is told ReportBuilder that we wish to group our bugs according to their ID number – that is, have multiple lines showing the fields in the Values list box for each field in the Row groups list box.
5. Click the Next Button.
6. In the Choose the layout wizard page, click Next.
7. In the Choose a style wizard page, select the Slate style and click Finish. We will now have a table on the design surface:
Running the Report
We’re ready to run the report for the first time! On the ribbon bar, click the Run button. After a short wait, in which SSRS is processing the report, your screen should resemble the following:
Enter a comma-separated list of bugs and click the View Report button on the right-hand side. You should now see this:
We’ve done it!
You can now use the Design button (that has replaced the Run button) to go back to design mode. Make sure you save your report – we’ll continue working on it next time.
We created a report and have shown it using live data. However, we’re not done. The report works, but we still need to change the title, column names and add a green background whenever the Reason is set to Verified. I’ll explain how to do that next time. See you then!