ROA
Gets the value of an attribute of an element.
Select Edit OLAP Formula dialog (Editing formulas) and select as the formula type.
or use theSee RWA
Syntax
=ROA("database alias", "cube", "hierarchy name", "attribute name", "element name")
Example
=ROA("Best Practices OLAP","Sales","[Product]","Att2_German","[Product].[All Tires].[Car Tires All Season]")returns 'Ganzjahresreifen.'
Display attributes dynamically
You can dynamically display attributes in a hyperblock with the ROA formula.
Example
A list of products could have the names of different product managers as attributes. You could use an ROA formula to display the product manager for each product in a hyperblock.
Click in the value cell of the hyperblock. Open the Edit OLAP Formula dialog and select Attributes as the formula type. Select the database alias, cube, dimension and hierarchy. Select the attribute you want to display from the Attribute list. From the Element list, select Selection of a variable. The Contextual Selection of Objects dialog opens. Select the All variables check box and select the cell reference of the hyperblock from the list.
Dynamic attribute-related properties
In hyperblocks you can use ROA to retrieve attributes of string custom hierarchies and the OLAP Metadata hierarchy.
To retrieve the cube type from the OLAP Meta Data hierarchy use this syntax:ROA("","","Olap Meta Data","CUBE_TYPE",cube)
To retrieve the name of the attribute cube from a dimension, use this syntax:ROA("","","Olap Meta Data","DYNAMIC_ATTRIBUTE_CUBE",dim)
To retrieve the type of a dimension in the attribute cube, use this syntax:ROA("","","Olap Meta Data"," DYNAMIC_ATTRIBUTE_TYPE",dim)
The function returns one of these dimension types:
- Dimension with dynamic attributes (1)
- Driver dimension (2)
- Other dimension (0) – default
Attributes of custom hierarchies
=ROA("","","Hierarchy name","Name", "cell reference of the key output cell")
To retrieve the Description attribute, use:
=ROA("","","Hierarchy name","Description", "cell reference of the key output cell")
Normally, the first two arguments of an ROA formula are "database alias" and "data cube". But a custom hierarchy or list is not dependent on an alias or cube, so these arguments are left blank (""). The third argument of an ROA formula is normally a dimension name. Here, Hierarchy name is used instead. Name is the attribute of the hierarchy.
Multiselect
ROA supports multiselect. That is, you can return the attribute values of elements that are stored in multiselect MLS-XML.
For example, a report variable called rv_multi contains this MLS-XML:
<?xml version="1.0" encoding="utf-16"?> <MultiSelect xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <Hierarchy>[Period]</Hierarchy> <AggregationFunction>Sum</AggregationFunction> <StructureSelection> <Member> <MemberUName>[period].[all years].[2003]</MemberUName> </Member> <Member> <MemberUName>[period].[all years]</MemberUName> </Member> </StructureSelection> </MultiSelect>
This ROA formula returns 2002
and Alle Jahre
. That is, it returns the 'ATT2_German' attribute of each element in the MLS-XML:
=ROA("Best Practices OLAP","ANALYSIS","[Period]","ATT2_German",ReportVariables.rv_multi.Text)
Other formulas that support multiselect are: