Show TOC

EPMDimensionOverrideLocate this document in the navigation structure

This function overrides the member selection for a specified dimension in one or several reports. The dimension member selection performed in the Member Selector is replaced by a specific cell or range of cells.
Parameter Description
reportID ID of one or several reports
dimension On a Planning and Consolidation connection, specify the dimension name. For other connections, specify the hierarchy name; to specify a hierarchy, enter: dimension.hierarchy
members Members you want to select for the specified dimension. The members override the members previously selected in the report.
reportID parameter
You can apply the selection to one or more reports, using the reportID parameter. For example: "000;001", where 000 corresponds to the first report of the sheet (also called the default report) and 001 corresponds to the second report of the sheet.
Note Use the ; character to separate the reports.
If you specify several reports in the reportID parameter, you can use the | character in the members parameter to separate the member override definition. The first member override definition is applied to the first report, the second member override definition is applied to the second report, and so on.
  • If you define more member override definitions than reports, the exceeding member override definitions will not apply to any report.
    For example: =EPMDimensionOverride("000;001;002","Category"," PLAN | ACTUAL,FORECAST | ACTUAL,FORECAST,PLAN | ACTUAL")
    • In report 000, the Category dimension members are overridden by the member PLAN.
    • In report 001, the Category dimension members are overridden by the members ACTUAL and FORECAST.
    • In report 002, the Category dimension members are overridden by the members ACTUAL, FORECAST and PLAN.
    • The fourth member override definition ACTUAL is not applied to any report.
  • In report 002, the Category dimension members are overridden by the members ACTUAL, FORECAST and PLAN and ACTUAL.
  • The fourth member override definition ACTUAL is not applied to any report.
  • If you define more reports than member override definitions, the exceeding reports will use the last member override definition.
    For example: =EPMDimensionOverride("000;001;002;003","Category"," PLAN | ACTUAL").
    • In report 000, the Category dimension members are overridden by the PLAN member.
    • In report 001, the Category dimension members are overridden by the ACTUAL member.
    • In report 002, the Category dimension members are overridden by the ACTUAL members.
    • In report 003, the Category dimension members are overridden by the ACTUAL members.
Note The master report takes precedence over the other reports.
members parameter
For the members parameter, you can use the following values (keywords) to specify a dynamic selection of members:
  • MEMBERS. Retrieves all the members of the dimension.
  • BASMEMBERS. Retrieves all the base level members of the dimension.
  • BAS. Retrieves all the base level members that are below the context member of the dimension.
  • DEP. Retrieves all the children of the context member of the dimension.
  • ALL. Retrieves all the descendants of the context member of the dimension.
  • SELF. Retrieves the context member of the dimension.
  • LDEP(n). Retrieves all the descendants of the context member of the dimension until the level n in the hierarchy - including the members in the intermediate levels. For example: LDEP(2,2008.TOTAL) retrieves the semester and the quarter members of 2008.

    In addition, you can optionally use the y character to retrieve only the members for the level specified by n. For example: LDEP(2,2008.TOTAL,Y) retrieves only the quarter members of 2008.

  • LBAS(n). Retrieves all the base level members that are below the context member of the dimension, until the level n in the hierarchy - including the members in the intermediate levels. Optionally, you can use the y character to retrieve only the members for the level specified by n.
  • LMEMBERS(n). Retrieves all the descendants of the "all" member of the dimension until the level n in the hierarchy - including the members in the intermediate levels. Optionally, you can use the y character to retrieve only the members for the level specified by n.
  • LBASMEMBERS(n). Retrieves all the base level members of the "all" member of the dimension until the level n in the hierarchy - including the members in the intermediate levels. In addition, you can optionally use the y character to retrieve only the members for the level specified by n.
  • PARENTAFTER. This value cannot be used alone. For example: MEMBERS,PARENTAFTER. Retrieves all the members of the dimension; displaying the totals at the bottom in the row axis, or displaying totals to the right in the column axis.
    Note
    • The values above are not case-sensitive.
    • To ensure that members are recognized as members, enter member names in ' ' (single quotes) or " " (quotation marks) when member names contain spaces or special characters. For example: =EPMDimensionOverride("000","FL","'99 - Closing Balance'")
Restriction

The ID given to a member/dimension/hiercharchy/navigation attribute in the database should not be a keyword.

Examples:
  • SELF. Context member. If you change the member in the context, the member is updated in the report.
  • SELF, DEP. Context member and its children.
  • SELF, ALL. Context member and its descendants.
Note
  • You can specify cells that are either in the current sheet or in other sheets.
  • You can enter any Microsoft Office Excel formula.
  • This function is dynamic, meaning that if you have specified SELF, DEP and if, for example, a child of the context member is deleted in the data source, it is also deleted from the report.
  • When using a Planning and Consolidation connection, you can enter a property selection: property=propertyvalue. For example, for the Account dimension, you can enter ACCTYPE=INC to retrieve all the income accounts. Use the Planning and Consolidation technical name of the properties and property values. To combine property values, only the operators OR and AND are supported.