Contextual or Correlated Charting

Sometime we need to create charts which are contextual in nature i.e. their data is based on data selected from some other report item like some column field etc. E.g. in the following screen shot if you click on any Category field say "C3" the data for the pie chart would change accordingly.








Designing such report is quiet simple. You just need to create two different datasets one for the table and the other for the chart. Dataset for the chart would be parameterized and the parameter value would be passed from the field you selected in the table (Category like C1, C2 etc. in our case)

Let's come into the implementation part. Follow below steps -

Step 1: Create a data set for the table report item using below query:

SELECT 'C1' AS Category, 4 AS Pass, 6 AS Fail
UNION ALL
SELECT 'C2' AS Category, 5 AS Pass, 5 AS Fail
UNION ALL
SELECT 'C3' AS Category, 6 AS Pass, 4 AS Fail
UNION ALL
SELECT 'C4' AS Category, 7 AS Pass, 3 AS Fail

Step 2: Create another, parameterized dataset using below query:

SELECT Category, Pass, Fail
FROM (SELECT 'C1' AS Category, 4 AS Pass, 6 AS Fail
UNION ALL
SELECT 'C2' AS Category, 5 AS Pass, 5 AS Fail
UNION ALL
SELECT 'C3' AS Category, 6 AS Pass, 4 AS Fail
UNION ALL
SELECT 'C4' AS Category, 7 AS Pass, 3 AS Fail) AS X
WHERE (Category = @CategoryParam)

Step 3: Configure Table using dataset created in step 1 and configure Chart using dataset created in Step 2.

Step 4: Now, the important part, right click the Category text box and configure the Text Box properties as per the below screen shot -

You need to configure Action and set parameter value so that data for pie chart is selected accordingly.

You are done, you can select any of the category and data of pie chart would change accordingly. Isn't this so simple? Please let me know if you have a better way to do this.