This function renames a property of a particular dimension
member in PowerOLAP directly from an excel environment.
The OLAPRenameProperty function allows you to replace
the name of a property of any member of a specified dimension, in
a specified database in two ways. First, you can choose to simply
specify the first four parameters and, as a result, this function
will return the particular property name, and by double clicking on
the cell where the property name was returned this will show a Rename
Property dialog box where you can type in the new property name. Another
method is by specifying a fifth parameter, which is the 'new property
name' which will automatically change the property name back in your
PowerOLAP database.
The function has the following syntax:
Syntax: OLAPRenameProperty
(database, dimension, group, member, new name (optional 5th parameter))
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 and property group 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 specified database which has the Property Group you want
to access / enter the cell reference which has the name of such
a dimension.
- Group : Enter the name of the Property Group
you want to access that exists in the dimension and database that
was specified / enter the cell reference which has the name of
such a property group.
- Member : Enter the member name of the property
in the above mentioned Property Group within the specified dimension
of the specified database / enter the cell reference which has
the member name.
- New Name : Enter the new name that would replace
the original property name.
Remarks
- If a connection is not established with a database
the result will be a #value.
- There must be the same property group existing
in the database as entered for the Group parameter above.
- Be sure to enclose the new name in double quotes,
should you opt to specify a 5th parameter.(").
- Only the 5th 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 connected 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 OLAPRenameProperty function from
the list of functions provided and click "Ok".
- When building the formula enter the cell range
for the database name, the dimension name and type in the name
for the property group in that database as well as the member
carrying that property. Lastly, type in the new property name
for your dimension member. If you don't want to manually enter
the property group name you can use another function called OLAPPropertyGroup
in a cell to get the property group name/names and then use that
cell as a reference for a formula.
- For example, in the Months dimension, we want
to rename the property of November from "Autumn" to
"Fall". The formula can be written as follows: B2 is
the cell that holds the database name (UsingPO), B6 is the cell
that holds the dimension name (Months), "Season" is
the name of the Property Group, "November" is the member
name and "Fall" is the new property name.
=OLAPRenameProperty($B$1,$B$6,"Season",
"November","Fall") |
The formula will return the new property name,
which is also written back or reflected in the PowerOLAP database.