Customize Form Axis using Native MDX

Author: Jeff Wang

Introduction

In this article, we are going to explore customizing a ROW axis on a form using NATIVE MDX. We will learn how to use MDX directly to customize the definition of a ROW and understand how that works.

 

 

Before We Begin

When we design a form using ROW, COLUMN, and FILTER in the Modeler UI, the underlying definition gets translated to a query language called MDX. Using the NATIVE MDX tab, we can view and edit that MDX to allow for complex expressions that would otherwise not be easily configurable through the UI.

 

For most form design scenarios, you will not need to have a deep understanding of MDX. However, for more powerful and flexible forms, we encourage you to take a moment to review the MDX Set Functions to have a better understanding of how MDX can be used in your form design.

 

 

Example

In our example, we are going to transform a form’s ROW axis by customizing its MDX. Here we have a form with Entity Hierarchy fully cross-joined with the Brand hierarchy.

 

 

However, we want to transform the above form to only show the Brand members at each leaf Entity Hierarchy member, thus excluding the Brands details at the rollups of the Entity Hierarchy members All, America and Asia-Pacific. The desired form is shown below.

 

 

To achieve the form we want, we will construct a MDX expression for the ROW axis that will use the original set and exclude the set of members that we want removed. Since we can use MDX expressions to represent each set, we will use MDX set functions to achieve the following:

 

(SET 1) – (SET2) = (SET 3)

 

 

 

How to Step by Step

Let’s first view the definition we have for the original form. The ROW definition has the following configuration:

 

 

This definition indicates that the members defined in the Entity Hierarchy column will be fully cross-joined with the members defined in the Brand column. Let’s go ahead and examine the NATIVE MDX. The highlighted MDX selection is where the ROW definition is being translated to in MDX.

 

 

We can copy the highlighted selected ROW MDX from NATIVE MDX->CURRENT and paste it into the NATIVE MDX->ROW tab. 

 

 

{
  {[Entity].[Entity Hierarchy].[All]}
  ,{ HIERARCHIZE (DESCENDANTS([Entity].[Entity Hierarchy].[All], 0, AFTER))}
}
*
{
  {HIERARCHIZE (DESCENDANTS([Product].[Brand].[All], 10000, LEAVES))}
  ,{[Product].[Brand].[All]}
}

 

Note: A set represents a collection of members from a hierarchy. Curly brackets represent a set. The * represent a cross-join set operation. When commas are used within a function they act as parameter separators, and when they are used within a set, they act as member/set separators. The HIERARCHIZE function is a MDX set function that orders the members of the hierarchy based on its natural ordering. The DESCENDANTS function is a MDX set function that returns a set of members that are at or below a given member in relation to the hierarchy.

 

By checking the ROW checkbox, we can inject the ROW tab’s MDX definition into the form definition.

 

 

If you select the main ROW tab, you will see the Native MDX is now active. At this stage, the form should still refresh the same as before as the NATIVE MDX->ROW tab’s MDX is the same as the original UI ROW definition.

 

 

Now that we know where to edit the ROW MDX definition, let’s transform our form to the desired view. First we need to define our two sets. 

 

(SET 1):

This definition is from our original ROW definition, but in compact format.

 

{[Entity].[Entity Hierarchy].[All], HIERARCHIZE (DESCENDANTS([Entity].[Entity Hierarchy].[All], 0, AFTER))}
*{HIERARCHIZE (DESCENDANTS([Product].[Brand].[All], 10000, LEAVES)),[Product].[Brand].[All]} 

 

 

(SET 2):

This definition is the set of members that we want to exclude. 

 

EXCEPT({[Entity].[Entity Hierarchy].[All], DESCENDANTS([Entity].[Entity Hierarchy].[All], 0, AFTER)}, DESCENDANTS([Entity].[Entity Hierarchy].[All], 100000, LEAVES))
*DESCENDANTS([Product].[Brand].[All], 10000, LEAVES)

 

Let's break down the expression for SET 2 so that we can understand what is happening:

• {[Entity].[Entity Hierarchy].[All], DESCENDANTS([Entity].[Entity Hierarchy].[All], 0, AFTER)} will return a set composed of the All member, followed by all other members of the Entity Hierarchy. 
• DESCENDANTS([Entity].[Entity Hierarchy].[All], 100000, LEAVES) will return a set of leaf members of the Entity Hierarchy members, such as United States, Argentina, China and Australia.
• EXCEPT({[Entity].[Entity Hierarchy].[All], DESCENDANTS([Entity].[Entity Hierarchy].[All], 0, AFTER)}, DESCENDANTS([Entity].[Entity Hierarchy].[All], 100000, LEAVES)) will return a set that removes the second set from the first set. This will resolve to All, America and Asia Pacific as the leaf members have been removed.
• DESCENDANTS([Product].[Brand].[All], 10000, LEAVES) will return a set of leaf members of the Brand hierarchy, such as Adair, Aviva, Harper and Zofia.
• The * cross joins the set for Entity Hierarchy with the set for Brands.

 

 

We can see what members are in each set by just running the form with the set expression in the NATIVE MDX->ROW definition.

 

 

Let’s combine the two sets with an EXCEPT function, which takes the first set and removes members from the second set, and use it in our ROW definition.

 

EXCEPT(
  -- (Set 1)
  {[Entity].[Entity Hierarchy].[All], HIERARCHIZE (DESCENDANTS([Entity].[Entity Hierarchy].[All], 0, AFTER))}
  * {HIERARCHIZE (DESCENDANTS([Product].[Brand].[All], 10000, LEAVES)),[Product].[Brand].[All]}
  ,
  -- (Set 2)
  EXCEPT({[Entity].[Entity Hierarchy].[All], DESCENDANTS([Entity].[Entity Hierarchy].[All], 0, AFTER)}, DESCENDANTS([Entity].[Entity Hierarchy].[All], 100000, LEAVES))
  * DESCENDANTS([Product].[Brand].[All], 10000, LEAVES)
)

 

And here we have our desired form.

 

 

In this article, we explored just one possible way to leverage NATIVE MDX to design advanced forms. Learning more about the MDX Set Functions and combining that with the NATIVE MDX functionality, you can access the full expressibility of the MDX query language to help in your form design.