DataRails formulas are Excel formulas that users place within Excel cells to initiate data extraction.

The most recognized DataRails formula is "DR.GET()". To create a Datarails formula, please ensure that you have the necessary Datarails function added to your spreadsheet. To read more about Datarails formulas and Datarails functions, please refer to this article. 

Once the function is in the workbook, you can create formulas that will update your report dynamically. To do this, you will use Formula Builder. There are two ways to open it:

Open with Right Click

1. Right click the cell where you added the function.
2. Select Datarails > Functions > Formula Builder. The **Formula Builder ** dialog opens.

Open from the Datarails Ribbon

1. Click Functions.
2. Select Formula Builder

Use Formula Builder to Create Dynamic Formulas

In this example, we’re creating a dynamic Cash Flow report for Jan and Feb of the given year.

1. Open Formula Builder.
2. Add the elements you want e.g.,
   • Date in the Axis box. You will see grand totals for each date period.
   • Category in the Legend box.
   • Value Type. Add to the Axis Box. In this Cash Flow report, we’ve added a filter to include only ‘Ending Balance’.
3. Next, copy the formula into Excel. Right click a cell with data and click Copy to Clipboard. Your formula will look something like this:
4. =DR.GET (CashFlow,"[Period]", DR.DATE(“01/31/2021”),"[Value Type]", “Ending Balance”,"[Title]",“Accounts Payable”)
5. To make the formula dynamic, replace hard-coded data with cell references in your report (i.e. DR.DATE("1/31/2021") or "Accounts Payable" can be replaced with the cell reference).

Example for columns Jan 2021 and Feb 2021.

The bold cell references are now dynamic. Note that the references are ‘locked’ ($).

=DR.GET (CashFlow,"[Period]", DR.DATE(D$6),"[ValueType]","EndingBalance","[Title]",$A11

=DR.GET (CashFlow,"[Period]", DR.DATE(E$6),"[ValueType]","EndingBalance","[Title]",$A12).

1. To update formulas, click Refresh on the ribbon.
2. Check your values before moving on. Correct the cell references as needed.
3. Copy the cells for both columns to include all the categories in the report.

Troubleshoot

Problem

I see the word “Missing” in a cell.

Solution

Click Refresh to update the functions in the report.

Understanding Datarails Formulas / Functions

DR.GET stands for Datarails

GET is the syntax for the function.

CashFlow is the name of the function

Parameters are the rest of the formula, and because they use cell references, they are dynamic. You can have as many parameters as needed.

Here’s a breakdown of the sample function used in this article.

=DR.GET (CashFlow,"[Period]", DR.DATE(D$6\ast \ast ),"[ValueType]","EndingBalance","[Title]",\ast \ast$A11)

[Period]", DR.DATE(D$6\ast \ast ),"[ValueType]","EndingBalance","[Title]",\ast \ast$A11

Formulas for QTD, YTD and Rolling Month

Replace DR.GET with the relevant period.

The Rolling Month default is 12 months, unless you change it in the configuration.

Missing Functions

When you change data, restructure layouts, or add new dimensions, the data model needs to be updated. If there are missing functions or formulas, when you click Refresh a dialog will show listing the items that not connected.

1. To refresh and make the connections, click Connect / Connect All.
2. Click Close.

Aspose Functions

Datarails supports Aspose functions. The Aspose Cells' Formula Calculation Engine allows you to set, read and calculate the results using many formulas and functions. Click here to see the Aspose documentation for the list.

Updated November 13, 2023 10:15