Share via

Populate Excel Table with Custom MDX Query & PivotTable Filters

  • Article

Chris Webb wrote this post: Binding an Excel table to the results of an MDX query.  In a comment at the bottom, Scott Gall suggested using VBA.  I want to take this one step further to allow custom MDX to be filtered by the filters from the Pivot Table Field List, while not actually using a pivot table (similarly to formulas mode).

This provides the following benefits over (or in addition to) pivot tables/formulas mode …

  • Performance.  MDX generated by pivot tables can be up to 10 times slower than custom MDX.
  • Custom calculations that are relevant only to a single report, and therefore don’t warrant being built into the cube/tabular model.  We can tackle this using the OLAP Pivot Table Extensions, but there are still some cases where we might want calculated members on hierarchies, or custom layout of members on axes, etc. for which it would be nice to use custom MDX.
  • Excel formulas on the results.  Formulas mode often works well in this scenario (that’s what it’s for), but
    • Large data sets in formulas mode perform worse than pivot tables, let alone custom MDX.
    • Formulas mode doesn’t work with a variable number of rows depending on filter selection.  Sometimes Excel formulas can be great for things like what-if analysis, etc. in this situation.
  • Multiple charts/grids sharing the same filters.  We can use slicer buttons for this, but we may still be on Excel 2007, or it might just be a report that is better suited to filters than slicer buttons.
  • Endless other customizations.

This Excel file does the above.  Things to note …

  • Don’t get too excited about the code.  It is VBA.
  • It supports multi-value filters.
  • It gets the connection info from an existing connection (Data tab > Connections) called “AdventureWorks”, which you will have to configure.
  • Place custom MDX in the Query tab with the <<Filters>> placeholder in the where clause as per the example.  You can then hide the Query tab from the users.
  • The sample query provided was written against the tabular Adventure Works.  You will have to change it if using the multidimensional Adventure Works or a different database.
  • Check the RefreshButton_Click() macro and change the constants at the top as required.  For example, you will need to change the intFirstReportRow constant if you add more filters.