Monday, June 21, 2010

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).
 

7 comments:

Nasreen Banu said...

Hi,
I have one question for you. Can you please let me know more about
Step 3: Nest FlagA data item under Sales Series and FlagB data item under Revenue series.

Zephyr said...

Hi Nasreen, you will have to place Sales and Revenue as Series items and then place FlagA data item under Sales data item and FlagB data item under Revenue data item.

Hope this helps.

Anonymous said...

Hi Zephyr,

You briefly mentioned that if we didn't want concatenated values, we would have to make a manual legend. Could you please explain this process of creating manual legends?

Many thanks

Zephyr said...

Hi, This need to be avoided as far as possible to avoid maintenance issue. This needs to be set up only if you have a static set of values on your chart that will not change dynamically over time.

To set up the legend, create small blocks with width set accordingly. Set background colors to match with your palette colors and then use text items for your values.

Ensure that the order of the values and colors are as the chart would render.

This is applicable only if you have less and static values. Else you would need to keep re-visiting the report every time a new value is displayed in the chart and add it to the legend.

Hence my suggestion would be to avoid this.

Anonymous said...

Hi,

I had a questions :- "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."

Did you mean Likewise in Query 2 include filter ?FlagB? is not null? Why would we set flagA twice?

Thanks,
Niyati

Anonymous said...

Hi Zephyr,

can you suggest me how to drilltrough from different measure of a crosstab to the single target list report.


thankyou..

sakthi said...

Hi Zephyr,

I have a different requirement. I have a chart report and have a drill through to another chart report and a detail list report. I have all the three reports on a dashboard (Page). Now when i click on a bar in the main chart, i want the changes in both the list report and the detail bar chart .
How can i do that ?


Thanks,
Sakthi