In this tutorial (part III of our MDX training series), we will cover calculations/operations. In MDX, we can define basic calculations using simple operators (+, -, *, /):
[Account].[Account].[Salaries] + [Account].[Account].[Health Insurance]
Before you start
Complete parts I and II of our MDX training series before following this guide.
WITH
WITH allows you to create a temporary calculated member to use in a query. For example, if we wanted to see the variance between the Budget and Actual scenarios, we could write:
WITH
MEMBER [Scenario].[Scenario].[Budget vs Actual] --Temporary calculated member, we can name this whatever we want
AS '[Scenario].[Scenario].[Budget]-[Scenario].[Scenario].[Actual]' --Put the calculation in single quotes
The full query looks like:
WITH
MEMBER [Scenario].[Scenario].[Budget vs Actual]
AS '[Scenario].[Scenario].[Budget]-[Scenario].[Scenario].[Actual]'
SELECT
{
DESCENDANTS([Account].[Account].[Salaries & Benefits], 10000, LEAVES)
}
ON ROWS,
{
{[Scenario].[Scenario].[Budget],[Scenario].[Scenario].[Actual],[Scenario].[Scenario].[Budget vs Actual]}
}
ON COLUMNS
FROM [Workforce-Reporting]
WHERE
{[Time].[FiscalYear].[FY20]}
*{[Department].[Department].[Sales]}
*{[Entity].[Entity].[United States]}
ORDER
ORDER allows us to sort the results of a query. This statement takes three arguments:
- Set expression (members we want to sort)
- Numeric expression (value we will sort the members by)
- Flag (sort ascending ASC or descending DESC)
For example, to order the members in the Salaries & Benefits hierarchy by the actual value, in ascending order, we could write:
ORDER(
DESCENDANTS([Account].[Account].[Salaries & Benefits], 10000, LEAVES),
[Scenario].[Scenario].[Actual],
ASC
)
We could use this statement in a full query:
SELECT
{
ORDER(
DESCENDANTS([Account].[Account].[Salaries & Benefits], 10000, LEAVES),
[Scenario].[Scenario].[Actual],
ASC
)
}
ON ROWS,
{
[Scenario].[Scenario].[Actual]
}
ON COLUMNS
FROM [Workforce-Reporting]
WHERE
{[Time].[FiscalYear].[FY20]}
*{[Department].[Department].[Sales]}
*{[Entity].[Entity].[United States]}
We can also use calculations in an ORDER statement. If we wanted to sort by the variance between Budget and Actual values, we could change the order statement in the above query to:
ORDER(
DESCENDANTS([Account].[Account].[Salaries & Benefits], 10000, LEAVES),
[Scenario].[Scenario].[Budget]-[Scenario].[Scenario].[Actual],
ASC
)
Tip: In addition to ASC and DESC flags, we can specify BASC or BDESC for Break-Hierarchy Ascending and Break-Hierarchy Descending, respectively. While ASC and DESC will order the members within their place in the Hierarchy, BASC and BDESC will order the members regardless of where they appear in the hierarchy.
SCOPE
Set up test model
The Scope function cannot be appended to the query types we have been using. To explain this functionality, we will add an MDX Rule to a Kepion Model.
To test this functionality out:
- Create a new Model in the Integrated Financial Planning App called Test.
- Use the Department and Scenario dimensions.
- Create a form with Departments on the Rows and Scenario on the columns (to easily add data to the Actual slice, you may want to check the Input box in the Scenario dimension).
- Fill in some sample data.
Your test form should have no filters and look similar to this:
Scope Function I
SCOPE is used to limit an MDX expression to a particular set of members within a cube. We can reference the values within a SCOPE statement using the keyword THIS.
For example, if we wanted to replace all of the forecast values with actuals, we could write:
SCOPE(
[Scenario].[Scenario].[Forecast]
);
THIS = [Scenario].[Scenario].[Actual];
END SCOPE;
To add this script, create a new MDX rule in the Rules node. Ensure you deploy the application after you save the rule.
Now in our test Form, we can see that all the Forecast values have been overridden by the Actual values. However, this change only exists in the cube. The Forecast values in the underlying SQL table have not been affected. If we disable the MDX script, those values will reappear.
Scope Function II
Let's look at another example of the Scope function. In this case, we have a scenario called Actual & Forecast, which should be an aggregation of the Actual and Forecast values.
We can define it as follows:
SCOPE(
[Scenario].[Scenario].[Actual & Forecast]
);
THIS = [Scenario].[Scenario].[Actual]+[Scenario].[Scenario].[Forecast];
END SCOPE;
Scope Function III
Now let's take a look at what happens if we multiply the two values together:
SCOPE(
[Scenario].[Scenario].[Actual & Forecast]
);
THIS = [Scenario].[Scenario].[Actual]*[Scenario].[Scenario].[Forecast];
END SCOPE;
Notice how the All value for Actual & Forecast is the product of the All-Actual and All-Forecast values, rather than the sum of all the Actual & Forecast leaf Members:
- 700 x 1,400 = 980,000
- 20,000 x 7 = 140,000
To change this behavior, we can narrow the scope of our calculation to only the leaf Members of the Department Dimension (thus excluding the All members):
SCOPE(
[Scenario].[Scenario].[Actual & Forecast],
DESCENDANTS([Department].[Department].[All], 10000, LEAVES)
);
THIS = [Scenario].[Scenario].[Actual]*[Scenario].[Scenario].[Forecast];
END SCOPE;
Now the calculation is only targeting the Actual & Forecast scenario where it intersects with the leaf Members of the Department Dimension. You can visualize how the two sections of the Scope statement interact below:
The All-Actual & Forecast cell is now the normal aggregation of its child Members.
IIF
MDX calculations also support conditional logic. IIF statements are useful when you want the calculation to perform different actions based on a condition.
The syntax for an IIF statement is:
IIF(
<Logical condition>,
<Expression if true>,
<Expression if false>
);
If the logical condition is true, the first expression will execute; if the condition is false, the second expression will execute. For example, if we looked at the percentage of Actual to Forecast values:
SCOPE(
[Scenario].[Scenario].[Actual & Forecast]
);
THIS = [Scenario].[Scenario].[Actual]/[Scenario].[Scenario].[Forecast];
END SCOPE;
We could use an IIF statement to cap the variance at 100%:
SCOPE(
[Scenario].[Scenario].[Actual & Forecast]
);
THIS = IIF(
[Scenario].[Scenario].[Actual]/[Scenario].[Scenario].[Forecast] <= 1,
[Scenario].[Scenario].[Actual]/[Scenario].[Scenario].[Forecast],
1
);
END SCOPE;
This statement says that if Scenario / Forecast is less than or equal to 1 (i.e., 100%), then perform the calculation as normal. If Scenario / Forecast is not less than or equal to 1, then set the value equal to 1.