locked
PowerPivot Excel 2013 Drill-through rows limited to 1000? RRS feed

  • Question

  • Problem:

    Drill-through data from a PowerPivot pivot table is been limited to the first 1000 rows?

    Scenario:

    I have a PowerPivot data model in Excel and generate a pivot table in a worksheet. When I click on a value cell in the pivot table, as expected, a new sheet is created containing the data that was aggregated to produce the pivot table cell value.

    Question:

    How do I increase the 1000 row limit?

    Investigation so far:

    The consensus on the web seems to be that if you

    1/ click on DATA->CONNECTIONS to show the workbook connections then you should be able to select the PowerPivot Data Model connection called "ThisWorkbookDataModel" and then

    2/ click "Properties" to show the "Connection Properties" dialog.

    3/ On the usage tab you should then be able to increase the maximum returned records by increasing the "OLAP Drill Through" setting.

    Unfortunately, the "Properties" button is greyed out for the "ThisWorkbookDataModel" connection and so the number of rows returned cannot be changed. Screen shot below with the button highlighted in red. I have also included a screen shot of the Properties dialog for one of the standard data connections where the option can be seen but is also greyed out.

    

    Does anyone have a solution to this? We need to return more than 1000 rows on a drill through.

    Excel Version 2013 (15.0.4420.1017) 64 bit

    OS Windows 7 Enterprise SP1 64 bit


    (Twitter | Blog)

    Wednesday, February 27, 2013 9:36 AM

Answers

All replies

  • I've just done a test on Excel 2010 with PowerPivot 11.0.2100.60 and I can set the drill through records ok. Might just be a problem in Excel 2013 - try it on an older version.

    In my test I'm just retrieving sample data from an excel sheet, not SQL, but I assume it wouldn't make any difference. Worth playing with different versions and scenarios to narrow it down...

    Wednesday, February 27, 2013 10:44 AM
  • Indeed, this works fine in Excel 2010 with the addin.

    Hoping someone from MS notices this thread some time soon.


    (Twitter | Blog)

    Thursday, February 28, 2013 1:35 PM
  • Hi SQLSophist,

    I discussed this issue in our internal group. I will let you know if there is anything update about this issue. In addition, you can also submit a feedback at:https://connect.microsoft.com/SQLServer/Feedback 

    Thanks for your understanding.

    Regards,


    Elvis Long
    TechNet Community Support

    Monday, March 4, 2013 9:06 AM
  • Thanks Elvis,

    I raised this issue on the partner support forum, and they sent me a link to the "Introduction to Powerpivot for Excel" tutorial.

    Hope to hear from you soon - if this isn't achievable, we may need to change approach here.


    (Twitter | Blog)

    Monday, March 4, 2013 9:54 AM
  • Connect item here:

    https://connect.microsoft.com/SQLServer/feedback/details/780558/powerpivot-drillthrough-limited-to-1000-rows-in-excel-2013


    (Twitter | Blog)

    Tuesday, March 5, 2013 3:51 PM
  • Gavin, are you looking for a workaround to this, or just to file the bug?

    Thanks!


    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Wednesday, September 18, 2013 6:47 PM
  • Hi Ed,

    There are a couple of workarounds on the Connect item; suspect this behaviour is by design.

    GC


    http://xkcd.com/979/

    Wednesday, September 18, 2013 7:55 PM
  • Still have issue now in 2013, I'm going to assume no one cares that most of us need to be able to change our drill through as we did in 2010.

    Had to downgrade, thanks Microsoft for taking away a great feature.

    Monday, July 18, 2016 8:40 PM
  • That's not nice! lots of people care. It's just Microsoft Support that doesn't.

    One of the workarounds is here:
    https://social.technet.microsoft.com/Forums/ie/en-US/bcb3ba7f-f402-4c40-a21c-bd519c65dbea/powerpivot-excel-2013-drillthrough-rows-limited-to-1000?forum=excel

    ... it's a bit of a hack but it does the job. 

    I'm feeling your pain. Googled for hourzzzz. Read all these threads. They're all dead ends. Microsoft's standard response for this issue: "We're investigating. Here's a link to a page completely unrelated to your query... hope that helps?"


    Monday, August 21, 2017 11:12 AM