OLAPAddMember

Description:

This function enables users to add new Members to a Dimension directly in an Excel environment.

The function has the following syntax:-

Syntax: OLAPAddMember (database, dimension, member, parent, weight)

Explained below are the 5 parameters that have to be entered when building this function.

• Database : Enter the name of the database which has the dimension name that you want to access / enter the cell reference which has the name of this Database .
• Dimension : Enter the name of the dimension in the above mentioned database which you want to add a Member to.
• Member : Enter the name of the member that you want to add into the Dimension indicated in the second parameter. The member name must be enclosed in double quotes " ", e.g., "New Member".
• Parent : Enter the name of a parent level member from which you want to add the newly created member as a child of /enter the cell reference which contains the name of the parent member. This is optional. In instances where you do not intend to include the newly added member into a hierarchy just type double quotes "" on the text box corresponding to this argument. leaving this parameter blank will bring up a #VALUE error.
• Weight : Enter the aggregate weight which you want to be assigned to the newly created member.

Remarks

• If one or more of the parameters indicated is erroneous the result will be a #value.
• The dimension must exist within this database and it should be referenced by the accurate and exact name.
• The name of the new member must be enclosed in double quotes " " for it to be considered valid.
• The parent member specified must exist within the database and it should be referenced by the accurate and exact name.
• The default value of the weight parameter is 1. The weight can be a positive or a negative number, and data corresponding to the newly added member will be multiplied by whatever is specified as its weight. The value of the weight specified will dictate how the data for the new member will be rolled up in its aggregate member (whether it will be added or subtracted).
• All arguments are compulsory with the EXCEPTION of the Parent parameter.

Example

This function may be easier to understand with an example. For this example, let us add a new member under Regions dimension. The new member to be added is Mexico, and make Mexico a child member of North America.

• In order to build this function with more ease, open the specific database and bring a slice from that database into excel. In this way you will have connected to PowerOLAP because you now have the OLAPDatabase formula automatically on your sheet when you bring a slice of the cube into Excel.



PowerOLAP Slice, bring this out to Excel






Slice to Excel

• In Excel, use the menu: Insert -> Function and select PowerOLAP from the drop down list.
• Choose the OLAPAddMember function from the list of functions provided and click "Ok".





OLAPAddMember Function Arguments

• When building the formula enter the cell range/or type the 'exact name' of the database name and the dimension name.
• Next, type in the name of the new member you want to create and add into the PowerOLAP database. Make sure to enclose this member name in double quotes. Following this example, type "Mexico".
• Enter the cell range/or type the 'exact name' of the parent member from which the new member to be added will be rolled up into. If you don't want to insert the new member in a hierarchy, type "" (double quotes).
• Enter a value for weight. For this example, just type 1. This means that data for Mexico will always be multiplied by 1 when it is rolled up into the parent member North America.



• Click 'OK'. Back in Excel we see the member name Mexico appear in one of the cells (circled in second image below).
  In the following example, based  the selections made above, clicking on the cell where the OLAPAddMember function is located will show the following formula. Note: You may also apply absolute reference when specifying cell references as parameters.

=OLAPAddMember(B1,B6,"Mexico",A10,1)








• The above formula should return the value = Mexico in Excel (as seen circled in the image above) and this is one indication that the parameters you have entered is valid, otherwise it will return a #VALUE error. Next, go and check PowerOLAP to verify if the new member has indeed been added. Notice that Mexico is now added in the Regions Member list and expanding the Region hierarchy will show that Mexico has indeed been added as a child member of North America.