Use Excel Add-In formulas to retrieve reports, data values, Business Activity Queries (BAQs), journal transactions, dimension properties, and DataClass data directly in Microsoft Excel. These formulas allow dynamic retrieval of Epicor FP&A data and support both fixed values and cell references.
In this article, we will cover:
Available Formulas
The Excel Add-In supports the following formulas:
| Formula | Purpose |
|---|---|
| EFPA.GETBAQ | Retrieves Business Activity Query (BAQ) results. |
| EFPA.GETBAQLIST | Returns a list of available BAQs. |
| EFPA.GETDATASET | Returns a dataset of values. |
| EFPA.GETDC | Downloads DataClass data. |
| EFPA.GETVALUE | Retrieves a single value. |
| EFPA.GETPROPERTY | Retrieves a dimension property value. |
| EFPA.GETGL | Retrieves General Ledger journal data. |
| EFPA.GETREPORT | Inserts a report into Excel. |
Retrieving BAQ Data
Use EFPA.GETBAQ to retrieve Business Activity Query results.
Syntax
EFPA.GETBAQ(BAQName, Parameters)
Required Parameter
| Parameter | Description |
|---|---|
| BAQName | Name of the BAQ. |
Example
=EFPA.GETBAQ("COM-CustContacts")
Filter BAQ Results
If the BAQ supports parameters, include them in the formula.
Example:
=EFPA.GETBAQ("GLJrnlDtlByBookByCompany","FYear=2013","FPeriodList=1|FPeriodList=2")
Date Parameters
For multiple DateTime parameters, use:
=EFPA.GETBAQ("LAC_ICAP","StartDate=2025-10-01|EndDate=2025-10-31")
No Results Returned
If the BAQ cannot be found, the formula returns an error message.
List Available BAQs
Use EFPA.GETBAQLIST to retrieve available Business Activity Queries.
Example
=EFPA.GETBAQLIST()
Retrieve a Dataset
Use EFPA.GETDATASET to retrieve a grid of values from a DataClass.
Required Parameters
| Parameter |
|---|
| DataClass |
| Entity |
| Account |
| Scenario |
| Time |
| TimeView |
Optional Parameters
Any additional dimensions can be supplied as filters.
Example
=EFPA.GETDATASET("Financials","All_","Income_Statement_","ACTUAL","201801","PERIODIC","BusinessProcess=CONSOLIDATED")
Retrieving DataClass Data
Use EFPA.GETDC to download DataClass records into Excel.
Required Parameter
| Parameter |
|---|
| DataClass |
Optional Parameters
| Parameter | Description |
|---|---|
| Filter | Restricts returned records. |
| OrderBy | Sort order. |
| Rows | Maximum number of rows returned. |
| Show Detail | Boolean Value |
Example
=EFPA.GETDC("Financials","Account=Financials_|BusinessProcess=CONSOLIDATED","Account DESC", "2000",FALSE)
Retrieving a Single Value
Use EFPA.GETVALUE to retrieve a single value from a DataClass.
Required Parameters
| Parameter |
|---|
| DataClass |
| Entity |
| Account |
| Scenario |
| Time |
Optional Parameters
Additional dimensions can be supplied as filter expressions.
Basic Example
=EFPA.GETVALUE("Financials","All_","Income_Statement_","ACTUAL","201801","PERIODIC")
Example with Additional Dimensions
=EFPA.GETVALUE("Financials","All_","Income_Statement_","ACTUAL","201109","NONE","Flow=All_|BusinessProcess=CONSOLIDATED|BusinessRule=Al
Using Cell References
You can reference worksheet cells instead of hard-coded values.
Example:
=EFPA.GETVALUE("Financials",$D$6,$A13,"ACTUAL",$D$7&TEXT(D$10,"00"),"Periodic",L23)
Use Dynamic Filters
Example:
=CONCATENATE("GL_Division=",$D11)
This formula can be referenced from the GETVALUE filter parameter.
Using Custom Account Hierarchies
GETVALUE supports custom Account hierarchies.
Hierarchy Syntax
Specify custom hierarchy members using:
HierarchyName:AccountCode
Default hierarchy members are specified without a prefix.
Example
=GETVALUE(dataClass, entity, "201000, Custom:202000", scenario, time, timeView, filters)
Result
The formula retrieves:
Account 201000 from the default hierarchy
Account 202000 from the Custom hierarchy
Retrieving Dimension Properties
Use EFPA.GETPROPERTY to retrieve dimension member properties.
Required Parameters
| Parameter |
|---|
| Dimension |
| Member |
| Property |
Example
=EFPA.GETPROPERTY("Account","Income_Statement_","Sign")
Nested Example
You can nest GETPROPERTY functions.
=EFPA.GETPROPERTY("Account",EFPA.GETPROPERTY("Account",MID(B4,1,4),"Parent"),"HelpText")
This example:
Extracts an account code from a worksheet cell.
Retrieves the parent account.
Retrieves the HelpText property from the parent account.
Member Description Recommendation
Use:
=EFPA.GETPROPERTY("Account","D_","MemberDescription")
instead of retrieving the Description property.
Retrieving Journal Data
Use EFPA.GETGL to download General Ledger journal data.
Required Parameters
| Parameter |
|---|
| Entity |
| At least one filter parameter |
Example
=EFPA.GETGL("EPIC01","FiscalYear=2021")
Optional Parameters
Examples include:
Book
FiscalYear
Account
Any available journal column
Retrieving Reports
Use EFPA.GETREPORT to insert report output directly into Excel. The formula expands into adjacent rows and columns.
Required Parameter
| Parameter |
|---|
| Report Name |
Optional Parameters
Any dimension in the application instance can be used as a filter, including:
Account
Currency
Entity
GL_Department
Time
Example
=EFPA.GETREPORT("P&L 12 Months","Time=2021")
Example with Multiple Filters
=EFPA.GETREPORT("Test 711","Currency=USD|Time=202109|Entity=USA")
Replacing Formulas with Values
Use Replace when you need to share a workbook with users who should see only calculated values and not Epicor formulas.
Replace Formulas
Open the workbook.
-
Select Replace.
The system converts all formulas on all worksheets into their calculated values.
This action cannot be undone. Create a copy of the workbook before replacing formulas.
Cache Behavior
The following formulas use a per-user cache that remains valid for 24 hours:
GETBAQ
GETDC
GETGL
GETPROPERTY
GETVALUE
Best Practices
Use cell references when creating reusable templates.
Use custom hierarchy prefixes when retrieving data from non-default Account hierarchies.
Use MemberDescription instead of Description when retrieving account descriptions.
Create a backup workbook before replacing formulas with values.
Use GETREPORT when a full report layout is required and GETVALUE when only a single value is needed.