Stacking Dimensions and Switching MDX Queries

Stacking Dimensions and Switching MDX Queries

This page extends the dynamic MDX Form built in the previous section by adding a lookup table of MDX queries — enabling the user to switch between entirely different query structures (not just different hierarchies) from a single dropdown.

Prerequisites

• A working MDXForm2 sheet with dimension switching configured (see Building a Dynamic MDX Form with Dimension Switching)

Step 1 – Create a Copy of the Sheet

Duplicate MDXForm2 and rename the copy MDXForm3.

Step 2 – Build the MDX Query Lookup Table

Insert 5 rows above row 1. In cells A1 to A6, create a list of report options:

In the corresponding B cells (B1, B2, B3), paste the MDX queries for Host, Neighbourhood, and Room Type — using the same dynamic query format built in the previous section (with the Dims reference). The B4, B5, and B6 entries will be built in the next step (see Creating On-the-Fly Calculated Members in MDX Forms).

Step 3 – Replace the MDX Query Cell with a VLOOKUP

The MDX query cell (previously B15 — adjust for your sheet layout) should no longer hold the query directly. Instead, replace its content with a VLOOKUP that retrieves the correct query from the lookup table:

This formula looks up the value selected in the Dims dropdown and returns the matching MDX query from column B.

Step 4 – Update the Data Validation Dropdown

Update the data validation list in the Dims cell to include all six items from the lookup table (A1:A6):

• Host

• Neighbourhood

• Room Type

• Room Type, Neighbourhood & All Measures

• Room Type, Neighbourhood & Measure

• TOP 10 Listings

Step 5 – Test the Report

Select each of the first three options from the Dims dropdown (Host, Neighbourhood, Room Type) and click Rebuild Sheet (ALT+F9) after each selection to confirm the report updates correctly.

How This Pattern Works

This pattern makes it trivial to add new report variants — just add a new row to the lookup table and a new MDX query in column B.