# Database Discussions > MDX and Analysis Services >  Drillthrough an Excel Pivot Table into Analysis Services ?

## markkeny

Wonder if you could help me ?

I have set up a cube running under MSSQL Server 2000 Analysis services and have created reports in Excel 2002 using pivot tables and linking into the cube as an external data source.

The pivottable works fine and I can slice / dice as usual, the only thing that doesnt work is the drill through into AS.  I receive and error message saying "Cannot show detail data for that selection...".

Now I know that the drill through works, as when I create a report using the Analsyis Services Excel Add Inthe drill through works fine, its just how do I get it working using the pivot tables ?

fyi - the reason I dont want to use the Analsyis Services Excel Add In for all the reports is because I have to deploy this to XX number of users, who wont have admin rights on their machines etc etc....

Is there any VB that I could use to perform this drill through and return the results.....or easier ??


Thanks very much

Mark

----------


## Queen

Hi!!

Did anyone get a resolution on this? I'm having a smiliar problem? 
I would be more than happy to share the resolution on this site if someone, anyone, can help me????? 

Mr. William Pearson, I would be eternally greatful if you can help? It seems to be that a lot of us out here are drowing in this issue!!

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

----------


## Island1

With regard to the initial request, RE:  Drillthrough (not Drilldown, a totally different concept - the reason I bring this up is the two terms are used interchangeably in the same post quite often, and, particularly here, where a "continuation" post redirects the topic from Drillthrough to Drilldown);

For a means of performing drillthrough (you hint at wanting an example in VB, etc.) look at how I do it with DTS (a package of which can be easily saved as code that can be called, used as a template, etc., depending on its need for further customization), as one of two perspectives, at:

http://www.databasejournal.com/featu...le.php/1963091

Another approach to consider is leveraging one of the MDX functions for that purposes - I put one of these to work in a hands -on example with Reporting Services (but it can be done elsewhere if you can extrapolate the concepts) in the following Database Journal article:

http://databasejournal.com/features/...le.php/3552186

Good Luck!

Bill

----------


## Island1

Sir / Madam:

If you would kindly post this to a separate item, entitled "Drilldown," etc., I'll be happy to try to respond.  We're mixing two distinct concepts here, and, by combining a "drilldown" question into an existing "drillthrough" question, we're only going to confuse and frustrate searchers.

Please repost as a new item, and I'll get back as soon as time is available.

Thanks.

Bill

----------

