LookupCube
The LookupCube function is available on Cloud and since eazyBI version 9.1 (not released yet).
EAZYBI Evaluates an MDX expression in another cube of the same account and returns its value. Use it to show measures of another cube in a report.
Syntax
LookupCube(Cube_Name, Expression_String) LookupCube(Cube_Name, Expression_String, SHARED_CONTEXT) LookupCube(Cube_Name, Expression_String, Dimension_or_Hierarchy [, Dimension_or_Hierarchy ...])
Arguments
Cube_Name | A string with the name of another cube in the same account, for example, |
|---|---|
Expression_String | A string with an MDX expression that eazyBI evaluates in the other cube, for example, |
SHARED_CONTEXT | (Optional) Applies the current report context of all dimensions and hierarchies that exist in both cubes. |
Dimension_or_Hierarchy | (Optional) One or more dimensions or hierarchies whose current report context is applied in the other cube, for example, |
How the report context is applied
By default, with two arguments, eazyBI evaluates the expression in the default context of the other cube: the rows, columns, and page filters of the report do not change the result. To get a value for the current report row or column, either specify the dimensions to map with the third and following arguments or build the member names into the expression string.
When you specify SHARED_CONTEXT or a list of dimensions and hierarchies, eazyBI finds the current members of the report in the other cube by their unique names, for example, [Time].[2026].[Q1 2026] or [Project].[My project]:
- If a member does not exist in the other cube, the result is empty.
- Page filters with several selected members and calculated members that aggregate other members, for example,
Aggregate({...})or[Time].[2025] + [Time].[2026], are applied as well. - Other calculated members of a mapped dimension, for example, a calculated member with a
CASEexpression, cannot be mapped, and the report shows an error. In such a case, specify only the dimensions and hierarchies that you need instead ofSHARED_CONTEXT. - The Measures dimension is not mapped. Always specify the measure in the expression string.
- Members that you specify in the expression string, for example,
"([Measures].[Budget hours], [Time].[2026])", override the mapped report context for that dimension.
Examples
Budget from another cube
You have imported budget hours from a spreadsheet in a separate Budget cube with the Project and Time dimensions and the measure Budget hours. To compare the budget with the logged hours, define a calculated measure Budget hours in the Issues cube. With SHARED_CONTEXT, the value is filtered by the project and time period of the report row, column, and page filters.
LookupCube("Budget", "[Measures].[Budget hours]", SHARED_CONTEXT)
Then use the result together with the measures of the Issues cube, for example, to calculate the percentage of the budget that is spent:
CASE WHEN [Measures].[Budget hours] > 0 THEN [Measures].[Hours spent] / [Measures].[Budget hours] END
Apply only the Time dimension context
The report shows issues by project and month, and you want to show the total revenue of each month from the Sales cube for all projects. Specify the Time dimension to apply only the time period of the report. The other dimensions of the Sales cube use their default members.
LookupCube("Sales", "[Measures].[Revenue]", [Time])
You can specify several dimensions or hierarchies, for example, LookupCube("Sales", "[Measures].[Revenue]", [Time], [Project]).
Fixed members in the expression
The expression string can specify members of the other cube. This example returns the approved budget of the current project and time period, where Budget type is a dimension that exists only in the Budget cube:
LookupCube("Budget",
"([Measures].[Budget hours], [Budget type].[Approved])",
SHARED_CONTEXT
)
Without the third argument, the expression ignores the report context. This example always returns the total budget of the year 2026:
LookupCube("Budget", "([Measures].[Budget hours], [Time].[2026])")
Build the expression string from the report context
You can also build the expression string from the current report members:
LookupCube("Budget",
"([Measures].[Budget hours], " + [Project].CurrentMember.UniqueName + ")"
)
The context arguments are simpler and faster, and they also apply page filters with several selected members and aggregated calculated members.
Notes
- The user's data access roles also apply in the other cube. If the user cannot access the other cube or the cube does not exist, the report shows an error.
- A dimension or hierarchy which you specify in the arguments must exist in both cubes, otherwise the report shows an error.
SHARED_CONTEXTignores the dimensions that exist only in one of the cubes. - When a report uses LookupCube and hides empty rows, eazyBI checks the values of each row, so that the rows which have values only in the other cube are kept. Such reports can be slower, especially with many rows.
See also
- Function CatchException to return an empty or specified value when an expression fails.