This function renames the member of a particular dimension
in PowerOLAP directly from an excel environment.
The OLAPRenameMember function allows you to change
the name of a dimension member, in a specified database, using two
ways. First, you can choose to simply specify the first three parameters
and, as a result, this function will return the particular member
name, and by double clicking on the cell where the member name is
returned, this will show a Rename Member dialog box where you can
type in the new member name. Another method is by specifying a fourth
parameter, which is the new member name which will automatically replace
the member name back in your PowerOLAP database.
The function has the following syntax:
Syntax: OLAPRenameMember
(database, dimension, index, new name (optional 4th parameter))
Explained below are the 4 parameters that have to
be provided when building this function.
- Database : Enter the name of the database which
has the dimension and that you want to access / enter the cell
reference which has the name of this Database.
- Dimension : Enter the name of the dimension
which exists in this database mentioned above and is the dimension
whose member you want to access through the index parameter /
instead enter the cell reference which has the name of such a
dimension.
- Index : Enter the index of the member that
resides in the dimension above. The member corresponding to this
index number will be the value returned by the function.
- New Name : Enter the new name that will replace
the original member name.
Remarks
- If a connection is not established with a database
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 value for the parameter "Index"
must be a positive real integer and should correspond to the member,
whose name you want to access through this function and it's the
value that the function has to return.
- Only the fourth parameter is optional, the
rest of the arguments are required.
Example
This function may be easier to understand with an
example.
- 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 connection to PowerOLAP
because you now have the OLAPOpen formula automatically on your
sheet when you bring a slice of the cube into Excel.
- In Excel, use the menu: Insert -> Function
and select PowerOLAP from the drop down list.
- Choose the OLAPRenameMember function from the
list of functions provided and click "Ok".
- When building the formula enter the cell range
for the database name and the dimension name. If you don't want
to manually enter the member you can use another function called
OLAPDimension in order to get this value and then use that cell
as a reference for a formula.
- Also enter an index number as a value for the
parameter "index" which signifies that the member name
that the function should return will correspond to the index specified.
- In the following example B1 is the cell that
holds the database name (UsingPO), B6 is the name of the dimension
(Accounts) from which the member names will be accessed, "2"
is the index of the member you want to rename (Cost of Sales),
and "COS" is the new name for your member.
=OLAPRenameMember
($B$1, $B$6, 2, "COS") |
The formula will return the new member name,
which is also written back or reflected in the PowerOLAP database.
