How to: Display the Same Data on a Matrix and a Chart (Report Builder 2.0)
When you want to show the same data in a matrix and a chart, you must set properties on both data regions to specify the same dataset, and also the same expressions for filters, groups, sorts, and data.
Because both data regions will have the same ancestor for data (the report dataset), you can add an interactive sort button to the matrix that, when the user clicks it, changes the sort order for both the matrix and the chart. For more information, see How to: Add Interactive Sort to a Table or Matrix (Report Builder 2.0),
To use the matrix column group values as a legend for the chart, you must specify the colors for the series data on the chart, and then use the same colors as the fill colors for the background of the text boxes in the matrix cell that displays the group values. For more information, see How to: Specify Consistent Colors across Multiple Shape Charts (Report Builder 2.0).
At run-time, your report may appear cluttered if there are too many group values for your group definitions. You might need to filter values, combine groups, or adjust the threshold for the chart to combine groups for you. For more information, see Linking Multiple Data Regions to the Same Dataset (Report Builder 2.0)
To add a matrix and chart to display the same data
Open a report in design view.
From the Insert tab, in the Data Regions group, click Matrix, and then click the report body or in a rectangle in the report body. A matrix is added to the report.
From the Insert tab, in the Data Regions group, click Chart, and then select the chart type. A chart is added to the report.
(Optional) From the Insert tab, in the Report Items group, click Rectangle, and then click the report. A rectangle is added to the report. Drag the matrix and chart from steps 2 and 3 into the rectangle.
By placing multiple data regions in the rectangle container, you help control how the matrix and chart render when you view the report.
In the next few steps, you will choose the same dataset field to display in the matrix and to display in the chart.
From the Report Data pane, drag a numeric dataset field to the Data cell in the matrix.
By default, the aggregate function Sum is used for calculating the group value. If you change the aggregate function in the matrix, you must change in the chart also.
In the matrix, right-click the cell with data, click Text Box Properties, and then click Number. Choose an appropriate format for the dataset field value.
Click OK.
Drag the same dataset field you chose in step 3 to the data fields drop-zone on the chart.
In the chart, right-click the Y axis, click Axis Properties, and then click Number. Choose the same format for the data that you chose in step 4.
Click OK.
In the next few steps, you will set the matrix row group and the chart series group to the same expression, and also set the sort order for the chart series group.
From the Report Data pane, drag the dataset field that you want to group by for matrix rows to the Row Groups pane.
By default, the matrix row group adds a sort expression that is the same as the group expression.
Drag the same dataset field that you used in step 9 to the series fields drop-zone for the chart.
Right-click the group in the series field drop-zone, and then click Series Group Properties.
Click Sorting.
Click Add. A new row appears in the sort expressions grid.
In Sort by, from the drop-down list, choose the dataset field that you chose to group by in step 9.
Click OK.
In the next few steps, you will set the matrix column group and the chart category group to the same expression, and also set the sort order for the chart category group.
From the Report Data pane, drag the dataset field that you want to group by for matrix columns to the Column Groups pane.
By default, the matrix column group adds a sort expression that is the same as the group expression.
Drag the same dataset field that you used in step 16 to the category fields drop-zone for the chart.
Right-click the group in the category fields drop-zone, and then click Category Group Properties.
Click Sorting.
Click Add. A new row appears in the sort expressions grid.
In Sort by, from the drop-down list, choose the dataset field that you chose to group by in step 16.
Click OK.
Preview the result. The matrix row and column groups display the same data as the chart series and category groups.
See Also