Once you are ready to add charts to your reports, the next step is to figure out which types of charts make the most sense. This first section details the creation of bar reports in Report Builder 3.0 and outlines the different bar options that are available to you.
Adding a bar chart to a report
When you create a new report, you're presented with a blank design surface similar to the one shown in Figure 1. After you add your data source and dataset, they'll be listed in the Report Data pane, beneath the Data Sources and Datasets nodes, respectively. If your datasets include variables in the query, they'll be added beneath the Parameters node, as described above for the @OrderYear variable.
To add a chart to your report, select the Insert ribbon from the menu bar, click Chart followed by Insert Chart. The cursor will change to a mini-chart icon when you hover over the design surface. You must then click the design surface and drag the dotted rectangle into a shape that reflects the tentative borders for your chart. When you release the mouse button, the Select Chart Type dialog box appears, as shown in Figure 2.
Note: Report Builder 3.0 also includes the Chart Wizard, which walks you through the basic steps for adding a chart. For the purposes of this article, however, we'll stick with the manual approach.
In the Select Chart Type dialog box, you can select your chart from one of eight categories. Don't worry if you select a chart that you later want to change. Report Builder makes it easy to switch chart types.
The first type of chart I'm going to demonstrate is a bar chart. To view the various options, click Bar from the list of chart types. Then double-click the last bar chart in that section — 3-D Clustered Horizontal Cylinder chart.
When you double-click the chart, Report Builder adds it to the area you outlined earlier with the cursor, as shown in Figure 3. Notice the Chart Data pane to the right of the chart. This does not appear until you click within the report to display the pane.
The chart data is the most critical part of the chart to configure. It determines what and how information will be displayed when the chart is viewed. Notice in Figure 3 that the Chart Data pane includes three types of data:
- Values -- Provides the values that will be aggregated on the chart.
- Category Groups -- Specifies how to segment data that is to be grouped.
- Series Groups -- Specifies how to group data that is to be aggregated.
For the majority of charts, you'll want to add at least one field from your dataset to each section. Figure 4 shows the bar chart once I configured its properties. Notice that the Chart Data pane includes a field in each section.
Let's take a look at the chart data one section at a time. In the Values section, I added the SalesAmount field from the dataset. To add a field, click the plus (+) button and select the field. When you add a field to the Values section, it automatically creates a basic expression that aggregates the data. Normally, the expression shows up as a second listing under the field and looks similar to the following:
For the purposes of this bar chart, however, I've modified the basic expression, which is why the following placeholder now appears where the basic expression would be:
To better understand the expression, let's look at the properties for the Values data. To access the properties, right-click the field name (in this case, SalesAmount), and then click Series Properties to open the Series Properties dialog box, shown in Figure 5.
As you can see, the Value field text box includes the expression placeholder. To view the expression, click the expression button (to the right of the text box) to open the Expression dialog box. Within the dialog box, you can create expressions for most of the properties in a chart, including the series value field. The dialog box includes such elements as parameters, fields, variables, operators, and functions -- all of which can be used in your expression. Figure 6 shows the expression that I created for the SalesAmount field in the bar chart.
For this expression, I am simply dividing the aggregated value by 1,000 so the data is easier to read in the chart. That way, the chart will display the sales amounts in thousands rather than the full figures.
If you refer back to Figure 5, you'll see that I also created an expression for the Tooltip property. The property allows you to display data when a user hovers over one of the data points on the chart. I include the following expression so users can see the exact sales amount:
For this expression, I simply formatted the aggregated sales amount into a currency figure so the tooltip is more readable.
I configured no other series properties for the bar chart, so let's move on to the Category Groups section of the Chart Data pane (as shown in Figure 4). In this case, I added the OrderMonth field, which you can verify by looking at the chart's vertical axis. Notice that the sample data now reads "Order Month" for each node on that axis.
Like the series properties, you can modify the category group properties. To access the properties, right-click the OrderMonth field and then click Category Group Properties to open the Category Group Properties dialog box. I only modified properties having to do with sorting, as shown in Figure 7.
I first modified the Column property so that it referenced the MonthNumber field rather than OrderMonth. Otherwise the values would be sorted alphabetically rather than in their calendar sequence. In addition, I modified the Order property. By default, columns are sorted in ascending order (from A to Z). However, because the months will be displayed along the vertical axis, the default sort order would have listed the months from bottom to top. To reverse that order, I changed the Order property to Z to A.
The last section in the Chart Data pane is Series Groups. For my sample bar report, I added the SalesGroup field, which you can verify in the sample data that's shown in the chart's key. I made no modifications to the series group properties. When the data is displayed in the chart, it will be categorized based on the values in this field.
That's all there is to the chart data. As I said, these are the most critical properties to configure. Still, there are other properties that are also important to the chart. For example, I modified the footer information as follows:
[&ReportName] report: [&ExecutionTime]
In addition to the ExecutionTime built-in parameter that's included in the footer by default, I've added the ReportName built-in parameter and text. You can view a list of built-in parameters beneath the Built-in Fields node in the Report Data pane.
I also modified the horizontal axis properties. To modify the properties, right-click the axis, and then click Horizontal Axis Properties to open the Horizontal Axis Properties dialog box, as shown in Figure 8. As you'll recall, I changed the expression for the series field so it provides sales amounts in thousands. I wanted the axis to also reflect these amounts, so I set the Minimum property to 0, the Maximum property to 100, and the Interval property to 100. I based the maximum value on the fact that I was familiar with the data and knew that the totals did not exceed 100,000.
In addition to modifying the horizontal axis properties, I modified the vertical axis properties by setting the Interval property to 1. I did this because I wanted to ensure that all 12 months are displayed in the report. Otherwise, Report Builder 3.0 may include only a subset of values to display, such as every other month.
If you refer back to Figure 4, you'll see that I also modified the axis title properties for the vertical axis. You can tell they've been modified by the expression placeholder. To access the axis title properties, right-click the axis title text box and then click Axis Title Properties. From there you can modify the text or add an expression. I added the following expression:
=Parameters!OrderYear.Value + " Monthly Totals"
This expression retrieves the value from the OrderYear parameter and displays it when the report is rendered. Whatever year the user specifies is displayed in the axis title, as shown in Figure 9. Notice also that the chart's horizontal axis shows the 0 to 1,000 range in increments of 100. In addition, the months are listed from top to bottom on the vertical axis, and the chart key shows the names of the sales groups: Europe, North America, and Pacific.
If you build a report that includes the bar chart as I've described here, you can hover your mouse over each column to view the individual totals, as they're displayed through the tooltip. In addition, you can display results for any years for which there is data by entering a different year as the parameter value and then regenerating the report.
Although the property configurations I've described here apply to the sample bar chart, these concepts can be applied to any chart. So let's take a look at a few other chart types so you have a feel for how they work.
USING CHARTS WITH REPORT BUILDER 3.0
Robert Sheldon is a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. You can find more information at http://www.rhsheldon.com.
This was first published in January 2010