PowerPivot drillthrough in Denali CTP3

By | July 13, 2011

One of the subtle changes made to PowerPivot with Denali is that it now supports drill-through actions in Excel. In SQL Server 2008 R2 PowerPivot we get the following message:

In Denali PowerPivot supports drill-trough in Pivottables. Let’s say I have the following Pivottable where I want to see the underlying rows of data that make up this sum of salesamount:

I can double click on the measure and it will open a new sheet in Excel that shows you the underlying rows of data in the fact table:

Notice that the rows are filtered by the values that combine the measure that we clicked on.

By default the drill-through will return only the first 1000 rows, but this is configurable in the connection, go to Excel, Data, Connections:

Select the PowerPivot Data connection, this is the connection that is made to the in memory, internal SSAS engine that runs inside PowerPivot.

Press properties:

In this dialog you can change the number or records you want to retrieve in the Drill through.

  • Bertjan Kaan

    Kasper, om gebruikt te kunnen maken van drillthrough optie via Excel 2010 op SSAS (middels drillthrough action) werkt niet als er meerdere items zijn geselecteerd in een filter/slicer. Is er nu een alternatief? Ik hoop van wel! Of is het echt wachten op de nieuwe release van SQLServer Denali? Ik hoop van niet… Klanten gaan meestal niet direct over op een nieuwe release.

    Waarom werkt drillthrough dan wel als ik middels de browser in SSMS mijn cube benader? Dan werkt drillthrough prima. Ligt het probleem dan niet bij Excel?

  • dontrial

    I want to change the column sequence after I press the drill through, is there any way to do this?

    • Kasper de Jonge

      Hi @dontrail, that is unfortunately not possible.


  • Dale

    The drill through appears to not include columns that are used to relate separate powerpivot tables which is a significant problem for me. Has anyone else seen this?

    (BTW, thanks for posting a great article!)