Sean Boon recently blogged about an approach to implement drilldown on charts with Reporting Services when Analysis Services is used as the data source, and it got me thinking about ways to implement drilldown in Reporting Services in general. There are two standard methods used to do this that are widely known about:
- The first can be described as "fetch all the data you’re ever going to need to display and then hide the stuff that hasn’t been drilled down on yet" – this article describes it well, albeit for SQL data sources. It’s easy to implement but has has big problem: if the amount of data your report could ever possibly display is massive then the report will be very slow to run, for example if your dataset query returns millions of rows.
- The second is more scalable, in that you have multiple reports for each level of granularity you want to display and when you drill down or drill up you click on a member in the Report and pass it as a parameter to another report. This also works well but has a different problem: you now have multiple copies of what is essentially the same report to maintain and keep in synch. This approach can also only display one level of granularity at a time, and sometimes it’s nice to be able to see multiple granularities in the same report.
Wouldn’t it be good to have drilldown capabilities in Reporting Services just like you have in any other Analysis Services client? That’s to say, you’d see a list of members on rows in your report, you’d click on one and then see all its children, then click again and its children would disappear? Well, it is possible and it’s a problem I’ve tackled numerous times myself. The last time was when I was writing the samples for Intelligencia Query, but I’ve just come up with an even better approach which I thought I’d blog about. I’ve implemented it for the standard Analysis Services data source although I’ll be honest it took me a few goes to get it to work properly (there would have been much fewer hacky workarounds if I’d been using Intelligencia Query!) and I’m not sure it’s 100% robust; hopefully someone will find this useful though.
What I’ve done is basically a variation on the second approach above, but instead of using multiple reports I’ve created a single report which calls itself when you drill down on a member. The really tricky part is how you manage the set of members you’ve drilled up and down on, and this is where I’d struggled in the past – the solution I’ve got here uses a hidden parameter to manage that set, which is then passed to the main dataset and used with the DrillDownMember function.
Here are the steps to get it working:
- Create a new Reporting Services report with a data source pointing to Adventure Works.
- Create three new report parameters in this order:
- MemberClicked – tick "Allow Blank Values" and set the default value to [Customer].[Customer Geography].[All Customers]. This parameter will hold the unique name of the member the user clicked on to drill down.
- PreviousDrillDowns – again tick "Allow Blank Values" and set the default value to [Customer].[Customer Geography].[All Customers], and tick "Allow Multiple Values". This parameter will hold the list of members the user drilled down on before the last drill down.
- DrillDowns – again tick "Allow Blank Values" and tick "Allow Multiple Values". This parameter will hold the complete list of members drilled down on for the current report.
- Create a new Dataset in the report called DrillDowns. Use the following MDX for the query:
WITH
MEMBER MEASURES.CUSTUNAME AS
[Customer].[Customer Geography].CURRENTMEMBER.UNIQUENAME
SET DRILLDOWNS AS
UNION({[Customer].[Customer Geography].[All Customers]},
IIF(
//CLICKED MEMBER HAS NO CHILDREN, SO IGNORE IT
ISLEAF(STRTOMEMBER(@MemberClicked)), STRTOSET(@PreviousDrillDowns),
IIF(
COUNT(INTERSECT(
STRTOSET(@PreviousDrillDowns),STRTOSET(@MemberClicked)
))=0,
//DRILL DOWN
UNION(STRTOSET(@PreviousDrillDowns),STRTOSET(@MemberClicked)),
//DRILL UP
EXCEPT(STRTOSET(@PreviousDrillDowns),STRTOSET(@MemberClicked))))
)
SELECT {MEASURES.CUSTUNAME} ON 0,
DRILLDOWNS ON 1
FROM [Adventure Works]What this does is take the set of previously drilled down members, and if the member we’ve just drilled down on is not in there return the set of all previously drilled down members plus the new member (for drilling down); if it is present, return the set of all previously drilled down members except the new member (for drilling up). If the member we’ve clicked on is a leaf member, we can ignore the click and just return the set of all previously drilled down members.
You’ll need to hook up the two parameters @PreviousDrillDowns and @MemberClicked to the report parameters you’ve previously declared. To do this, first of all in the query designer declare the parameters but just fill in the names and a default, such as [Customer].[Customer Geography].[All Customers] (see here, towards the end, for more detailed steps). Then exit the query designer but stay in the Dataset Properties dialog and create two dataset parameters with the names PreviousDrillDowns and MemberClicked and hook them up to the appropriate report parameters.
- Go to the report parameter called DrillDowns and set the default value to be the CUSTUNAME field from the dataset you’ve just created.
- Create a second dataset called DisplayQuery with the following MDX:
WITH
MEMBER MEASURES.CUSTNAME AS
Space([Customer].[Customer Geography].CURRENTMEMBER.LEVEL.ORDINAL) +
[Customer].[Customer Geography].CURRENTMEMBER.NAME
MEMBER MEASURES.CUSTUNAME AS
[Customer].[Customer Geography].CURRENTMEMBER.UNIQUENAME
SELECT {MEASURES.CUSTNAME, MEASURES.CUSTUNAME, [Measures].[Internet Sales Amount] } ON COLUMNS,
DRILLDOWNMEMBER({[Customer].[Customer Geography].[All Customers]},
StrToSet(@DrillDowns, CONSTRAINED), RECURSIVE)
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [Adventure Works]This query simply displays the measure Internet Sales Amount on columns, and on rows uses the DrillDownMember function to drilldown on the All Member on Customer Geography plus any other visible members that are present in the set returned from the DrillDowns parameter.
Once again you’ll have to hook up the @DrillDowns parameter to the DrillDowns report parameter.
- Now, in the report, create a table and bind it to the DisplayQuery dataset. Only use the CUSTNAME field to display the members for the Customer Geography hierarchy on rows – this means you have a single field that can contain members from all levels of the hierarchy.
- Finally, open the Textbox Properties dialog for the cell bound to the CUSTNAME field and set up an Action to jump to the report we’re currently building. We also need to pass two parameters: one that sends the value of the CUSTUNAME field (note this is the unique name of the member clicked on, not the CUSTNAME field which is just the member name) to the MemberClicked parameter, and one that send the value of the DrillDowns parameter to the PreviousDrillDowns parameter. It’s not actually obvious how to pass the values of a multi-value parameter through an Action, but I found the answer here; the expression you’ll need to use for this report is:
=Split(Join(Parameters!DrillDowns.Value, ","),",")
Here’s what you’d expect to see when you first run the report:
Click on Australia and then South Australia and you get this:
Click on Australia again and you’d go back to what’s shown in the first screenshot.
I realise these steps are pretty complex, so I’ve created a sample report in SSRS2008 format and uploaded it to my SkyDrive here:
http://cid-7b84b0f2c239489a.skydrive.live.com/embedrowdetail.aspx/Public/RealDrilldown.zip
I dream of the day when SSRS will do all this stuff for me automatically…
UPDATE: you can now view the sample report online (minus the indenting for members on different levels, for some reason) here –
http://reportsurfer.com/CustomContentRetrieve.aspx?ID=170467
Very nice Chris! I dream of the day to. So where is Microsoft at with getting the Dundas OLAP included? I guess we just need to wait for Gemini and leverage Excel. Incorporating the ProClarity functionality would have been nice.
I\’m sure I heard that Dundas OLAP wasn\’t part of the whole Dundas deal. But I do know that the SSRS team are thinking seriously about their SSAS support… so keep your fingers crossed for the next version! But I\’ve been saying that since 2002.
Hi – thanks for another great post – I\’ve already found a use for this in some work I\’m doing with a customer. Have just booked for your MDX lesson on 27th March, looking forward to it.CheersRich
Hi Chris, have seen http://www.reportsurfer.com? – it allows you to post reporting services reports and people can try them out live on the site before downloading them. They have Adventure Works as one of their databases so if you posted this up there people could try it out live and see how it works.CheersDarren
Hi Chris,THX a lot for that example – i am absolutely sure that i will need that soon… also i will send you an email concerning the project i am in now – hopefully we can work together on some things in the future.Greetz from hatfield in the uk (wjhere i am at the moment)Dietmar
I thought I had read that Dundas OLAP was included on a few blogs. Right now I can only trackdown one of them from Russell Christopher where he mentions the map, calendar, and later on in an Update the OLAP components. http://blogs.msdn.com/bimusings/archive/2007/06/04/microsoft-acquires-dundas-s-data-visualization-components-sql-2008-news-download-ctp3.aspx. Still waiting on the Map components, hopefully Kilimanjaro?Thanks for the great link Darren, that is sweet and very cool!
Dan: Hmm, I did ask about Dundas OLAP and I\’m sure someone told me it wasn\’t included – I can\’t find where though. Possibly it\’s in a comment on one of my blog entries. I\’ll do some digging though.Dietmar: Hatfield is just down the road from me, about 30-40 mins away! I\’d certainly be interested in any work you\’ve got 🙂
This exact issue is one of the biggest problems in my professional life. There\’s a few ways to do it badly, hopefully this be the way to do it properly. If so, there\’s a pint in it for you in Manchester on 28th March."…I dream of the day when SSRS will do all this stuff for me automatically…" Amen to that mate. Absolutely incredible that it doesn\’t do this straight outof the box.
Let me know how you get on, Sam, I\’m always up for free beer…
This is a great technique. If you include the ordinal property in the dimension properties list (DIMENSION PROPERTIES …,LEVEL_NUMBER), you can make the background color depend on the level (Fields!Customer.LevelNumber) to give more visual cues about what is a total and what is detail.
Speaking of drillthrough and SSAS. Have you come across the issue where you\’d like to alias the columns included in SSAS default action drillthroughs? I would like my users to see a bit more friendly column names when they click \’drill to detail\’ in Proclarity or Performance Point. Can one edit those in the MD_Schema rowsets?
It\’s not possible, I\’m afraid Steve – I agree the column names are pretty user-unfriendly, but they can\’t be changed.
Hi,"I dream of the day when SSRS will do all this stuff for me automatically"I´m not dream of the day when SSRS support this stuff, because im thinking its a lack approach. I dream of the day when one integrated BI Frontend supports Reporting, Analyzing, Dashboarding etc…. yes hopefully with kilimanjaro and gemini
I don\’t think there\’s much chance of this happening with Kilimanjaro or Gemini; in fact with Gemini all we\’ll get is yet another MS BI tool to compete with all the others…
This is a great post Chris! I\’ve been looking to update my sample to do something similar, but you beat me to it 🙂
This is a great post Chris! I\’ve been looking to update my sample to do something similar, but you beat me to it 🙂
On Dundas… I asked Alex Gorev in the MSDN chart forums awhile back if OLAP was included. It is….. but I wouldn\’t expect to see it for awhile. Kilimanjaro looks like it might be exciting, but I suspect that it will be pushed back until Mid 2010 because we have yet to see the CTP that was promised for early Q1. While I\’m on the subject I hope they give us a Map control(Preferably for Silverlight, RS, and .NET 4) that can consume and render SQL Spatial Data. It’s stupid to have the ability to store map data in a SQL database if you can\’t easily display it on the latest MS platforms.With that said the Dundas OLAP control isn\’t really that great, its slow and has some limitations. What MS really needs to do is give us a Silverlight PivotTable Control along the lines of the one in Flex. Radar Soft has one of these thats kind of nice, but like Dundas its slow and unlike Dundas somewhat buggy. Right now the lack of a replacement for OWC is a major limiting factor for us in deploying some of our databases in SSAS. We would like to give our users the ability to create their own pivot tables online which they can download to Excel or Print. In my opinion the real problem with Reporting Services for AS work isn\’t the query limitations but rather the inability to allow the user to quickly browse a cube and generate a custom report.
Interesting news re Dundas OLAP – thanks for that! I agree, I think MS desperately needs its own standard query control for Analysis Services.Re Reporting Services/Analysis Services, I think everyone wants the kind of easy report generation and integration with Office that you describe. However I do think that the query limitations are at the bottom of it all: if they didn\’t exist, just about every SSAS client tool would now have the option to publish to SSRS as standard – it\’s such an obvious piece of functionality, and the only reason they don\’t have it is because it\’s not easy to implement.
Chris, I added a suggestion regarding the Dundas OLAP/Standard Query Control to MS Connecthttps://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=426072
Hi Chris, Good to hear from you. This was really interesting (and helpful). Here\’s the URL to my blog post where I describe an alternative mechanism to get the same effect (at least with a parent-child dimension): http://hitachireed.spaces.live.com/blog/cns!ECBD6A0DD8E42605!209.entry. I\’d be interested to hear what you think.Cheers, Reed
Hi… I just wanted to chime in and say that I agree with \’no name\’ that Dundas OLAP is too slow. We want to give our users the ability to create their own reports based on a cube through a webpage and it takes too long to put something together. Can anyone tell me how this was done in the past? ie. If a business user wants to drill down would he be in the same physical location as the CUBES and access them through a windows client over the network?
Great post Chris!!! This fix one of SSRS major lack .Unfortunately now I’ve to face another similar problem: How to implement Drill Down with attributes that comes from different dimensions.In my case I have to build a report that show sales by Customer Name, Family Product and Product (Customer Name doesn’t belong to the Product hierarchy). If I will find a smart solution I’ll share with you; anyway every suggestion will be appreciated!!Italians are not famous for the beer, but if you come to Verona you will find a good bottle of wine from Valpolicella. 🙂
Simone,
did you ever work out how to do drill downs with attributes from different dimensions? I’d like to see an example.
Cheers.
Perfecto!! Muchas gracias!
I so miss ProClarity
Hi,
i have one query, hope so you can help me with it.
I made a report server project in visual studio, which works absolutely fine with all the drill downs working in a proper manner, but when i deployed the same on Share Point 2010, then if i change the values from default to so another i.e. i select only some projects out of many, and accordingly the needed other fields also, and then if i click apply the reports get generated, but then when i am going for the drill down report, the values of the parameter passed is shown as blank, instead of the selected project and other fields.. and no drill down report is generated, whereas if same tried in the visual studio preview tab works absolutely fine. can u suggest me where i may have created problem/error. i have tried redeploying it.. but doesn’t work..
Hmm, sorry – I don’t have a Sharepoint environment that I can test this on at the moment, so I have no idea what might be going on here. Your best bet is to ask at the SSRS MSDN Forum I think.
Chris, In your post you mention when deploying this solution to SSRS you lost the indentation on the hierarchies. I found a hint about why this happens here: http://www.codeproject.com/KB/reporting-services/SSRSaaka3.aspx -> Look at the section titled “Preserving Space in the Reports”. So, I took this concept and brought it over to your example. The first thing I tried was to use a “Replace” function directly in the MDX to replace “spaces” with “alt+0160”. However, MDX does not yet have a built in “Replace” function – at least as of SSRS 2008 R2. So, I finally came up with an alternative. In the DataSet editor add a new calculated field. The field name should be “CUSTNAME2” and the expression should be “=Replace(Fields!CUSTNAME.Value, ” “, ” “)”. NOTE: The second “space” enclosed in double quotes (“”) is actually the space entered by using the “ALT+0162” method on the numeric key pad. Now, go to the report designer and edit the tablix field called “[CUSTNAME]” and edit the placeHolderProperties. Change the “Value” field on the “General” tab to this new field – “[CUSTNAME2]” and keep the label the same. No other changes are required. Redeploy this to the SSRS server and test. The hierarchy indentation now works correctly, the same as it did in the BIDS “review” pane.
Thanks Brian!
Hey Chris, I didn’t see you mention this anywhere, but I did notice after implimenting a similar solution that eventually after drilling through several levels the URL query string will be truncated, which can cause the report to blow up. Just thought I’d mention that in case you hadn’t.
Hi Dustin,
Good point – I’d imagine it’s because, eventually, you hit the maximum length of a url.
Hi Chris
Very informative post. Thanks for sharing your ideas.