@XREF

The following terminology is used to describe @XREF:

Target cube: the cube on which the current calculation is running (that is, the cube on which the @XREF call originates).
Source cube: the cube that is queried by @XREF. This cube may be remote (that is, on a different machine than the data target).
Point of view: the member combination currently being calculated on the target cube (that is, the member combination that identifies the left hand side of a calculation).

The @XREF function retrieves values from a source cube to be used in a calculation on a target cube. @XREF does not impose member and dimension mapping restrictions, which means that the source and target outlines can be different.

Syntax

There are multiple ways to call this function, depending on your goal.

Syntax 1

To incorporate values from a remote cube, use:

@XREF (locationAlias [, mbrList])

For Syntax 1 using location alias, a querying user does not need to be provisioned nor signed in on the remote cube.

Syntax 2

To incorporate values from another application and cube on the same Essbase instance, use:

@XREF (appname, dbname [, mbrList])

For Syntax 2, a querying user must be signed in on the target cube, and also provisioned on the source cube.

Parameters

locationAlias

A location alias for the data source. A location alias is a descriptor that identifies the source cube. A location alias is not needed if the source and target are on the same Essbase instance.

If used, the location alias must be set on the cube on which the calculation script will be run. The location alias is set by the database administrator and specifies a server, application, database, user name, and password for the source cube.

mbrList

Optional. A comma-delimited list of member names that qualify the @XREF query. The members you specify for mbrList are sent to the source cube in addition to the members in the current point of view in the target cube. The source cube then constructs a member combination, using in order of precedence:

The members specified in mbrList
The members in the current point of view
The top member in any unspecified dimensions in the source cube

The mbrList parameter (1) modifies the point of view on the target cube or (2) defines a specific point of view on the source cube. For example, the following formula modifies the point of view on the target cube:

2003(2003->Jan->Inventory = @XREF(sourceDB,Dec);)

If the source cube (sourceDB) contains data only from 2002, this formula sets Inventory for Jan in 2003 to the Inventory value for Dec from 2002.

The following formula defines a specific point of view on the target cube:

Jan = @XREF(sourceDB,January);

Assume that the target cube contains the member Jan, while the source cube (sourceDB) contains the member January. This formula simply maps the member in the target (Jan) with its corresponding member in the source (January), and pulls January from sourceDB.

See Notes for more information about the mbrList parameter.

appname, dbname

Application and cube name. Use only for deployments with only one Essbase instance.

Notes

An error is returned if the members supplied in mbrList do not exist in the source cube.
The number of data cells queried on the source cube must match the number of data cells expected on the target cube.
The member list cannot contain functions that return more than one member. For example, the following formula is not valid:
```
West = @XREF(SourceDb, @LEVMBRS(Market,0));
```
The member list cannot contain ranges. For example, the following formula is not valid:
```
West = @XREF(SourceDb, Jan:Mar);
```
mbrList can contain attribute members. For example, if the source cube classifies products based on a color attribute, the following formula would calculate the sum of the sales of all red products and would assign the result to member RedThings:
```
RedThings = @XREF(SourceDb, Sales, Red);
```
mbrList can contain attribute operators. For example, the following formula calculates RedThings as the average sales of all red products:
```
RedThings = @XREF(SourceDb, Sales, Red, Average);
```
@XREF can query all types of members. For example, members retrieved from a source cube can be Dynamic Calc members as well as attribute members. Keep in mind that all performance considerations that apply to dynamic and attribute calculations also apply to @XREF queries that depend on dynamic and attribute members.
Over the course of an @XREF calculation, data in the source cube may change. @XREF does not incorporate changes made after the beginning of the calculation.
@XREF is a top-down formula. For more information on top-down formulas, see Bottom-Up and Top-Down Calculation.
For a member that does not exist in either the source or the target cubes, @XREF returns the value of the top dimension, not the value #M1.
If you are using @PARENT within @XREF, it must be within @NAME. For example:
```
COGS=@XREF(Sample, @NAME(@PARENT(Product)),Sales);
```
When running a parallel calculation that includes @XREF, the application times out if the number of threads you specify to use is higher than the configured number of SERVERTHREADS.

Example

For this example, consider the following two databases:

Main Database

Year
  Qtr1
  Qtr2
Measures
  Sales
  Units  
Product
  100
    100-10
    100-20
Market
  East
  West
Scenario
  Budget
  Forecast

Inflation Rates Database

Year
  Qtr1
  Qtr2  
Assumptions
  Inflation
  Deflation = Inflation * .5 (Dynamic Calc)  
Country
  US
  Canada
  Europe

The following formula is associated with the Main Database:

Units = Units * @XREF(InflatDB,Inflation,US);

Where InflatDB is the location alias for the Inflation Rates Database and Inflation is the member for which a data value is retrieved from InflatDB.

In this example, Essbase calculates the following member combinations:

Units->Qtr1->100-10->East->Budget = Units->Qtr1->100-10->East->Budget * Inflation->Qtr1->US

Units->Qtr2->100-10->East->Budget = Units->Qtr2->100-10->East->Budget *Inflation->Qtr2->US and so on.