How to: Display the Same Data on a Matrix and a Chart (Reporting Services)
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 (Reporting Services),
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.
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
To add a matrix and chart to display the same data
Open a report in design view.
From the Toolbox, add a matrix and a column chart to the body of a report or to a rectangle in a 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.