Report Builder 2.0 - Creating Reports with Charts and Gauges

Hands-on exercise 6.3: Creating reports with charts and gauges

In this exercise you will be connecting to an Excel spreadsheet with some data and will be creating a simple report. You will also add a chart and gauge data regions to the report to display the data. In authoring the report you will be creating a report using the New Table or Matrix wizard which is supposed to lead to the creation of a table or a matrix according to the documentation. What has been noted is that this wizard can only create a matrix report and if you need a table report you need to start from scratch. However, a workaround has been adopted to use it as is. Again this was another reported bug that was fixed in the final version.

Getting ready

In order to carry out the tasks make sure you have Microsoft Excel installed on the machine and that the Report Server is running.

Follow on

In this exercise you will carry out the following steps:

1. Create a Microsoft Excel Spreadsheet with some data.
2. Create an ODBC DSN to access the data.
3. Create a datasource using this DSN in Report Builder 2.0.
4. Create a dataset based on the data in the Excel file.
5. Design a report to display the data.
6. Create a chart to display the data.
7. Add gauges to the report.

Creating a Microsoft Excel spreadsheet with some data

In order to work with this exercise we will create a simple spreadsheet with data using the MS Excel program. The chart we will be creating will use this spreadsheet.

Open an empty MS Excel file and type in some numbers as shown in the following screenshot. Delete Sheet 2 and Sheet 3 from the file. Save this file after providing a name (herein RptChart.xls).

The file is quite simple and you can just type in the numbers shown or makeup your own data. All data are numbers. The file gets saved to the default folder MyDocuments on the C: drive.

Create an ODBC DSN to access the data

The MS Excel data will be accessed by creating an ODBC DSN. The details of how to do it are listed here:

1. Click on Start | All Programs | Control Panel | Administrative Tools | Data Sources (ODBC) to open the ODBC Data Source Administrator.

2. If the tabbed page is not in User DSN, change the tab to User DSN and click on the Add button.

3. Scroll down and highlight Microsoft Excel Driver (*.xls) [Version 12.00] and click on the Finish button.

This opens the ODBC Microsoft Excel Setup window as shown.

4. Provide a name (Gauges) and a Description. Click on the Select Workbook...; button.

5. Use the controls on the Select Workbook window to locate the file you

saved: the RptChart.xls file. Highlight the file. This will get the file into the Database Name window as shown.

6. Click on the OK button.

This creates the ODBC DSN as shown in the following screenshot. The DSN you created enters the USER DSN folder.

7. Click on the OK button to close the window.