Introduction

Welcome to Part 2 of the Groovy Rules Ideas series. In Part 1, we focused on orchestration and automation, patterns for reading substitution variables, running business rules, executing calc scripts, triggering DM loads, polling for job status, and running Data Maps.

• Groovy Lab –  Groovy Rules Ideas – part 1
• Groovy Lab – Managing Dates
• EPM Lab – Groovy Engine Upgrade & Validator Deep Dive
• EPM Groovy Lab – Bulk Update Member Names without Data Loss
• Groovy Lab – ASO Data Clear – Groovy & REST API
• Groovy Lab – Print Calculation Information in Job Log 
• Groovy Lab – Updating Attribute via Smart List – Groovy Time!
• Groovy Lab – Groovy in EPM Introduction (EPBCS & Hyperion)

In Part 2, we shift our focus to data interaction and user-facing techniques. These are the patterns you need when your Groovy rules interact directly with Planning forms, export data to files, manage user variables, and trigger reporting workflows.

We will cover six topics:

1. Rolling Forecast Logic
2. Reading Information from Planning Forms
3. Writing Data to CSV Files
4. Presetting User Variables Daily
5. Running Reports and Bursting Definitions
6. Sending Emails via Bursting Reports

Let’s dive in.

 

1. Rolling Forecast Logic

Why This Matters

Rolling forecasts are increasingly common in modern FP&A. Unlike traditional annual budgets that end at a fixed fiscal year-end, a rolling forecast covers a continuous window, typically 18 months forward, that advances each period. The challenge is dynamically calculating the start and end of this window without hardcoding.

This Groovy pattern calculates the rolling forecast window automatically based on the current forecast start month and year, distributing the window across fiscal years as needed.

How It Works

The logic determines an 18-month forward window from the current forecast starting point. It calculates how many months remain in the current year, then allocates the remaining months to the next year (and potentially a third year if the window spans that far). The result is a clear start period/year and end period/year.

Code Example

/* Define period and year lists from application */ int startYear, startMonth, endYear, endMonth int firstPeriod = allPeriods.indexOf(‘Jan’) int lastPeriod  = allPeriods.indexOf(‘Dec’)   /* Set starting point from forecast load parameters */ startYear  = allYears.indexOf(loadFcstYear) startMonth = allPeriods.indexOf(loadMonth) int nextYear = startYear + 1   int numPcurY, numPNextY1, numPNextY2   /* Calculate months remaining in current year */ numPcurY = 12 – startMonth   /* Distribute 18-month window across years */ if (18 – numPcurY > 12) { numPNextY1 = 12 numPNextY2 = 18 – numPcurY – numPNextY1 endMonth = numPNextY2 – 1 endYear  = startYear + 2 } else { numPNextY1 = 18 – numPcurY numPNextY2 = 0 endMonth = numPNextY1 – 1 endYear  = startYear + 1 }   /* Resolve member names */ String startP = allPeriods.get(startMonth) String startY = allYears.get(startYear) String endP   = allPeriods.get(endMonth) String endY   = allYears.get(endYear)   println “months curr Year: ”  + numPcurY println “months next Year1: ” + numPNextY1 println “months next Year2: ” + numPNextY2 println “start: ” + startP + ” ” + startY println “end: ”   + endP   + ” ” + endY

 

Key Takeaways

• The 18-month window is a common rolling forecast horizon, but you can adjust this to 12, 24, or any custom number of periods by changing the constant.
• The logic handles year-boundary crossings automatically, including scenarios that span up to three fiscal years.
• allPeriods and allYears are lists that map to your application’s dimension members. Populate these lists before running the logic.
• This pattern works well when combined with substitution variable retrieval (Part 1, Topic 1) and Data Map execution (Part 1, Topic 6) for a fully automated rolling forecast process.

 

💡 Tip Consider adding Run-Time Prompt (RTP) parameters for the start year and month, so users can run the rolling forecast from different starting points without modifying the script. You can also uncomment the RTP-based year calculation line in the code for this purpose.

 

