Essbase member name manipulation - Net Transshipments

Transshipments
In some Essbase models there is a need to capture two essentially identical elements in separate dimensions. One common example of this is a transshipment model, where it is necessary to identify both the origination and destination of a shipment.

Two Dimensions
The most straightforward, and often user-friendly, manner to accomplish this type of tracking is to have the same hierarchy in two dimensions, with each member prefixed differently (i.e. the Source location members might be prefixed with "TO:" while the Destination members would be prefixed with "FROM:").

Net Shipments
A simple metric to consider in this type of model is often Net Shipments. In a multidimensional database, to calculate Net Shipments, shipments out of a location needs to be subtracted from shipments in (or vice-versa, depending on your preference). Assuming this is being calculated for Location A, the formula might look like:
Shipments -> TO:A -> Total Sources - Shipments -> From:A -> Total Destinations
(I.E. take all of the shipments that are sent to "A", regardless of where they are from, and subtract all of the shipments from "A" regardless of where they go to).

The Problem
The formula is simple and straightforward for one location, and remains similar for all other locations, but, every time a location is added, renamed, or deleted the calculation must be updated manually.

The Solution
Assuming the hierarchies are carefully constructed to always require "TO:" before the source location and "FROM:" before the destination location, Essbase can programatically determine the corresponding member by removing and replacing the prefix.

Components
Several string manipulation functions are required:

• @NAME or @ALIAS: Used to pass either an Essbase Member Name or the respective member's Alias Name to another function as a string.
• @SUBSTRING: This function will return a portion of a string passed to it.
• @CONCATENATE: Used to join two strings together.
• @MEMBER: Turns a string into a reference to a member name.

For this example, I'll also utilize @CURRMBR. This function returns the current member being calculated from a given dimension.

So, to determine the corresponding destination from a member in the source dimension:

1. Turn the current member in the source dimension into a string: @NAME(@CURRMBR("Source"))
2. Remove the prefix "TO:" from the string: @SUBSTRING(@NAME(@CURRMBR("Source")),3)
3. Prefix the new string with "FROM:": @CONCATENATE("FROM:",@SUBSTRING(@NAME(@CURRMBR("Source")),3))
4. Convert the string into a reference to a member: @MEMBER(@CONCATENATE("FROM:",@SUBSTRING(@NAME(@CURRMBR("Source")),3)))

Using this formula, you can fix on the portion of a hierarchy in the Source dimension, and have access to each member's corresponding Destination member. The original example of calculating Net Shipments might look like:
Fix(@RELATIVE("Source",0))
"Net Shipments"(
"Shipments"->"Total Destination" - "Shipments"->"Total Source"->@MEMBER(@CONCATENATE("FROM:",@SUBSTRING(@NAME(@CURRMBR("Source")),3)));
);
ENDFIX