ROA

Gets the value of an attribute of an element.

Select Insert > Function > ROA or use the Edit OLAP Formula dialog (Editing formulas) and select Attributes as the formula type.

Note: RWA is the writable version of ROA. To enable write back in a cell values formula, change ROA to RWA or select Allow writeback of attributes in the Edit OLAP Formula dialog.

See RWA

Syntax

=ROA("database alias", "cube", "hierarchy name", "attribute name",
		"element name")

Example

This formula:
=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

To retrieve the Name attribute, use:
=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)
Note: The aggregation function specified in the MLS-XML is not relevant to attributes and so is ignored.

Other formulas that support multiselect are:

Related topics