0845 643 64 63

Excel Cube Pivot drillthrough limited to 1000 rows

When browsing a cube using Excel 2007, you can drillthrough the measures to display up to 1000 rows of the transaction level source data.

I often get asked whether this limit of 1000 rows is configurable – well the good news is yes it is.

There is an option in the actions tab of the BIDS cube designer which allows you to specify the maximum rows, but helpfully this is ignored by Excel. Instead, you have to set it in Excel when you create a pivot.

Just click “Options” on the “PivotTable Tools” ribon, then in the “Change Data Source” dropdown click on “Connection Properties“. In this screen, just change the “Maximum number of records to retrieve” property.

Excel 2007 Pivot Options

One Response to Excel Cube Pivot drillthrough limited to 1000 rows

  • Pingback: Excel Cube Pivot drillthrough limited to 1000 rows | The Frog-Blog Get Pivot
Power BI Sentinel
The Frog Blog

Team Purple Frog specialise in designing and implementing Microsoft Data Analytics solutions, including Data Warehouses, Cubes, SQL Server, SSIS, ADF, SSAS, Power BI, MDX, DAX, Machine Learning and more.

This is a collection of thoughts, ramblings and ideas that we think would be useful to share.

Authors:

Alex Whittles
(MVP)
Reiss McSporran
Jeet Kainth
Jon Fletcher
Nick Edwards

Data Platform MVP

Power BI Sentinel
Frog Blog Out
twitter
rssicon