Call today 0845 643 6463

Tag Archive: Pivot

  • Excel Cube Pivot drillthrough limited to 1000 rows

    March 25th, 2010 in Analysis Services, Blog

    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…

    » Read more

  • Excel 2007 and SSAS 2008 Error

    October 30th, 2009 in Analysis Services, Blog

    I was working on a new SSAS 2008 cube today, and came across an error which Google was unable to help with. I thought I’d post the solution here to help anyone else who may encounter it. The cube in question will be primarily be accessed using Excel 2007, so I’d been dutifully testing it along the way to ensure all was well. And then, after a number of changes the following error appeared when connecting to the cube from Excel to…

    » Read more

  • Extract Datasource and Query from Excel Pivot

    January 23rd, 2008 in Analysis Services, Blog, SQL Server

    Have you ever tried to reverse engineer an Excel pivot table? It’s not as easy as you would think! Whether you just want to find out the datasource details, or identify the query that was used, there is just no simple way of getting Excel to tell you. The macro below will loop through every sheet in a workbook, and will document the datasources, SQL or MDX queries as well as the page, row, column and data fields. To use it, add it into…

    » Read more