Building a Dynamic MDX Form with Dimension Switching

Building a Dynamic MDX Form with Dimension Switching

This page explains how to make an MDX Form dynamic by using Excel data validation, named ranges, and cell references inside the MDX query — allowing the user to switch between hierarchies from a dropdown without modifying any formulas.

Prerequisites

• An MDX Form already exported to Excel (see Generating MDX Forms with Slice)

• A working sheet named MDXForm from the Listing AnalysisSolution cube

Step 1 – Create a Copy of the Sheet

Duplicate the MDXForm sheet and rename the copy MDXForm2. All edits in this section are made on MDXForm2.

Step 2 – Restructure the Sheet Header Rows

Insert 1 row above the current row 8 to create dedicated cells for each MDXForm argument. The layout should be:

Step 3 – Add a Dimension Dropdown (Data Validation List)

In cell B12, create an Excel Data Validation list containing the hierarchy names you want to switch between. For the Listing AnalysisSolution cube, the ListingSolution dimension has three relevant hierarchies:

• Host

• Neighbourhood

• Room Type

For guidance on creating a data validation list in Excel, see: Microsoft Support – Create drop-down lists

In cell B11, enter the attribute reference:

Use the syntax Dim:Hier:Attribute. Change this to a different attribute if needed.

Step 4 – Replace Hard-Coded Values in MDXForm with Cell References

Update the MDXForm function in its cell to reference B10 (server), B9 (MDX), and B11 (attributes) instead of using hard-coded strings. Use Excel named ranges for best practice — for example, name the relevant cells Server, MDXQuery, and Attributes.

Step 5 – Replace the MDX Query

Replace the auto-generated MDX query in cell B9 with a custom query. The following example returns two measures (price and average price) and displays all listings by Room Type:

After pasting the query, click Rebuild Sheet (ALT+F9) from the Slice ribbon to render the report.

Step 6 – Name Cell B12 and Make the MDX Query Dynamic

1. Select cell B12 and define the named range Dims in the Excel Name Box (top-left, next to the formula bar). Press ENTER.

2. Update the MDX query in cell B9 to replace the hard-coded hierarchy name (Room Type) with the Dims named range using string concatenation:

Result

The dropdown in B12 now controls which hierarchy is shown on rows. Changing the selection from Room Type to Host or Neighbourhood and clicking Rebuild Sheet will update the entire report automatically.