Drill through to different reports on clicking of different series in a chart

Requirement: A chart report displays Sales and Revenue. On clicking Sales value, users should be able to drill through to detailed Sales report. Likewise users should be able to view detailed Revenue report on clicking of Revenue series.

Solution:

Step 1: Create Source report with Data Items - Year, Sales, Revenue.

Step 2: Create 2 data Items - FlagA, FlagB for Sales series and Revenue Series.

FlagA - '1'
FlagB - '2'

Step 3: Nest FlagA data item under Sales Series and FlagB data item under Revenue series.

Step 4: Create 1 Target Report, with 2 lists - one for Sales and one for Revenue and include filter on Year to show up the report for the corresponding year passed from the source report.



Step 5: In Query 1 associated with Sales include filter ?FlagA? is not null to force the parameter to show up in the drill through definition. Likewise in Query 2 include filter ?FlagA? is not null.

Step 6: Create a render variable to conditionally display the required list.

case when ?FlagA? is not null then 'A' else 'B' end

Step 7: Apply render variable on the lists to display accordingly.

Step 8: In the source report, set up the drill through link to pass the Year, FlagA and FlagB data item.





Note: The legend displays the value of FlagA and FlagB concatenated with the Sales and Revenue Keyword. In case you wish to avoid this, then you would need to create a manual legend (design outside of the chart).