Formulas are the foundation of how data is extracted into Excel using the Datarails Flex Add-in. By placing a formula in the formula bar, you can seamlessly connect your Excel environment to your organization's core systems and retrieve real-time data for analysis. This enables you to create dynamic, up-to-date reports directly within Excel.

Formulas Structure 

To bring data into Excel, a formula consists of three main components:

1. DR Formula
   The DR Formula initiates the connection to the database and specifies the type of data retrieval. It is placed at the beginning of the formula. Different DR Formulas support various data structures and aggregation methods. For more details, see the article on DR Formulas.

2. Function
   The Function defines what data to extract, where it comes from, and how it should be calculated. For example, the 'Value' function can sum amounts from a financial table or retrieve specific values based on your criteria. More about Functions is explained in the Functions article.

3. Fields and Values
   Fields and values enable you to customize your data extraction by specifying criteria such as timeframes, scenarios, account IDs, or other data points to filter and tailor the extracted data. You can also reference specific cells instead of manually entering values, making your formulas more dynamic. Additionally, you can drag formulas across multiple rows or columns to efficiently adapt them to your reporting needs.
   You can also use date ranges within fields and values to enhance flexibility, allowing you to extract data for broader or more specific periods as needed. Learn more about using Date Ranges in Formulas. 

Example Formula

Here’s an example of how these components work together:

=DR.GET(Value,"[Report_Field]","Interest Income","[Reporting Month]",31/12/2024,"[Scenario]","Actuals","[Account Group L2]","Income")

• DR Formula (DR.GET): Initiates data extraction and ensures all data from the database is accessible.
• Function (Value): Specifies the sum of the 'Amount' field from the Financials table.
• Fields and Values: Filters data based on specific criteria, like the Reporting month and Account Group.

Formula Builder

The Formula Builder is an interactive tool designed to make formula creation easier by allowing you to choose functions and drag relevant fields, eliminating the need to manually type complex formulas. This tool helps you construct accurate formulas with minimal effort.

Create a Formula using the Formula Builder 

1. Click on the Formula Builder button in the Datarails Flex Add-in ribbon to open its interface.

2. Choose the function that matches your data needs. For example, select the Value function to retrieve numerical values from your dataset.

3. Use the Formula Builder interface to add the necessary parameters. For example:

   • Specify a time period, such as a reporting month.
   • Add fields like scenarios, accounts, or departments to refine your data extraction.

4. Click on the corresponding cell that matches the added parameters.

5. You can either click 'Show cell formula' to reveal the formula or choose 'Copy to clipboard'. 

6. The Formula Builder will generate the corresponding DR.GET formula. Copy the formula directly from the Formula Builder to your Excel cell.

Building and Managing Formulas

The Datarails Flex Add-in includes tools to help you build and manage formulas with ease:

• Add Function: Create a new function to extract the desired value.
• Edit Function: Modify an existing function in the selected cell.
• Manage Functions: Open a list of functions to add, delete, clone, or edit them.

More about creating and editing Functions is explained in the Functions article.

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 December 29, 2024 17:05