The @MDPARENTVAL() function returns parent-level data from multiple dimensions based on the current member being calculated.
@MDPARENTVAL(numDim, dimName1, . . . dimNameX [,mbrName])
| numDim | Integer value that defines the number of dimensions from which parent values are being returned. |
| dimName1, . . . dimNameX | Defines the dimension names from which the parent values are to be returned. |
| mbrName | Any valid single member name or member combination, or a function that returns a single member or member combination, from which the parent values are to be returned. |
Marketing expenses are captured at the Product Family and Region level in a product planning application. The Marketing Expense data must be allocated down to each Product code and State level based on Sales contribution.
Data is captured as follows:
Sales Marketing
===== =========
New York 100-10 300 N/A
100-20 200 N/A
100 500 N/A
Boston 100-10 100 N/A
100-20 400 N/A
100 500 N/A
East 100-10 400 N/A
100-20 600 N/A
100 1000 200
The Marketing Expense value of 200 at East and Product code 100 is allocated down to each Product code and State with the following formula:
Marketing = (Sales / @MDPARENTVAL(2, Market, Product, Sales)) * @MDPARENTVAL(2, Market, Product, Marketing);
Which produces the following result:
Sales Marketing
===== =========
New York 100-10 300 60
100-20 200 40
100 500 N/A
Boston 100-10 100 20
100-20 400 80
100 500 N/A
East 100-10 400 N/A
100-20 600 N/A
100 1000 N/A
The Marketing expenses can then be reconsolidated across Products and Markets.
@PARENTVAL
@SPARENTVAL
@MDANCESTVAL
Copyright (c)1991-2000 Hyperion Solutions Corporation. All rights reserved.