Datarails Flex Add-In: DR Formulas

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 in tandem with Datarails Functions and parameters 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 parameters provided.

To create a DR Formula, ensure the Datarails add-in is installed, and your Excel file is connected to your Datarails environment.

Primary DR Formulas

Here’s an overview of the most commonly used 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 (default: 12 months). "Revenue forecast between September last year and today." Varies

Aggregation periods can be customized using additional formulas like DR.RANGE, DR.MONTH, or Excel date functions.

Internal DR Functions

These functions refine the way data is retrieved and processed within DR Formulas. They allow for advanced customization and 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.FORECAST Returns the second argument if the first argument is "Forecast," otherwise returns "Null." DR.FORECAST("Actuals", "10+2 (2024)") = Null
DR.ROLLINGMONTHS Aggregates results over a rolling period (e.g., 12 months). DR.GET([Revenue], DR.ROLLINGMONTHS(DR.DATE("01/01/2023"), DR.DATE("01/01/2024")))

Notes:

  1. DR.RANGE and DR.INCLUDE cannot be used together in the same formula.
  2. The DR.INCLUDE is not supported in published items

Named Ranges

Named ranges provide dynamic values for frequently used fields or metadata in the Datarails environment. These ranges help simplify formulas by offering predefined values for various metadata.

DR Name Description Output Type
DR_FILEBOX_NAME Returns the name of the Filebox associated with the current file. Text
DR_LAST_REFRESH_ALL Displays 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, represented as a number. Number
DR_TAG_DATE_YEAR The year of the Filebox tag date, represented as a number. Number
DR_TAG_DATE_SOMONTH The first day of the month based on the Filebox tag date. Date
DR_COLLECTION_PROCESS Returns 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. Returns the name of the display field for a specific dynamic range. Text

Notes:

  1. Named ranges simplify the process of building formulas by providing quick access to frequently used metadata.
  2. They can be used as standalone references or combined with Datarails functions in formulas.

Aggregation Examples

To aggregate data over different periods, 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.ROLLINGMONTH(Value, "[Period]", DR.DATE(D6)) (default: 12 months).

DR Formulas with Excel Functions

DR Formulas work seamlessly with standard Excel functions, enabling further analysis. For example:

  • Average Expense: Use =AVERAGE() to calculate the average of a column containing DR.GET formulas.
  • Dynamic Reporting: Reference cells in your formulas for scalable and reusable calculations.

For more advanced use cases, check out the article on Using Date Ranges.



© Datarails Ltd. All rights reserved.

Updated

Was this article helpful?

0 out of 0 found this helpful

Have more questions? Submit a request

Comments

0 comments

Article is closed for comments.