DrillDown Charts

 Drilling down usually refers to navigating a hierarchy of information. It helps in encapsulating or wrapping the data details into levels.

User can expand or collapse the data present in the form of hierarchy according to their usage.

We have seen this thing in our reports in the form of Tabulated data.










SSRS provides us the feature in the form of Toggle that can be used in our tablix to have this drill-down hierarchy.




















That was easy, i mean we have the control available to toggle the visibility of table data.

How about doing the same to charts?

Can we also do the same in our charts in the way. I am sorry, we don't have any Toggle kind of thing available for charts.



So, what's the catch! If you can play with expressions and parameters then you can achieve that.


I tried on achieving the same and came out with a workaround (I will say it a workaround J ) 


Lets start then,


Categories used in my example: Year, Month, Date
Values used in my example: Amount
  • Create a parameter Level 
  • Set the visibility to Hide.
  • Assign Default value to 0
  • Assign Available values to 0, 1, 2
 Note: Parameter Level will be used for conditionally checking the hierarchy shown in the chart.

  •  Create a chart, add a category (I used Year for instance) and a Value (used Amount)
  • I want to show Year --> Month --> Date drill-down structure in my chart so I have used the fields accordingly.
  • In the Category group, I have used the expression, both on the Group On and Label, as
= Switch ( Parameters!Level.Value = 0, Fields!Year.Value,
Parameters!Level.Value = 1, Fields!Month.Value,
Parameters!Level.Value = 2, Fields!Date.Value



  • Then, another important aspect that needs to be added is action on the Bars, so as to drill-down furthur.
  • Add an Action to the Bars, using Go To Report feature.
  • Select the same report, add a parameter Level with value

= Switch ( Parameters!Level.Value = 0, 1,
Parameters!Level.Value = 1,2,
Parameters!Level.Value = 2,0
)
  • Now, the basic structure is done, you can add images and manipulate visual labels etc. based on the requirement.
So, here's the report:




Same can be done to other chart types as well, like the Pie chart.





10 comments:

  1. Good Artical, I think this is drillthrough report

    ReplyDelete
  2. Thanks anonymous, this post focuses on implementing toggle feature in a chart using a workaround.

    ReplyDelete
  3. Thanks Manoj !!!
    Thats true will you elobrate more how yhis toggle can be achived in Chart

    ReplyDelete
  4. Thanks for the reply,
    if you will go through this blog post, you will understand how you can achieve the look and feel of a toggle inside a graph.

    Like in my example shown above,

    you can drill into Time period:
    Year --> Month --> Date

    ReplyDelete
  5. This post is probably where I got the most useful information for my research. Thanks for posting, maybe we can see more on this.
    Are you aware of any other websites on this
    msbi


    ReplyDelete
  6. My partner and i actually enjoy this post and the internet site all in all! Your piece of writing is really plainly composed as well as simply understandable. Your current Blog design is awesome as well! Would be awesome to know where I are able obtain it. Please maintain up the very good job. We all require far more such website owners like you on the net and much fewer spammers. Fantastic mate!
    http://sqlservermasters.com/

    ReplyDelete
  7. Awesome post
    This is such a great resource that you are providing and you give it away for free. I love seeing websites that understand the value of providing a quality resource for free. It?s the old what goes around comes around routine. Did you acquired lots of links and I see lots of trackbacks??
    Hadoop training

    ReplyDelete
  8. My understanding of this chart is this; Each Chart behaves as a filter for itself and all other Charts. In order to select the subset.

    ReplyDelete
  9. SSRS - Charts based drill-down Report
    A picture is worth a thousand words – especially when business is trying to find relationships and understand in their data, which could include thousands or even millions of variables and representation of data visualization through the chart reports become very urgent to take the right action for the business. A chart based drill down report is similar to row drill down report but main difference is that it’s based on the category groups and will provide the additional details of that particular category.
    SSRS - Charts based drill-down Report

    ReplyDelete
  10. That is a creative and innovative idea and it will be of help to many bloggers and social media users. I have tried running the code and it is working perfectly and it has no errors. I love programming and I have been learning different programming languages. Students can now access quality writing services by clicking on Topic on Cross-functional Integration.

    ReplyDelete