2. Reading Information from Planning Forms

Why This Matters

One of Groovy’s most powerful capabilities in EPM Planning is the ability to read data directly from the current Planning form. When a Groovy business rule runs on save, it has access to the entire data grid – including all rows, columns, and cell values. This enables validation logic, conditional processing, and data-driven workflows.

This pattern is the foundation for many advanced use cases: validating user input before saving, reading approval flags, extracting transfer details, and more.

Code Example

/* Get the current data form grid */ DataGrid curgrid = operation.getGrid()   /* Initialize tracking variables */ String account = “” String currency = “” String entity = “”   /* Lists to collect approved items */ String account = [] String entity = [] String Date = [] String rowData = []

 

Iterating Through Grid Cells

Once you have the grid reference, you can iterate through cells to read values, check conditions, and collect data:

/* Iterate through data cells */ operation.grid.dataCellIterator().each { cell ->   /* Read dimension members for this cell */ String account = cell.getMemberName(“Account”) String entity  = cell.getMemberName(“Entity”)   /* Read cross-dimensional cell values */ String flag = cell.crossDimCell(      “flag”).getFormattedValue()   /* Only process flagged rows */ if (flag == “Yes”) {      account.add(          cell.crossDimCell(              “flagged Account”)              .getFormattedValue())      entity.add(          cell.crossDimCell(              “flagged Entity”)              .getFormattedValue()) } }

 

Key Takeaways

• operation.getGrid() returns the current form’s data grid. This only works for rules that are triggered from a form (on-save or on-demand from a form).
• getMemberName(dimensionName) retrieves the dimension member for the current row/column intersection.
• crossDimCell(memberName) lets you read a value from a different column in the same row – extremely useful for reading flags, dates, and reference data.
• Use getFormattedValue() to retrieve Smart List text values. Use getData() for numeric values.

 

⚠ Note The dataCellIterator only iterates through data cells, not header cells. If your form has merged cells or complex layouts, test the iteration behavior carefully to ensure you are reading the correct intersections.

 

3. Writing Data to CSV Files

Why This Matters

Exporting data from Planning forms to CSV files is a common integration pattern. Whether you are sending data to ERP system, generating reconciliation files, or producing audit exports, Groovy’s csvWriter makes it straightforward to create well-structured CSV files from form data.

This pattern combines form data reading (Topic 2) with file output to create a complete data export workflow.

Code Example

/* Write form data to a CSV file */ csvWriter(‘export.csv’).withCloseable() { out ->   /* Write the header row */ out.writeNext ‘Account’, ‘Info’, ‘Amount’,      ‘Date’, ‘Creator’   /* Track the current account to detect changes */ String account = “”   /* Iterate through data cells */ operation.grid.dataCellIterator().each { cell ->        /* Detect row changes */      if (cell.getMemberName(“Account”) != account) {          account = cell.getMemberName(“Account”)            /* Skip header rows */          if (account != ‘Account’) {                /* Look up attribute value */              def Mbr = Dim.getMember(account)              String account = Mbr                  .getAttributeValue(Attr).toString()                /* Format date from cell data */              def valueDate = formatDate(                  cell.crossDimCell(“Value Date”))                /* Only export flagged rows */              String approved = cell.crossDimCell(                  “flag”)                  .getFormattedValue()                if (approved == ‘Yes’) {                  out.writeNext(                      account,                      entity,                      cell.crossDimCell(                          “flagged account”)                          .getFormattedValue(),                      valueDate,                      Fcst_Creator                  )              }          }      } } }

 

Key Takeaways

• csvWriter() creates a CSV file in the application’s outbox. Use withCloseable() to ensure the file is properly closed after writing.
• writeNext writes a single row. The first call typically writes the header row, followed by data rows.
• Combine crossDimCell with getFormattedValue() for Smart List values and getData() for numeric values.
• Use getAttributeValue() to look up attribute dimension values for member enrichment.
• The exported CSV file can be retrieved from the Planning outbox using EPM Automate or the REST API for downstream processing.

 

