DR formulas are the core component of Datarails formulas, used to extract data from your organization's systems directly into Excel. They serve as the query, initiating data retrieval and defining how the data is processed.
DR formulas are Excel-based formulas that connect to the Datarails database. They work together with Datarails functions and conditions to fetch, calculate and display real-time data. The most commonly used DR formula is DR.GET(), which returns specific data points or aggregated results based on the conditions you provide.
To use a DR formula, make sure the Datarails add-in is installed and your Excel file is connected to your Datarails environment.
For how a DR formula fits into a complete formula, see Formulas.
Primary DR formulas
| Formula | Description | Example | Output type |
| DR.GET | Retrieves a specific data point without aggregations | "How much did we spend on marketing last month?" | Varies |
| DR.MTD | Aggregates data from the first day of the month to a given date | "Revenue from the beginning of the month until today." | Number |
| DR.MTG | Aggregates data from a given date to the end of the month | "SG&A expense forecast for the remainder of the month." | Number |
| DR.QTD | Aggregates data from the first day of the quarter to a given date | "Salary expense from January 1 to February 24." | Number |
| DR.QTG | Aggregates data from a given date to the end of the quarter | "Travel expense forecast for the remainder of the quarter." | Number |
| DR.YTD | Aggregates data for the year to date | "Sales year to date." | Varies |
| DR.YTG | Aggregates data for the remainder of the year | "Sales forecast from today until the end of the year." | Varies |
| DR.ROLLINGMONTHS | Aggregates data over a rolling period, 12 months by default | "Revenue forecast between September last year and today." | Varies |
Aggregation periods can be customized using other additional functions, such as DR.RANGE and DR.MONTH. or Excel date function. See Internal DR functions below.
Internal DR functions
These refine how data is retrieved and processed within a DR formula, allowing more advanced filtering of your data.
| Function | Description | Example |
| DR.DATE | Converts a date into an Excel-compatible date format | DR.DATE("01/31/2022") = 44592 |
| DR.RANGE | Defines a range of dates or numbers for filtering data | DR.GET(Amount, "[Period]", DR.RANGE(DR.DATE("01/01/2024"), DR.DATE("08/31/2024"))) |
| DR.MONTH | Filters data for a full month | DR.GET([Reporting Month], DR.MONTH(31/01/2024)) |
| DR.QUARTER | Filters data for a full quarter | DR.GET([Reporting Month], DR.QUARTER(31/03/2024)) |
| DR.YEAR | Filters data for a full year | DR.GET([Reporting Month], DR.YEAR(31/12/2024)) |
| DR.INCLUDE | Combines specific values into a single filter | DR.GET([Month], DR.INCLUDE("Jan", "Feb")) |
| DR.TEXTSPLIT | Combines comma-separated values from a cell into a single filter | DR.GET([Month], DR.TEXTSPLIT(A1)) where A1 contains Jan, Feb, March |
| DR.FORECAST | Returns the second argument if the first is "Forecast", otherwise returns Null | DR.FORECAST("Actuals", "10+2 (2024)") = Null |
| DR.ROLLINGMONTHS | Aggregates results over a rolling period, for example 12 months | DR.GET([Revenue], DR.ROLLINGMONTHS(DR.DATE("01/01/2023"), DR.DATE("01/01/2024"))) |
Limitations
- DR.RANGE and DR.INCLUDE cannot be used together in the same formula
- Two instances of DR.RANGE or DR.INCLUDE are not supported in the same formula
- DR.INCLUDE is not supported in embedded ranges
Combining DR.INCLUDE and DR.TEXTSPLIT
To combine a fixed value with several values held in a cell, nest DR.TEXTSPLIT inside DR.INCLUDE:
DR.INCLUDE("Sales", DR.TEXTSPLIT(A1))
If A1= “COGS, OPEX” this resolves to DR.INCLUDE("Sales", "COGS", "OPEX").
Named ranges
Named ranges provide dynamic values for frequently used fields and metadata from your Datarails environment. They simplify formulas by giving you predefined values to reference.
| Named range | Description | Output type |
| DR_REFERENCE_DATE | The reference date currently set in the task pane. Files built before the rename use DR_DATE_PICKER, which still works and holds the same date. | Date |
| DR_FILEBOX_NAME | The name of the Filebox associated with the current file | Text |
| DR_LAST_REFRESH_ALL | The last refresh date and time of the file | Date-time |
| DR_TAG_DATE | The date associated with the Filebox tag | Date |
| DR_TAG_DATE_MONTH | The month of the Filebox tag date, as a number | Number |
| DR_TAG_DATE_YEAR | The year of the Filebox tag date, as a number | Number |
| DR_TAG_DATE_SOMONTH | The first day of the month, based on the Filebox tag date | Date |
| DR_COLLECTION_PROCESS | The name of the collection process, if the file is connected to one | Text |
| DR_[DYNAMIC_RANGE_ID]FIELD[I] | Relevant for Dynamic ranges only. The name of the display field for a specific dynamic range. | Text |
Named ranges can be used on their own, or combined with Datarails functions inside a formula.
Aggregation examples
To aggregate data over a different period, replace DR.GET with the relevant formula:
- Quarter: DR.QTD(Value, "[Period]", DR.DATE(D6))
- Year: DR.YTD(Value, "[Period]", DR.DATE(D6))
- Rolling months: DR.ROLLINGMONTHS(Value, "[Period]", DR.DATE(D6))
Rolling months defaults to 12 months.
Using DR formulas with Excel functions
DR formulas work alongside standard Excel functions, so you can build further analysis on top of them:
- Average expense: Use =AVERAGE() to calculate the average of a column containing DR.GET formulas.
- Dynamic reporting: reference cells inside your formulas, so a calculation can be reused and scaled.
For more advanced cases, see Date Ranges in Formulas.
Working across platforms
DR formulas behave identically on Windows desktop, Mac desktop and Excel on the web. A workbook built on one opens and recalculates the same way on the others.
© Datarails Ltd. All rights reserved.
Updated
Comments
0 comments
Please sign in to leave a comment.