Customising EPM File Exports with Groovy

For this blog we will be covering a common use case of exporting data from an EPM application to a file, for upload to a third-party system.

While it is possible to export data from EPM Planning using the “Data Export to File” functionality in Data Integration, the formatting options available are quite limited and typically would require some manual file manipulation before it can be uploaded. This is where groovy comes in!

In fact, to get the best of both worlds, we will be using a combination of both Data Integration AND groovy scripting, to produce a highly customised file format. This allows us to utilise the powerful mapping capabilities of Data Integration, as well as the file manipulation of groovy rules.

Step 1 – Export to File in Data Integration

For the first step, we will use the built in functionality to export data to a file. For details of how to do this, please refer to my previous blog here:

https://lydia-maksoud-epm.blogspot.com/2025/01/exporting-epm-planning-data-to-file.html

Step 2 – Apply groovy to customise the format

The second step will use groovy scripting within a business rule to pick up the csv file produced by step 1 (e.g. ExportRaw.csv) and apply the required changes to the file format.

Firstly, we need pick up the file and read each line into a map for referencing later:

    List<String[]> CSVRows = []

    csvIterator('ExportRaw.csv',",").withCloseable(){ reader ->

       reader.each { String[] values ->

          CSVRows << values

       }

    }

From here, we can reference the existing rows using this map and setup a NewCSV rows map to manipulate the existing rows into a new format.

In this example, the formats are below:

·         Existing Row format (6 columns) = Period, Years, Account, Site, Brand, Amount

·         New Row format (8 columns) = Period, Account Code, Name, Site / Branch Analysis Code, Description, Brand / Concept Analysis Code, Description, Base Amount

Note: the maps are 0 based start, so to reference the first field we use [0]. For example, to reference the first column (period dimension) in the new row format, we use NewRow[0].

The next bit of code will pick up each line of the existing csv format (referenced using ExistingRow) and transform it into the new file csv format.

    Integer RowCount = 1

    Integer NewRowCount = 1

    List<String[]> NewCSVRows = []

    CSVRows.each{ExistingRow ->

    if(RowCount == 1){RowCount = RowCount + 1}

    else{

    String[] NewRow = ["","","","","","","",""]

This section is an example where we can customise the Period formatting. In the new file, we need the period to be in format ‘2025/001’ for January, for example. To do this, we pick up ExistingRow[0] (Period column) and then use a combination of ExistingRow[1] (Year column) and the relevant suffix number.

    switch (ExistingRow[0]){

    case 'Jan': NewRow[0] = ExistingRow[1] + '/001'

    break;

    case 'Feb': NewRow[0] = ExistingRow[1] + '/002'

    break;

    case 'Mar': NewRow[0] = ExistingRow[1] + '/003'

    break;

    case 'Apr': NewRow[0] = ExistingRow[1] + '/004'

    break;

    case 'May': NewRow[0] = ExistingRow[1] + '/005'

    break;

    case 'Jun': NewRow[0] = ExistingRow[1] + '/006'

    break;

    case 'Jul': NewRow[0] = ExistingRow[1] + '/007'

    break;

    case 'Aug': NewRow[0] = ExistingRow[1] + '/008'

    break;

    case 'Sep': NewRow[0] = ExistingRow[1] + '/009'

    break;

    case 'Oct': NewRow[0] = ExistingRow[1] + '/010'

    break;

    case 'Nov': NewRow[0] = ExistingRow[1] + '/011'

    break;

    case 'Dec': NewRow[0] = ExistingRow[1] + '/012'

    break;

    }

In the below few lines, we move some existing columns into the correct position in the new file. We also use groovy functions to find the member alias and add it to the relevant column:

    NewRow[1] = ExistingRow[2] /* Account */

    NewRow[2] = AccountDim.getMember('A'+ExistingRow[2]).getAlias('Default') /* Account Description */

    NewRow[3] = ExistingRow[3] /* Site Code */

    NewRow[4] = EntityDim.getMember(ExistingRow[3]).getAlias('Default') /* Site Description */

    NewRow[5] = ExistingRow[4] /* Brand Code */

    NewRow[6] = ProductDim.getMember(ExistingRow[4]).getAlias('Default') /* Brand Description */

 

    if(RowCount == 1){}

    else{NewCSVRows << NewRow}

    RowCount = RowCount + 1

    }}

Lastly, we can then output the rows into a new CSV using the csvwriter function, as below:

    csvWriter('NEW_Export.csv').withCloseable(){csv ->

        csv.writeNext("Period","Account Code","Name","Site / Branch Analysis Code","Description","Brand / Concept Analysis Code","Description","Base Amount")

        NewCSVRows.each{row ->

                csv.writeNext(row)

            }

    }

}

We can then find the final CSV file in the inbox/outbox area, under Overview.

Happy coding!

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

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

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