💡 Tip For date formatting, consider building a reusable utility function that converts Planning’s internal date representation to your target format (YYYY-MM-DD, MM/DD/YYYY, etc.). This avoids repetitive substring logic throughout your code.

 

4. Presetting User Variables Daily

Why This Matters

User variables in Planning control which data slice a user sees on their forms. In applications with daily-level data, user variables like “Current Monday,” “Current Week,” and “Current Year” need to be updated automatically so that forms always show the right time period.

This Groovy pattern automates user variable assignment, eliminating the need for users to manually update their variables and reducing support requests.

Code Example

/* Configuration */ String cubeName = “FIN” String varMonday = “MondayforRTPDay” String varMondayYear = “YearforRTPDay” String varCurWeek   = “CurRTPWeek” String varPrevWeek  = “PrevRTPWeek”   /* Get cube and dimension references */ Cube cube = operation.application.getCube(cubeName) Dimension Daydim = cube.getApplication() .getDimension(‘Day’)   /* Resolve member objects */ Member dayMember  = Daydim.getMember(CurMonday) Member weekMember = Daydim.getMember(CurWeek) Member PrevweekMember = Daydim.getMember(PrevWeek)   Dimension Yeardim = cube.getApplication() .getDimension(‘Years’) Member yearMember = Yeardim.getMember(sCurYear)   /* Set user variable values */ operation.application.setUserVariableValue( operation.application.getUserVariable(varMonday), dayMember)   operation.application.setUserVariableValue( operation.application.getUserVariable(varMondayYear), yearMember)   operation.application.setUserVariableValue( operation.application.getUserVariable(varCurWeek), weekMember)   operation.application.setUserVariableValue( operation.application.getUserVariable(varPrevWeek), PrevweekMember)   /* Log results */ println “Current Day selected: ” + selectedDay println “User Variable ${varMonday} set to ${CurMonday}” println “User Variable ${varMondayYear} set to ${yearMember}” println “User Variable ${varCurWeek} set to ${CurWeek}” println “User Variable ${varPrevWeek} set to ${PrevWeek}”

 

Key Takeaways

• getUserVariable retrieves the user variable definition by name. setUserVariableValue assigns a dimension member to it.
• The member object must be a valid member of the dimension associated with the user variable. If the member does not exist, the rule will throw an error.
• This pattern is ideal for scheduled rules (via Job Scheduler or EPM Automate) that run daily to keep user variables current.
• Log each assignment for audit purposes and to help troubleshoot if a variable is set incorrectly.

💡 Tip Schedule this rule to run early each morning (for example, 6:00 AM) using the EPM Cloud Job Scheduler or EPM Automate. This ensures all users see the correct time period when they open their forms.

 

5. Running Reports and Bursting Definitions

Why This Matters

EPM Cloud supports bursting report definitions that can generate and distribute reports to multiple recipients automatically. Triggering these bursting jobs from Groovy allows you to integrate report generation into your automated workflows. For example, generating reports immediately after a forecast calculation completes.

Code Example

/* Get connection and substitution variables */ Connection conFin = operation.application .getConnection(“Connection”)   HttpResponse<String> jsonResponse = conFin.get( “/rest/v3/applications/FCST/substitutionvariables”).asString() Map<String,Collection<?>> response = new JsonSlurper().parseText(      jsonResponse.body) as Map   /* Execute a Bursting Definition job */ try { jsonResponse = conFin.post(      “/rest/v3/applications/FIN/jobs”)      .header(“Content-Type”, “application/json”)      .body(“”” {      “jobType”: “Execute Bursting Definition”,      “jobName”: “EPM Bursting Job”,      “parameters”: {          “burstingDefinitionName”:              “/Library/Admin Reports/Bursting report”      } } “””).asString()   println “Bursting job response: “      + jsonResponse.body   } catch (Exception e) { throwVetoException(“Bursting error: ${e}”) }

 

