Driving Grids from an Excel Range

For many standard reports, you will want to use data from the Excel workbook to influence your Grids. Driving dimensions from a range provides a simple way to do this, especially when multiple Grids need to use the same parameters.

If the input to the Grid can contain members that aren't found in the Cube, then the error message that is shown for invalid members can be hidden. See the Grid Properties Display error on invalid members option.

Example

Here’s a simple grid:

DrivingGrid1.png

We will change it to use an Excel range to drive which calendar periods are displayed.

We will set up cell E2 as the Excel range:

DrivingGrid2a.png

Now edit the Calendar Date hierarchy to use E2 as its input. Click on the Excel range button and select E2 as the driving range:

DrivingGrid3.png

The selected range is added to the list of selections:

DrivingGrid4.png

Click OK.

The grid will update to use the new range for the Date Calendar hierarchy.

DrivingGrid5.png

Similarly, we can use an Excel range to drive the grid rows:

DrivingGrid6.png

Now edit the Geography hierarchy to use E2 as its input. Click on the Excel range button and select E2 as the driving range:

DrivingGrid7.png

Click OK.

The grid will update to use the new range for the Geography hierarchy.

DrivingGrid8.png

Additionally, you can change the selection to include children, descendants etc:

DrivingGrid9.png

Let’s select Children:

DrivingGrid10.png

Click OK and the grid will now show all children (states) of the selection in E2, currently United States.

DrivingGrid11.png

Let’s update E2 to be Germany (by typing straight into E2) – the grid now shows all German states:

DrivingGrid12.png

The Excel range can also be across a number of cells:

DrivingGrid13.png

The grid changes automatically because of a grid setting: Grid Properties > Behaviour > Refresh when driving cells change.

If you want to update it manually, uncheck this setting.

You can also use a named range in the same way as an Excel range to drive your grid.

In the screenshot below CountryGroup1 has been set up as a named range. See Named Grid Ranges for more information.

DrivingGrid14.png

Further Exercises

  • Drive a second hierarchy: perhaps the Product hierarchy
  • Drive a second Grid from the same cell, and watch as both Grids change at once
  • Drive the Grid from formulae

Text Resolution Order

As of v2405.1, this is the order that range-driven members are resolved in:

  • If the range is in another Grid, the member is resolved from that Grid
  • If the "Get unique names from formula ranges" is set for the workbook and the range contains an XL3Member formula, use that member
  • If the text is blank use the default member for the hierarchy
  • If the text starts with Mdx: then use those custom members
  • If the member is an XL3Calc* formula, then use that custom member
  • If the text is an Alias use that member
  • Otherwise, we resolve the member by asking the datasource for a member with the cell text as its caption

See Also