Top Five Oracle Cloud EPM Report Text Functions

Top Five Oracle Cloud EPM Report Text Functions

Lydia Maksoud
Brovanture Consultant

My Top Five Oracle Cloud EPM Report Text Functions revealed and explained.

When building reports in Oracle Cloud EPM Planning or Oracle Cloud EPM Consolidation and Close, text functions can be utilised to expand the report beyond a simple grid format and give added context to data. Text functions can also be used to surface additional information, including attributes, User defined Attributes (UDAs) and cell commentary.

Functions are access via the dropdown at the bottom of the Member Selector:

Top Five Oracle Cloud EPM Report Text Functions


  1. CellText

Purpose: Surfaces the cell comments for a specified cell

Example: Use this to report on variance commentary which is entered as a comment on a cell

Syntax: CellText(Row,Col) where Row/Col are optional


  • Cell comments can be entered against any member combination, which can then be surfaced for reporting
  • Comments add context to data for reporting
  • Allows comments to be held in the system, rather than offline, and then surfaced in reports


  1. MemberProperty

Purpose: Reference a range of properties for a selected member

Example: Display the packaging type attribute for a product member on the report

Syntax: MemberProperty(“Grid”, “Dimension”, Row/Col, “Property” ) where the Property is from the list below


  • Adds additional detail to the report which gives the context to the data
  • Allows export of attributes or UDAs which could be used to load to downstream systems
  • Can be used to return any of the following properties:
    • Attribute dimensions
    • User-defined attributes (UDAs)
    • Generation
    • Level
    • IsExpense
    • AccountType
    • Description
    • Alias Table
    • Base Currency


  1. Left/Right/Mid

Purpose: Trim the result of another column or function

Example: Remove the code name from an alias, for reporting purposes

Syntax: Below, where ‘text’ can be a function result:

  • Left (text, [number_of_characters])
  • Right (text, [number_of_characters])
  • Mid (text,start_position, [number_of_characters])


  • Extract information already on the grid, in a slightly different format
  • Strip unnecessary information where it is not required


  1. AncestorName / AncestorAlias

Purpose: To display the name or alias of the referenced member’s ancestor

Example: Show data for all bottom level members, with the parent and grandparent in separate columns next to the bottom level member

Syntax: AncestorName(“Grid”, “Dimension”, Row/Col, (Index)) or AncestorAlias(“Grid”, “Dimension”, Row/Col, (Index)), where the index references how many ancestor levels upward e.g. (1) is parent, (2) is grandparent etc.


  • Provides further context of hierarchy location
  • Displaying the parent in a column next to the member can be simpler for users to understand data, rather than with the parent shown above or below in hierarchy format
  • Parent or grandparent columns can be filtered when exported to Microsoft Excel
  • This works best for balanced hierarchies (i.e. not ragged)


  1. DateTime

Purpose: Returns the current date and time in a specified format

Example: Provide a timestamp of when the report was run and downloaded

Syntax: DateTime(” DateFormat “,” TimeFormat “), where:

  • DateFormat & TimeFormat are one of short|medium|long|full|none
  • If left blank, the report will use the user preferences format


  • Downloaded reports will have a timestamp for when the data was exported
  • Provides historic versioning

Until next time


For more tips on using functions in reports, check Lydia’s blog on the Top Five Oracle Cloud EPM Report Member Functions HERE.

You can see all our Brovanture Blogs and News HERE

Brovanture UKOUG EPM Partner of the Year 2023