Key Takeaways

• The jobType for bursting reports is “Execute Bursting Definition” – this must be exact.
• The burstingDefinitionName is the full path to the bursting definition in the EPM Cloud report library.
• Like other REST API jobs, bursting runs asynchronously. Use the polling pattern from Part 1, Topic 5 to wait for completion if needed.
• Combine with substitution variable retrieval to pass dynamic parameters to the report.

 

⚠ Note Bursting definitions must be configured in advance in the EPM Cloud report library. The Groovy rule triggers the execution but does not create or modify the bursting definition itself.

 

6. Sending Emails via Bursting Reports

Why This Matters

A natural extension of running bursting reports (Topic 5) is sending those reports via email to stakeholders. EPM Cloud’s bursting functionality supports email distribution as part of the bursting definition. When you trigger the bursting job from Groovy, the email delivery happens automatically based on the bursting configuration.

This enables fully automated end-to-end workflows: run forecast calculations, generate reports, and email them to the finance team, all triggered from a single Groovy rule.

How It Works

Email delivery via bursting is configured at the bursting definition level, not in Groovy code. The Groovy rule simply triggers the bursting job (as shown in Topic 5), and the bursting definition handles the rest:

•   Who receives the email (distribution list configured in the bursting definition).
•   What format the report is delivered in (PDF, Excel, etc.).
•   What subject line and body text to include.
•   Whether to attach the report or embed it inline.

 

Typical Workflow

Here is a common end-to-end workflow that combines several patterns from both Part 1 and Part 2:

1. Read substitution variables to determine the current forecast period (Part 1, Topic 1).
2. Run business rules or Essbase calc scripts to update the forecast data (Part 1, Topics 2-3).
3. Execute Data Maps to push data to the reporting cube (Part 1, Topic 6).
4.  Trigger the bursting report definition (Part 2, Topic 5).
5. The bursting definition generates the report and emails it to configured recipients.
6. Poll for job completion and log the result (Part 1, Topic 5).

 

Key Takeaways

• Email distribution is configured in the bursting definition settings within the EPM Cloud report library, not in Groovy code.
• Groovy’s role is to trigger the bursting job at the right time, as part of a larger automated workflow.
• This approach eliminates manual steps: no one needs to remember to run reports or send emails after the forecast process completes.
• Test the bursting definition manually first to ensure email delivery works correctly before automating with Groovy.

💡 Tip Consider adding error notifications to your Groovy rule as well. If any step in the workflow fails, you can use throwVetoException to alert the user, or trigger a separate notification bursting job to email the admin team about the failure.

 

Summary

In Part 2 of this series, we covered six Groovy patterns focused on data interaction and user-facing techniques in Oracle EPM Planning:

• Rolling Forecast Logic – dynamically calculating forecast windows that span multiple fiscal years.
• Reading Form Data – iterating through grid cells to read values, flags, and cross-dimensional data.
• CSV Export – writing form data to structured CSV files for downstream integration.
• User Variable Automation – presetting user variables daily for consistent form behavior.
• Bursting Reports – triggering report generation via the REST API.
• Email Distribution – leveraging bursting definitions for automated email delivery.

 

Bringing It All Together

Across both parts of this series, we have covered 12 practical Groovy patterns that, when combined, enable you to build sophisticated, fully automated EPM workflows. The key insight is that Groovy is not just about individual rules – it is about orchestrating entire processes:

Prepare Read sub variables Calculate periods Set user variables

Process Load data (DM) Run calc scripts Execute rules/rulesets

Deliver Push via Data Maps Generate reports Email stakeholders

I hope this two-part series gives you practical ideas and code patterns you can adapt for your own EPM environments. As always, feel free to reach out with questions or share your own Groovy tips.

 

More to come in future Groovy Lab posts.