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