Analysis Services: Increase Drill-through Rows in Excel 2007
More of a placeholder for me because I keep forgetting. You know (probably) the scenario where you have enabled more than the default 1,000 drill-through rows in your SSAS Cube but Excel is still stubbornly limiting the drill-through to 1,000 rows.
You need to edit the data connection in Excel as per the picture below to increase the default.
Have you encountered a situation where the above solution does not stick? I edit the OLAP Drill Through Max Records value in the connection properties, but it reverts to the 1000 default? This is happening on a specific machine (the client’s machine of course) which is XP and recently upgraded (from Office 2003) to Office 2007 sp2. I ensured the 9.0 OLE DB Provider is installed and being used.
I tried moving an altered connection file from my Win7 machine back to the problem machine, which displayed the increased max record count in the connection properties, but still did not return more than the 1000, and once the connection property window was committed, reverted back to the 1000 max value.
Thanks!
Hi, Sorry, not really set up for rapid response here
I’m afraid I haven’t seen that behaviour, nor can I re-create it. The only thoughts I have are that somehow the user is not able to edit the connection (maybe a local security policy or similar). Does that value change on saving, or does the >1,000 rows never get committed – have you actually been able to return >1,000 rows to Excel on that machine at all, even temporarily?
Does anything change if the user is admin of the box?