Friday, June 17, 2011

Generate an MDX query from Microsoft Excel

Writing your own MDX queries can be a painful experience. So most of the time I cheat Smile.

I’ll use Excel to connect to the Analysis Services server and use PivotTables to drag and drop the expected result together. But how can you get from this PivotTable to the corresponding MDX query that is used underneath?

One way to do this is through the OLAP PivotTable Extensions:

OLAP PivotTable Extensions is an Excel 2007 and Excel 2010 add-in which extends the functionality of PivotTables on Analysis Services cubes. The Excel API has certain PivotTable functionality which is not exposed in the UI. OLAP PivotTable Extensions provides an interface for some of this functionality. It also adds some new features like searching cubes, configuring default settings, and filtering to a list in your clipboard.

A screenshot:

AvgTaxMDX.png

1 comment:

bloger7791 said...

Thats great! thanks