How to drillthrough in Excel using Pivot Tables?

Question

Post reply

How to drillthrough in Excel using Pivot Tables?

Tiago Rente

Default port

Points: 1403
More actions
March 23, 2005 at 3:57 am

#152369

I am trying to use the "drill through" functionality in Excel using Pivot Tables.
It works on the Analysis Services and in Excel with Microsoft's Cube Analysis add-in.
Does anyone have any tips/clues on how to do it?
If it means using any VB code, can you send me an example?
Thanks in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

DtAdmnstrtr SSC Rookie Points: 36 More actions · Answer 1

See Microsoft MSDN Library Article "Extending Excel OLAP Functionality"

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_xlextendolap.asp

mhempenstall SSC Veteran Points: 276 More actions · Answer 2

I could be getting totally the wrong idea here but you could try this:

1. Right click on the pivot table and select "Pivot Table Wizard".

2. Click "Options..."

3. Under the "Data options" section check the box for "Enable drill to details".

Think that the VBA for this is "Sheet1.PivotTables(0).EnableDrilldown = True" if that helps?

Eduardo Goicovich SSC Journeyman Points: 89 More actions · Answer 3

Hi:

First, must enable drilltrough in the cube editor:

--menu

/tools

/drilltrough options

Then, grant access a role to the cube

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 4

re: granting access via a role, remember to set the 'Allow drillthrough' checkbox in the role too. ie On the AS side you need to allow drill through i) in the cube as per Eduardos instructions and ii) in the/any roles that will be used to access the cube.

Just out of interest, the drill through in the (Office/Excel) Accellerator for OLAP has worked well for us in the past.

Steve.

Tiago Rente Default port Points: 1403 More actions · Answer 5

Steve,

Regarind the PivotTables options "3. Under the "Data options" section check the box for "Enable drill to details"." is not available, although the cube has drill through enable and my role has access to it. It Analysis Services I can do the drill through.

We have create a Excel add-in that allows us to do the drill through but I am still find a way to do it without the add-in.

--

Can you please send me more details about the Office/Excel Accellerator for OLAP?

For other reasons I am getting to very limits on the PivotTables and I would like to try other solutions.

Thanks.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 6

My 'drill through to details' option is greyed out (tickec/checked, but not available) too.

But, I just did a quick test using the VBA from the site mentioned in someones earlier post and that works a treat. It has a few sections that I had to modify slightly (doing a copy & paste form the web is not always a good idea) but it worked well for me once clened up.

It sounds to me like you've already looked at the Aceellerator, it is the addin that puts the 'Cube Analysis' menu in your toolbars.

If you need it I can post the VBA but it is almost identical to what's found on the site.

Cheers,

Steve.