Developer ToolsFree generatorPDF, Excel, Word exportOracle Hyperion / EssbaseAll Oracle Hyperion and Essbase tools

Essbase MDX Query Generator with Calculated Members

This generator writes Essbase MDX SELECT statements with NON EMPTY suppression, a row set built with Descendants or Children, a WHERE slicer tuple and a WITH MEMBER calculated member. It also gives ASO member formula patterns that use the same MDX functions, for Essbase 11.1.2.x and 21c.

Fill in

Application

Database

Column set

Row set

POV slicer tuple (WHERE)

Calculated member name

Calculated member formula

Slicer for the variance query

Generated code

Download:

Query 1: grid with suppression

SELECT
  NON EMPTY {[Year].[Jan], [Year].[Feb], [Year].[Mar]} ON COLUMNS,
  NON EMPTY Descendants([Product].[100], [Product].Levels(0)) ON ROWS
FROM [Sample].[Basic]
WHERE ([Scenario].[Actual], [Measures].[Sales]);

Query 2: calculated member (variance to Budget)

WITH MEMBER [Measures].[Sales Var] AS
  '[Measures].[Sales] - ([Measures].[Sales], [Scenario].[Budget])'
SELECT
  {[Measures].[Sales], [Measures].[Sales Var]} ON COLUMNS,
  NON EMPTY Descendants([Product].[100], [Product].Levels(0)) ON ROWS
FROM [Sample].[Basic]
WHERE ([Scenario].[Actual], [Year].[Qtr1]);

ASO member formula patterns (paste into the outline formula editor, no WITH or quotes)

/* Average price that returns #Missing instead of dividing by zero */
IIF([Measures].[Units] = 0, MISSING, [Measures].[Price Paid] / [Measures].[Units])

/* Different logic at level 0 and at parents of the Products dimension */
CASE
  WHEN IsLeaf([Products].CurrentMember) THEN [Measures].[Price Paid] - [Measures].[Returns]
  ELSE MISSING
END

/* Flag only level-1 members of Time */
IIF(IsLevel([Time].CurrentMember, 1), 1, MISSING)

Running it from the MaxL shell

login admin identified by 'password' on 'essbase-host';
/* paste the MDX query here, ending with a semicolon */
logout;
exit;

Function names and syntax checked against the Essbase 21c Technical Reference MDX section: NON EMPTY, WITH MEMBER, Descendants(member, layer or index, Desc_flags), Children, Levels, IIF, CASE, IsLeaf, IsLevel and MISSING. Dimension and member names default to Sample.Basic; the ASO formula block uses ASOsamp-style names, so replace them with your own. In Sample.Basic, Colas is the alias of member 100.

Stuck on an MDX formula?

Send the outline extract and the formula; we will return a corrected version with an explanation of each tuple.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Enter the application and database, a column set in braces and a slicer tuple in parentheses. Choose a row set: Descendants with Levels(0) returns the level-0 members under a parent, .Children returns one level down, and Descendants with an index of 1 and SELF_AND_BEFORE returns the member plus its children. Query 1 is a straight grid. Query 2 adds a calculated member that subtracts Budget from Actual using a tuple, so Sales Var appears as a column next to Sales. Every dimension you leave out of the axes and the slicer defaults to its top member, which is the most common reason a number looks too big.

A tuple has one member per dimension, in parentheses: ([Measures].[Sales], [Scenario].[Budget]).
A set is in braces and holds members from the same dimension(s): {[Year].[Jan], [Year].[Feb]}.
A dimension can appear on only one axis or in the slicer, never both.

Tuples and calculated members explained

Inside the WITH MEMBER formula, ([Measures].[Sales], [Scenario].[Budget]) is a tuple: it overrides the Scenario coordinate of the current cell and keeps every other coordinate. That is why the same formula works on every row and in any slicer. The calculated member exists only for the query. In an ASO outline the same expression language is used for stored member formulas, without the WITH MEMBER wrapper and without quotes. IIF and CASE let a formula behave differently by level, and MISSING returns #Missing instead of zero, which keeps NON EMPTY suppression working.

Where to run MDX

In the MaxL shell (essmsh) you can log in and paste an MDX query terminated with a semicolon; the grid prints to the console or to a spool file. In Smart View, newer releases offer an Execute MDX option on the Essbase ribbon for ad hoc grids, and free-form grids can also be built from member names. Essbase 21c also runs MDX reports from the web interface. ASO outline member formulas are entered in the outline editor using the third block above. Financial Reporting and OBIEE also generate MDX against Essbase, so the same syntax helps when you read their logs.

Gotchas

Member names with spaces or that start with digits need square brackets. Duplicate member name outlines need qualified names such as [Market].[East].[New York]. NON EMPTY removes rows only when every column is missing; if a calculated member returns 0 instead of MISSING, rows stay. Descendants with a numeric index counts steps down from the member, not outline levels, so Descendants([Year], 2) returns months in Sample.Basic. Large crossjoins on ROWS can hit query limits on ASO cubes; restrict with Filter or a narrower slicer before raising limits in essbase.cfg.

Frequently Asked Questions

What is a tuple in Essbase MDX?

A tuple is a cell coordinate made of one member from each of one or more dimensions, written in parentheses, for example ([Measures].[Sales], [Scenario].[Budget]). Dimensions not named in the tuple take their value from the current context. Tuples are used in WHERE slicers and inside calculated member formulas to read a value from a different scenario, period or version.

Can I write ASO member formulas with these MDX functions?

Yes. ASO outline formulas use MDX expressions, so IIF, CASE, IsLeaf, IsLevel, CurrentMember and tuples all work. Enter only the expression in the formula editor, without WITH MEMBER and without the surrounding single quotes. Keep formulas simple on high-volume dimensions because ASO evaluates them at query time.

How do I suppress missing rows in an Essbase MDX query?

Put NON EMPTY before the set on the axis, for example NON EMPTY {rows} ON ROWS. A row is removed only when all cells in the row are #Missing. Calculated members should return MISSING rather than 0 when they have nothing to show, otherwise the row survives suppression.

How do I select level-0 members under a parent in MDX?

Use Descendants([Dim].[Parent], [Dim].Levels(0)). Children returns only one level down, and Descendants with a numeric index returns members that many steps below the parent. In ragged hierarchies, Levels(0) is the reliable way to reach every leaf.

If you want your analysts to ask Essbase questions in plain English and see the MDX that answered them, on your own servers, we can set that up.

Talk to an Expert Explore More Free Tools