How to Load Metadata in a Planning Application

@IANCESTORS calculation function in PBCS

by

The @IANCESTORS calculation function in PBCS is a powerful tool that allows you to return the specified member and all of its ancestors, up to a certain generation or level. This function can be used in a variety of ways, such as to:

  • Calculate aggregate values for a member and all of its ancestors
  • Apply adjustments to a member and all of its ancestors
  • Create reports that show the relationships between members

Syntax

The syntax for the @IANCESTORS function is as follows:

@IANCESTORS (mbrName, generation, levelName)

Where:

  • mbrName is a valid single member name, or a function that returns a single member.
  • generation is an optional integer value that defines the absolute generation or level number up to which to select members. A positive integer defines a generation number. A value of 0 or a negative integer defines a level number.
  • levelName is an optional level or generation name up to which to select members.

Example

The following example shows how to use the @IANCESTORS function to calculate the total sales for a product and all of its categories, up to the third generation:

FIX(Product,"100-20")

Total Sales = SUM(@IANCESTORS("100-20",3),Sales);

ENDFIX;

This calculation will return the total sales for the product “100-20” and all of its categories, including the “Colas” and “Diet” categories, and the “Beverages” category.

Using @IANCESTORS with other functions

The @IANCESTORS function can be used in conjunction with other functions to perform more complex calculations. For example, the following calculation uses the @IANCESTORS function to calculate the growth rate for a product and all of its categories, up to the second generation:

FIX(Product,"100-20")
Growth Rate = (@IANCESTORS("100-20",2),Sales) / (@IANCESTORS("100-20",2),Sales,@LAG,1);
ENDFIX;

This calculation will return the growth rate for the product “100-20” and all of its categories, based on the sales for the current period and the previous period.

Best practices for using @IANCESTORS

Here are some best practices for using the @IANCESTORS function:

  • Be aware that the @IANCESTORS function can return a large number of members, depending on the size and complexity of your dimension hierarchy. If you are using this function in a calculation, make sure that the calculation is efficient and that it does not return more members than necessary.
  • Use the @IANCESTORS function in conjunction with the FIX statement to restrict the calculation to a specific subset of members. This will help to improve the performance of your calculation.
  • Be aware that the @IANCESTORS function does not guarantee the order in which members are returned. This is important to consider when using this function with certain forecasting and statistical functions.

Examples of using @IANCESTORS

Here are some examples of how the @IANCESTORS function can be used:

  • Calculate aggregate values for a member and all of its ancestors:

FIX(Product,"100-20")
Total Sales = SUM(@IANCESTORS("100-20"),Sales);
ENDFIX;

This calculation will return the total sales for the product “100-20” and all of its categories.

  • Apply adjustments to a member and all of its ancestors:

FIX(Product,"100-20")
Sales = Sales * 1.05;
ENDFIX;

This calculation will increase the sales for the product “100-20” and all of its categories by 5%.

  • Create reports that show the relationships between members:

REPORT REGION Sales by Category
COLUMN CATEGORY
COLUMN SALES
DATA
CATEGORY,SALES
@IANCESTORS("100-20"),Sales
ENDDATA
ENDREPORT;

This report will show the sales for the product “100-20” and all of its categories.

Advanced usage

The @IANCESTORS function can also be used to perform more advanced calculations. For example, the following calculation uses the @IANCESTORS function to calculate the weighted average sales for a product and all of its categories, up to the third generation.

Leave a Comment