Top 5 EPM Report Text Functions

When building Reports in Oracle Cloud EPM Planning or FCC, 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, UDAs and cell commentary.  

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


 Here are my top 5 text functions to use when creating EPM reports:

      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

Benefits:

·         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 surfaced in reports  


2. 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

Benefits:

·         Adds additional detail to the report which gives 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:

o   Attribute dimensions

o   User-defined attributes (UDA)

o   Generation

o   Level

o   IsExpense  

o   AccountType

o   Description

o   Alias Table

o   Base Currency

 

3. 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])

Benefits:

·         Extract information already on the grid, in a slightly different format

·         Strip unnecessary information where it is not required

     

     4. 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.

Benefits:

·         Provides further context of hierarchy location

·         Displaying the parent can provide 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 Excel

·         This works best for balanced hierarchies (i.e. not ragged)

 

5. 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

Benefits:

·         Downloaded reports will have a timestamp for when the data was exported

·         Provides historic versioning

For more tips on using functions in reports, check out my blog on the Top 5 EPM Report Member Functions here: https://lydia-maksoud-epm.blogspot.com/2022/10/top-5-epm-report-member-functions.html

Comments

Post a Comment

Popular posts from this blog

Loading multi-period row data files using Data Management

Image
The ability to load multi-period data with the Period and/or Year in the rows is not particularly new (since release 16.10), however it is something that most people seem to forget is possible in Data Management. This really is something worth remembering as it eliminates the need to either split the data files into one file per month or try to pivot the month into the columns. In order to set this up, let’s firstly look at how to add the Period and Year columns to the import format. Add the Period and Year columns using the Add -> Source Period Row dropdown, and input: The Source Column name – e.g. PERIOD The Field Number (file column) – e.g. 3 Any required expressions for that field  As these are Period/Year dimensions, the mappings are not held in the usual Data Load Mappings area. Instead they are configured in the Period Mappings , under the Source Mapping tab. Select the Source System as File to add a new mapping calendar for loading the data ...
Read more

Executing Smart View Retrievals using VBA

Image
When working with Hyperion systems, Smart View is a key tool used for reporting, as well as inputting data. It provides a valuable link between Hyperion and Excel, which allows users to retrieve data from the database straight into Excel with ease. Smart View functions are available through the Smart View toolbar and can be used with Essbase, HFM, Planning or Cloud connections. Occasionally, it is necessary to perform Smart View functions behind the scenes using VBA code, rather than manually using the Smart View toolbar. For example, a spreadsheet which will refresh all the Essbase retrieval sheets on opening, so that users can view the most up to date data, without having to remember to refresh each of the retrieval sheets themselves. This blog will be cover how to retrieve data using a Smart View refresh function in VBA. For more information about Smart View Excel VBA functions, go to the Oracle Hyperion Smart View Developers Guide .  System Requirements ...
Read more

Loading Actuals from Fusion ERP Cloud to PBCS

Image
If you’re looking to set up a data load of Actuals from ERP to PBCS and have heard this is an “Out of the box” integration, then good news! Not only is the OOTB connector included within PBCS Data Management but the integration is simple to setup and to schedule using EPM Automate. This blog will detail how to setup the ERP to PBCS connector in Data Management and some of the common errors found when running the data load. The first thing to know about this integration is that the data source is actually the ASO Essbase cube which sits underneath the Fusion ERP tables, holding the period Actuals values. This ERP Essbase cube is created using job ‘Create General Ledger Balances Cube’ when configuring the ERP system. This cube must be created before the integration can be set up correctly and can be accessed directly via Smart View for reconciliations. Set up the Source System As this is an Oracle built connector, it’s as simple as providing a name for the source system ...
Read more