1. Calculation and Query Reference for Oracle Essbase
  2. Calculation Functions
  3. Calculation Function List
  4. @WEIGHTEDSUMX

@WEIGHTEDSUMX

The @WEIGHTEDSUMX calculation function for Essbase aggregates all members in a member list, depending on the unit weight of each member, which is fetched from a remote data source.

This function improves the performance of aggregating currency databases by calling the calculation framework only once.

The following terminology is used to describe this function:

  • Data target: the database on which the current calculation is running (that is, the database on which the @WEIGHTEDSUMX call originates).

  • Data source: the database that is queried by @WEIGHTEDSUMX. This database may be remote (that is, on a different machine than the data target).

  • Point of view: the member combination currently being calculated on the data target (that is, the member combination that identifies the left hand side of a calculation).

Syntax

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

To incorporate values from a remote cube, use this syntax:

@WEIGHTEDSUMX (mbrList, locationAlias [, CurrencyType, CurrencyRate, Period]);

The mbrList and locationAlias parameters are required. If the other parameters are not provided, they are taken from the POV.

To incorporate values from another application and database on the same Essbase server instance, use this syntax: 

@WEIGHTEDSUMX (mbrList, appname, dbname [, CurrencyType, CurrencyRate, Period]);

Parameters

mbrList

Required. Specifies the list of members to be aggregated according to the unit weight of the individual members. The mbrList can be a calculation function that returns a member list or a comma-separated list of member names. The member list cannot contain functions that return more than one member.

Examples of functions that return a member list: @CHILDREN, @DESCENDANTS, and @RANGE.

A comma-separated list of member names must be expressed as a single argument. For example, a list of currencies such as "USD","ARS","AUD","BRL" can be used with a member list function, as in @LIST ("USD", "ARS","AUD","BRL"), or expressed as a range if the members are at the same level, as in "USD":"BRL", or enclosed in parentheses, as in ("USD", "ARS","AUD","BRL").

The members you specify for mbrList are sent to the data source in addition to the members in the current point of view in the data target. The data source 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 data source

The following formula modifies the point of view on the data target. Assume that the cube on the data source (sourceDB) contains data only from 2002. This formula sets Inventory for Jan 2003 to the Inventory value for Dec 2002:

2003(2003->Jan->Inventory = @WEIGHTEDSUMX (mbrList, locationAlias, Dec) ;)

The following formula defines a specific point of view on the data target. Assume that the data target contains the member Jan and the data source (locationAlias) contains the member January. This formula maps the member in the data target (Jan) with its corresponding member in the data source (January), and pulls January from data source:

Jan = @WEIGHTEDSUMX (mbrList, locationAlias, January);

The following formula is an example of using @RANGE with a comma-separated list of members, which includes a range of members at the same level: 

@WEIGHTEDSUMX(@RANGE("Entered","USD":"ZAR"), _FCCS_Rates_, "Rate.Average", "Rate_USD");
locationAlias

Required. A location alias for the data source. A location alias is a descriptor that identifies the data source. The location alias must be set on the database 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 data source.

appname, dbname
Application and cube name. Use only for deployments with only one Essbase server instance.
CurrencyType
Optional. A member in a dimension that contains currency types, with members such as Average, Closing, or Historical.
CurrencyRate
Optional. A member in a dimension that contains currency rates, with members depicting the global currency rates.
Period
Optional. A member from a time dimension.

Notes

  • You must be signed in on the data target, and also provisioned on the data source.

  • An error is returned if the members supplied in mbrList do not exist in the data source.

  • The number of data cells queried on the data source must match the number of data cells expected on the data target.

  • The member list cannot contain functions that return more than one member.

  • Only one parameter can be provided per dimension.