Advanced Row and Column selections

Named sets

A named set is a Multidimensional Expressions (MDX) expression that returns a set of dimension members. These can be created at cube level or within an application such as XLCubed. You can create named sets by combining cube data, arithmetic operators, numbers, and functions.

The report above shows Reseller Sales Amount across All Geographies for fiscal years 2003 and 2004.

We would like to create a named set, Europe which consists of France, Germany and United Kingdom.

Create a calculated set, Europe, as below.

Double-click on Geography hierarchy and Edit Hierarchy:

Click on Advanced Tab and then Member Set (last but one on right in window).

The following window will be displayed and you will see the defined named set.

Select the named set and click OK – you can now choose to include or exclude this named set in your report.

Between and Manual MDX

In v6.5 two new options are now available, Between and Manual MDX.

The screenshot below shows a simple grid displaying a measure across Products and Calendar Years 2002 – 2004.

Double-clicking on the Date.Calendar hierarchy allows us to edit the hierarchy – click on Advanced tab and you will see window as below

Click on Member Set and the following window will be displayed:

Between

This option allows the user to report for a particular range on that hierarchy. For example, we can choose to report all data between 2002 and 2004 as below:

There is also an option to enter an Excel range as below – we will run the grid report based on values in $I$3 and $I$4. The start range value is in $I$3 and the last range value is $I$4.

Another feature of using the range option is that you can choose to leave one of the ranges blank.

Value in $I$3	Value in $I$4	What will be reported
CY2002	CY2004	Grid report will report data starting CY 2002 through to CY 2004
	CY2004	Grid report will report data starting from earliest date through to CY 2004
CY2003		Grid report will report data from CY 2003 through to latest date

Manual MDX

This option allows you to use MDX code – click on the ellipse to the right of the Manual MDX radio button and you will be able to enter MDX code in the following window:

For example if you only want to report CY 2002 and CY 2004 you would enter {[CY 2002], {CY 2004]} in the Manual MDX window. You can also specify an Excel cell range as the MDX source. Once you have defined your named set you can then select to report Descendants, Lowest Descendants or Descendants At.