SSRS: Parent-Child (Account) Drill-Down
The Goal: Interactive MDX Drill-Down
In a previous blog post (http://hitachireed.spaces.live.com/blog/cns!ECBD6A0DD8E42605!190.entry), I talked about a simple way to display a Parent-Child dimension without having Reporting Services change the sort order inherent in the MDX query. The down-side of this simple approach is that Reporting Services can’t do its normal drill-down action. Fortunately for me, right about the same time that I was working on the client problem, I found the blog Chris Webb wrote about adding query-time (i.e., “real”) drill-down to a report (http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!4194.entry). His goal was scaleable performance: Why retrieve a huge dataset just so that a user can to drill-down a level or two. My goal is more modest: I just want drill-down to work at all once SSRS doesn’t have a recursive hierarchy to work with. I am also inherently lazy, so I thought Chris’s solution was too much work. So, after way too much work, I came up with an alternative that gives probably 90% of the benefit at maybe 10% of the cost.