ROA

This function returns the value of an attribute of an element.

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

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

Syntax

=ROA("data_connection","cube","hierarchy_name","attribute_name","element_name")

Example

This formula returns the German name of the specified element, which is stored in the Attr2_German attribute:

=ROA("BestPractices OLAP","Sales","[Product]","Att2_German","[Car Tires All Season]")

Display attributes dynamically

You can use ROA to dynamically display attributes in a hyperblock.

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 data connection, cube, dimension and hierarchy. From the Attribute list, select the attribute to display. From the Element list, select the cell reference of the hyperblock.

Returning data from OLAP metadata

ROA can be used to get data from OLAP metadata. OLAP metadata is a virtual hierarchy which describes the cubes, dimensions, and hierarchies of OLAP data. When you use ROA with OLAP metadata, the alias and cube parameters are empty strings ("","") and the dimension name is "Olap meta data".

These attributes are supported:

  • Actual_Level_Cardinality
  • Children_Cardinality
  • Database_Name
  • Dataconnection_Caption
  • Dataconnection_name
  • Datasource_URL
  • Description
  • Level_Number
  • Level_Unique_Name
  • Measure_Dimension
  • Member_Caption
  • Member_Name
  • Member_Type
  • Member_Unique_Name
  • Member_Weight
  • Name
  • Parent_Level
  • Parent_Unique_Name
  • Project_Name
  • Provider_Type
  • Server_Name
  • Server_URL
  • Session_ID
  • Time_Dimension
  • Value_Type

Example

To return the unique name of a cube, use this syntax: ROA("","","Olap meta data","Name","[Repository].[alias].[cube_name]").

Example

To retrieve the unique name of the parent of a specific cube, use this syntax: ROA("","","Olap meta data","Parent_Unique_Name","[Repository].[alias].[cube_name]")

Example

To retrieve the server URL of an alias, use this syntax: ROA("","","Olap meta data","Server_URL","[Repository].[alias]")

Attributes of custom hierarchies

Custom hierarchies are hierarchies that you create. Like OLAP metadata, the alias and cube names are empty strings. For the hierarchy parameter, use the custom hierarchy name.

To retrieve the Caption attribute, use: =ROA("","","Hierarchy name","Member_Caption", "element_name")

To retrieve the Level number attribute, use:

=ROA("","","Hierarchy name","Level_Number", "celement_name")

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].[2017)]</MemberUName>
    </Member>
     <Member>
      <MemberUName>[period].[all years]</MemberUName>
    </Member>
  </StructureSelection>
</MultiSelect>

This ROA formula returns Jahr 2017 and Alle Jahre. That is, it returns the ATT2_German attribute of each element in the MLS-XML: 

=ROA("BestPracticesOLAP","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.
Related topics