We use Datarails formulas to show specific data points from your database in Excel. All formulas have the same structure:

Datarails Formulas begin with the  Formula Name which indicates how we aggregate the data. In this example, we aggregated all values from the start of the year to a selected date. To review all available formulas, refer to this Help Center page. This Help Center page also reviews Named Ranges which we will cover in the Certificate Level 2 course.

The Function specifies which column will be used to present the value that we want to display.  In this example, it’s the Dollar amount.

Functions are a required component of every Datarails formula. They specify which column will be used to present the value that we want to display in our Excel reports. 

For example, your database may have columns that include amounts for expenses, employee counts for headcount reporting, and stock counts for inventory reporting. Clicking on the Function button will allow you to specify which of these columns you would like to show in your Excel report. 

The following video clip shows how to add a Function.

Video: Adding a Function

Let's take a closer look at Default Aggregation.

Datarails has formulas that aggregate, such as year-to-date (DR.YTD), or quarter-to-date (DR.QTD). Since your dataset may have multiple date columns, you need to specify which date field should be used to do the aggregation. 

In the example shown in the above video, there are five date fields (Submission Date, System_Date, Posting Date, and Reporting Month). In this case, we want to aggregate by "Reporting Month", because our P&L reports use the same logic. 

Next, formulas require Parameters that help define what to show in the report. You can compare these parameters to filters. In the example at the top of this article, the term in [square brackets] is the Column, and next to it is the associated Row that specifies what to filter for. Datarails knows that it should look for the row labeled “12/31/2021” in the column labeled “Period” because in the formula the row is listed right next to its column, separated by a comma. 

The parameters can be listed in any order. In other words, this means that the parameter { "[Account Name]","Revenue - Product" } could also be listed before the parameter {"[Period]","12/31/2021" }.

In essence, the formula says the following: 

The formula in our example above is static. In other words, it doesn’t change when you copy it into a new cell. 

We can make it dynamic by turning the Rows into cell references. That way, we can copy it into other cells and thereby easily add additional data points to the report. 

This is how the formula from our example looks like with dynamic cell references:

It picks up the “Period” from cell A1 and the “Account Name” from cell B9. The $-signs indicate which parts of the cell references are kept static. If you copy this formula into a different cell, then the Period reference will move to the right to row 1 and the account name moves down to  column B. 

The result of setting formulas up in this way reflects a common layout of Financial reports:

Editing Datarails formulas

The easiest way to make changes to an existing formula is to use the Add Parameter feature. You can select which rows and columns to include as parameters from a simple drop-down menu. That means you don’t need to memorize any formula syntax.

Try it now: Editing Formulas practice

1. Open the Excel file “P&L - Practice Report”. It’s located in a separate Datarails environment, so you can use it to practice without impacting your existing reports.

   Click here for instructions on how to open the practice report.

2. In the tab “P&L” select cell E8
3. Right-click on the formula bar and select Datarails > Parameters as shown in the video below.

4. Select a "Customer" in the list on the left. Wait a moment until you see the rows in the box on the right. Select the first row ("Customer 1").

5. Click “Ok” to add the Parameter to the formula. Note the parameters can be listed in any order. 

6. Click Refresh. Cell E8 now shows Income filtered for "Customer 1".

Custom aggregations

In certain situations, you might need to aggregate data across a range. For example, you may want to show your company’s financial performance since you increased your prices. Or you might want to show the revenue of only your two biggest products.

You can use the Datarails formula DR.INCLUDE to achieve this.

Continuing with our formula example above, let’s say we want to aggregate the accounts “Travel”, and “Marketing Expenses”. In that case, the  formula would look like this:

=@DR.YTD(Value,"[Period]",A$1,"[Account Name]",DR.INCLUDE(“Travel”,”Marketing Expenses”))

You can also access the DR.INCLUDE formula via the Parameters menu as explained above.

Note: If you aggregate specific data points on a regular basis, then it’s best practice to group them via a Look-Up Table. That way, your formulas are shorter and can be more dynamic. We will cover that in the Web Environment module of this course.

Good to know: You can add regular Excel formulas to reports that include Datarails formulas.

For example, you can create profitability ratios with a simple division in Excel that reference cells with Datarails formulas.

Previous: Excel Add-In

Next: Report Builder

Updated September 19, 2023 12:36