Drill Down

hi Guys

I want to allow users to have the ability to drilldown. But it doesn't seem possible outside AS ? am I wrong ? As I have built a cube with drilldown enabled and have tested and is working within AS. However after I've opened it up in excel I and tried to drill down it wasn't actually possible. I could only increase / decrease the report's hierachy and that was all.

Anyone know how I can allow my clients to view drill down values ?

any help is appreciated :)
Tom

If you can't drill down with Excel then you are not using a dimension that has more than one level defined in it. Excel quite happily drills down - just double click on a cell.

Are you confusing drill down with drill through ?

Out of the box Excel does not do drill through. However, it is possible if you use an addin. MS has one called "Excel 2002/2003 Add-in for SQL Server Analysis Services". To download this, goto http://www.microsoft.com/downloads/d...displaylang=en

Thanks for that

Hi:

Due to the number of possibilities for client manifestations, the answers here are legion. However, one answer that would seem to meet your requirement in a "quick and dirty" way lies within Reporting Services (free with a MSSQL Server license, in effect): create a report with drilldown (among other features) like you describe, then export it (you can automate mass exports via data-driven subcriptions, etc.) to Excel XP / 2003 and above. You get a very elegant (especially post-SP2 implementation, for Reporting Services) report in Excel, with pivot-table functionality for the drill down - the little "rheostat" (like a graphic equalizer in appearance) buttons are there and everything!

The only drawback, then, is convincing people that Excel is not the "single version of the truth, "once you give it to them, but that is a wholly separate discussion that lies somewhere within a conversation about (or between) Nebuchadnezzar and Hamlet. (In other words, "Not now, John...")

Have a great day!

Bill

Hi bill

Thanks for that do you know of anyway where i can implement drill through in excel ?

Thanks
Tom

I thought that's what the last two threads were put here to tell you how to do. Am I missing something? When you say you want to "drill down," do you mean in a fixed report with underlying data, or interactively against a cube?

The option Stephen offered is quite good, have you attempted it? Have you attempted the Reporting Services route for a fixed report? Have you even tried Pivot Table Reports (I have an article out there on doing that, also, but you might want to look at MSDN / the Office online help for using it in the current versions of Excel - thought the procedures have changed very little since O2k).

There are more elegant, complicated ways, but you have to at least be able to work with these simple approaches first. And you certainly have to have a "medium" in mind (the app or add-in you intend to use) before you can address how to go about using it.

Try some hands-on and get back to us with specific questions that arise. That way, I'm sure we can be of more efficient assistance.

Good Luck,

Bill

The medium that I am using for the cube is Excel 2003 the problem is I wish to implement drill through as in double clicking on a numeric cell to bring up another window with a list of individual items that cause the result in the sum. (well this is the case in analysis services manager) However I can't seem to perform this task in excel.

I hope that makes it clear ?

Good Morning:

Are you trying to drill DOWN (per the orignal thread) or drill THROUGH? Do you understand the difference?

You mention accessing a cube with Excel 2003: Are you doing this via pivot table / pivot table chart, through the Excel Add-in, or through some other connection / mechanism?

Are you attempting this with a local cube (.cub) or with an Analysis Services cube?

I can possibly help if I know the above - particularly the first question, which will mean significantly different answers. If you don't know the difference,in drilldown and drillthrough, please ask or look it up. I often spend hours of back and forth before in forums, only to find out that a correspondent was tallking about something entirely different that what they thought they were asking for. (This is why I had to give up answering e-mails.)

Please be explicit in answering the above, so we can help you without wasting time most of us don't have anyway...

And have a great day!

Bill

Thanks Bill

I understand the difference between drill down and drill through. What I seek is drill through.

I am connecting to Analysis services via pivot table.

The datasource is Analysis services. NOT local cube. (But I am curious is it possible to implement drill through with analysis services ?)

Tom

Hi!

Did anyone get a resolution on this? I'm having a smiliar problem. I want to know WHY I CAN'T DRILL-DOWN.

This is my problem:

I have a cube that I created in AS2005, I have a hierarchy and drillthrough action defined. When I create a pivot table in Excel based on that cube and I try to drill down on the cell in the pivot table I can not drill down to the next level. I get an error message: Cannot show or hide detail for
this selection. My hierarchy is based on Dimension Facility. Facility State is root level, facility zip is next level. When I'm in AS2005 and browse the cube, I'm able to drill down and drill through and see the hierarchy just fine. (Also, I find interesting that when I create a cube in excel, based on a two relational tables, I'm able to drill down. ) I have the Analsysis Services Add-on installed on my machine, so I have the Cube Analysis Menu item. But I can't seem to even drill-through. So, for now all I want to know is why can't I drill-down (i.e. double clicking on a cell and having the data related "explode" in the spread sheet) like it shows in the article written by William Pearson, titled September 12, 2005
Introduction to MSSQL Server Analysis Services: Reporting Options for Analysis Services Cubes: MS Excel 2003 and More. Any help is greatly appreciated!!

Resolving via Independent Post: "Drillthrough an Excel Pivot Table into Analysis Services." The lasat entry in this post was crossposted in multiple threads. Consolidating in "Resolving via Independent Post: Drillthrough an Excel Pivot Table into Analysis Services"

Thanks for Reading.

Bill