Flattening Pivot Tables into Excel 2007 Tables

If you have an MDX query, you can run it directly in Excel outside of a pivot table.

The steps are:

1. Create a pivot table against an OLAP cube

2. Drag in a measure (not calculated) and rows/columns/filters.

3. Select Show Details on the measure.

This brings up an Excel drillthrough table.  Under Table Tools – Design, Connection Properties, you have access to Command Types SQL, table, Default.  Under this is a drillthrough query that can be converted to MDX.

Unfortunately it appears as though it returns key results instead of descriptive names for some columns, but it might be useful.  You can reference a column name in =Table_ExternalData_1[#Headers] so you could write a VLOOKUP to get the caption from another sheet if required.

You can go to Table Design – Summarize with Pivot table, to get a non-OLAP pivot table.

You can add row numbers, remove duplicates, and export the table to a Sharepoint List.

I like using Excel tables, and one of the things that has mildly annoyed me in the past is that you can’t bind the results of an MDX query to an Excel table in the way you can do with a SQL query. I mean, pivot tables are all very well, but sometimes a plain old table is a better fit – for example, think of the cool stuff the data mining addin works can do with tables. Luckily, with a bit of hacking, you can do it… and here’s how.

Chris Webb's BI Blog's Blog - Windows Live

You can do all of the wacky stuff available with Excel 2007 tables.

So now let’s finish our discussion of syntax, since some of you are probably asking questions like “how do I reference the entire table, headers and all?”  Referencing the entire table can be done this way: =Table1[#All].  Or if you just want to reference the headers: =Table1[#Headers].  To reference the entire column: =Table1[[#All], [Column1]].  To reference just the header value of a column: =Table1[[#Headers], [Column1]].  The special keywords can also be combined: =Table1[[#Headers],[#Data],[Column1]].  At this point I think you get the idea. 

http://blogs.msdn.com/excel/archive/2005/10/28/486604.aspx

Now if it didn’t involve hand-coding your own MDX this could be a bit more useful…

Next step would be to publish this to Excel Services in Sharepoint and see if it is refreshable.  You would end up with a live Reporting Services-style flattened rowset report that could be exported to Excel with live connectivity.

With the crummy key captions of course…