Results 1 to 4 of 4

Thread: Drillthrough an Excel Pivot Table into Analysis Services ?

  1. #1
    Join Date
    Oct 2005
    Posts
    1

    Drillthrough an Excel Pivot Table into Analysis Services ?

    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

  2. #2
    Join Date
    Apr 2005
    Posts
    9

    Cool Did you receive resolution to this???

    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.

  3. #3
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607
    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

  4. #4
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607

    Message to "Queen:"

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •