none
Excel 2007 OLAP drillthrough: how to change/remove limit of 1000 rows?

    Question

  •  

    We're using Excel 2007 as an OLAP client to query cubes on Analysis Services 2005/2008. I've set up drillthrough actions on the cube. Drillthrough works, but the only problem is that Excel limits the number of drillthrough rows displayed to 1000 rows, which for our purposes is too restrictive. (Excel always accompanies the display of the drillthrough rows with the message "Data returned for xxxxxxx (First 1000 rows).")

     

    Can this this 1000 row limit in Excel for drillthrough be changed/removed?

     

    In Analysis Services, I have set the maximum rows property of each drillthrough action to 50,000. Issuing a DRILLTHROUGH MDX statement without the MAXROWS option returns the full recordset, which may exceed 1000 records, so the 1000 row limitation appears to be imposed by Excel.

     

    Thanks for your thoughts and assistance.

     

     

    Saturday, September 13, 2008 1:57 AM

Answers

  • In the options tab in the ribbon for the PivotTable, click on arrow on the "Change Data Source" button in the ribbon. You will see an option for "Connection Properties". In the Connection Properties dialog there is an option for "OLAP Drill Through" where you can change this setting.

     

    Monday, September 15, 2008 12:01 AM

All replies

  • In the options tab in the ribbon for the PivotTable, click on arrow on the "Change Data Source" button in the ribbon. You will see an option for "Connection Properties". In the Connection Properties dialog there is an option for "OLAP Drill Through" where you can change this setting.

     

    Monday, September 15, 2008 12:01 AM
  • Thank you very much, Darren. I found the "OLAP Drill Through" option on the Usage tab within the Connection Properties dialog, just as you described.

     

    I've visited this dialog frequently, but had always focused on the Definition tab, overlooking the Usage tab. Moreover, my prior efforts searching Excel Help were unsuccessful as I searched for the term "drillthrough" rather than "drill through" as it is called in Excel.

     

    Much thanks for your assistance.

    Monday, September 15, 2008 9:12 PM