by Ian Macdonald , Principal Technologist

Background

One of the ways BI Office is unique in fronting Analysis Services is its ability to create “asymmetric grids”. These are grids where the inner dimension members can be different within the outer dimension members.

Consider the following grid. Countries are shown on rows with quarters shown on the columns. Within each quarter there are three measures.

Grid Showing Three Measures Per Column

We can use the “Eliminate Entire Column” option to remove specific measures from selected quarters.

“Eliminate Entire Column” Option

If we remove Net Profit from the Qtr 2 2009 column the grid becomes asymmetric. We could also use the Multi-Element option to remove multiple inner columns in one action.

Result of Eliminating One Measure from One Column

Remove Multiple Inner Columns Simultaneously

Result of Removing Multiple Inner Columns Simultaneously

This works really well to construct a specific asymmetric grid.

However, if you wish to make the grid dynamic, for example driving the quarter selection via a parameter for use on a storyboard, the eliminations are lost because the columns are eliminated on the combination of the column headers, Qtr 2 2009 / Profit, Qtr 3 2009 / Net Profit, etc. If a year parameter is used to drive the respective quarters, the new grid does not contain 2009 quarters so the eliminated combinations of quarters and measures are not there to be performed.

Result of Using a Parameter

I encountered a real life situation on a recent consulting day with a customer whereby the customer wanted the user to be able to choose a quarter, display three measures for that quarter, then show the previous four quarters but only show a single measure for those quarters.

We cannot use eliminations because the quarter is dynamically driven and the combination of quarters and measures changes. I needed to find a different solution.

Solution

The answer lies, as usual, in Dynamic Sets. In this case, it is useful to think of the quarters as two sets, one containing the selected parameter, the other the previous four quarters, again driven by the parameter.

We can also think of the measures as two sets, the measures to be shown under the selected quarter and the measure to be shown under the previous quarters.

To get two sets of variably driven quarters / measures, we can then cross join the first selected quarter set with the set of three measures, then cross join the set of previous quarters with the single measure set. We can then select the two sets to appear on the columns.

Let’s start with creating our quarter parameter. As we want to show the previous four quarters from the selected quarter, we will restrict the parameter to containing quarters from 2009 and 2010:

Creating a Quarter Parameter

We can use the parameter directly in the grid to place the selected quarter into the columns. We now need to create a set of the previous quarters, using the parameter as the driver. To do this we will need to use the MDX editor. We can create a default custom set from the Analytics Tab. We then need to drag the parameter from the date hierarchy and apply the Lag function to get the quarter four places before the selected parameter (Lag(4)). We do that again to get the quarter immediately preceding the selected quarter (Lag(1)). We can then use the Range operator “:” to generate a set of quarters starting four places behind the selected quarter up to one place behind.

Using the MDX Editor to Create a Set of Previous Quarters

We then need to create our other sets. These will consist of the parameter in one set, the single measure to appear under the previous months in another, and the measures to appear under the selected month in the third. All these can be created using the simple slide-off from the element tree.

Creating a Set of a Single Quarter

Creating a Set of Multi Measures

Creating a Set of a Single Measure

We now have our four sets: previous quarters, single quarter, multi measures, and single measure. Now that the building blocks are in place, we can use the MDX function “Crossjoin” to generate the single month, multi measure set and the multi month, single measure set.

Create a new blank custom set from the Analytics tab. We can either use the explicit CROSSJOIN(<set1>,<set2>) function or the implicit <set1> * <set2> operator. Both give the same results.

Using MDX to Create a Single Month, Multi Measure Set

Using MDX to Create a Multi Month, Single Measure Set

Note that the parent dimension is set as measures. This is important because if the parent dimension is set to Date, an error is generated at runtime indicating that the Measures dimension is used more than once in the query. This is because the set resulting from the cross join contains elements from both the Measures dimension and Month Dates hierarchy. As measures are always used in a query (in either the rows or columns or as a background subselect filter), the query generated by BI Office using this set will have Measures in the rows or columns or subselect and in the rows or columns where the set is being displayed.

We can now select the two sets to be displayed:

Displaying the Selected Sets

Changing the parameter quarters to Qtr 1 2009 allows us to see the sales, profit and net profit for that quarter and just sales for the previous quarters:

Quarters Shown Updates Per Selection