MDX and Rules Editor

Owned by
CXOS
Last updated: Oct 31, 2014 by
Migration
3 min read

What is MDX Rules and Editor?

MDX and Rules Editor window is used to create MDX statements and apply required formats to them.

How to create a MDX Statement

The top section of the MDX and Rules Editor window is used to create MDX statements:

  1. Use "Select Dimension" drop-down list to specify the dimension to be included in the MDX statement
  2. Use "Select Member" drop-down list to specify the dimension member to be inclused in the MDX statement
  3. Edit the MDX statement by adding required MDX set function
  4. Check the syntax

CONTENT

How to format MDX statements

The bottom section of MDX Rules and Editor window (List Rules) is used to format MDX statements.

You can format the created MDX statement by creating the rules and applying them to different levels and members of the dimensions.

To create a new rule press "Add a new rule" and set the rule name:

Define a condition, to which the rule will be applied:

  • Account level stands for dimension level
  • Account name stands for dimension member
  • Is last item stands for the last item in the list
  • Index stands for the serial number of the item in the list

Define an action to be applied to the condition:

  • Apply Format - allows to select a specific format to be applied to the condition
  • Apply Drill - allows to select a specific drill to be applied to the condition
  • Set Custom Description - allows to select a specific description to be applied to the condition
  • Set Empty - select this option, if you want to leave the condition empty
  • Set Visible - select this option, if you want to make the condition visible
  • Apply Header Format - allows to select a specific format, to be applied to the header
  • Set Reverse Valuation - makes the negative values positive and vice versa
  • Set Reverse Sign - flips the sign
  • Set Suppress Value - sets automatic row suppression (e.g. suppresses rows with no data and zeros)

Example:

Let's consider an example of a report, where in the rows we want to include all members from Account dimension.

The MDX statement in this case will look as follows:

[ACC].[ACC].AllMembers

Then we will set three rules to be applied to different levels of Account dimension:

  1. Account level equals 01 - apply Default Total format
  2. Account level equals 02 - apply Base Inclusive Drill
  3. Account name equals GrossMargin - apply Default Highlighted Header format

As a result we will get the following formatted report:

See also