The Datarails Flex Add-in enhances Excel with advanced data management and reporting tools, enabling seamless data connections, management, and dynamic report creation. Key features include data management, powerful reporting tools, and an intuitive interface.
Start by visiting the Installation Guide, then explore the new Excel tab, which contains essential tools for managing data and reports. This article provides a brief overview of each feature and links to detailed guides.
Main Features
- Connect
- Submit
- Refresh
- Drill Down
- Report Builder
- Functions
- Data Tables
- Dynamic Ranges
- Filters
- Lists
- Reference Date
- Roll Forward
- Embedded Range
- Export
- Info
- Errors & Notifications
Connect
The Connect button allows you to link a local Excel file to a Filebox in Datarails, enabling you to store and manage it directly within your Datarails environment. To upload and organize a locally saved file, follow these steps:
- Click 'Connect' to open the 'Connect as Filebox' window.
- Select the desired path to create a new Filebox and complete the required fields.
- Click 'Connect to Filebox' to add the new Filebox to the selected destination.
Submit
The 'Submit' button sends your updates from Excel directly to Datarails, ensuring that your changes are immediately reflected in the connected data source. Use it to keep your data current and synced.
Refresh
The Refresh button keeps your Excel workbook up-to-date with the latest data from Datarails, syncing any changes made to datasets, mappings, and groupings.
If the database is updated, the refresh button will indicate that new data is available. A missing icon means new data was added to the table, and you need to click refresh to see it in the report.
- Refresh: Updates all data sources and recalculates all tables in the worksheet, ensuring all data is current.
- Selected Cells (via the arrow icon): Updates only the selected cells for a quicker refresh.
- Selected Tables (via the arrow icon): Refreshes data within specific tables, ideal for larger datasets.
Drill Down
The Drill Down button lets you dive deeper into your data by breaking down aggregated figures into detailed components. It offers two types: one for analyzing specific values and another for viewing a full list of details. You can also save frequently used drill-downs as favorites for quick access. For more information, see our detailed Drill Down capabilities article.
Report Builder
The 'Report Builder' button enables you to create custom reports tailored to your data analysis needs. From this button, you have two options:
- Create a Report Builder – Design reports using our comprehensive tools. For detailed instructions, refer to our Create a Report Builder Article.
- Manage Report Builder Dynamic Settings – Adjust dynamic ranges and settings to fine-tune your reports, which is equivalent to managing dynamic ranges in the Report Builder.
Functions
To understand how functions work in Datarails, it's essential to know that data is brought into Excel through formulas. A complete formula consists of three main components: the DR Formula, the Function, and the fields with their corresponding values.
- DR Formulas: These are Datarails-specific formulas placed at the beginning of the formula to initiate data extraction. Different DR Formulas bring data in various aggregations. You can learn more about them in the DR Formulas article.
- Function: The function specifies the value, source table, and the type of calculation. For instance, the 'Value' function sums the 'Amount' from the Financials table, helping to define what data you need.
- Fields and Values: These represent the specific data points you want to pull, such as date ranges, scenarios, or account IDs, tailoring the data extraction to your needs.
Example Formula: =DR.GET(Value, "[Reporting Month]", 31/08/2024, "[Scenario]", "Actuals", "[Data Type]", "Activity", "[Account ID]", 10000)
- DR Formula (DR.GET): Extracts all data from the database at all times.
- Function (Values): Sums the 'Amount' from the Financials table.
- Fields and Values: Specify criteria like the reporting month and account ID to fetch relevant data.
Using the 'Functions' button:
- Add Function: Create a new function to define the value you want to extract.
- Edit Function: Modify an existing function in the selected cell.
- Manage Functions: Open the function list to add, delete, clone, or edit functions.
- Formula Builder: This tool builds formulas by dragging and dropping functions and relevant fields.
If you want to explore functions and formulas more, refer to the article Creating a Formula.
Data Tables
A Datarails Excel Table is a filtered extract of your data stored in the Datarails database. This feature allows you to filter and import your data directly into any Excel file, making it easy to work with the exact subset of data you need for your analysis and reports.
Using the 'Data Tables' button:
- Add Table: Create a new table by setting filters and options to import targeted data from the Datarails database into your Excel file.
- Edit Table: Adjust the existing table's filters, columns, and settings to refine your data view.
- Manage Table: Access the table management panel to add, delete, clone, or edit tables, providing full control over your data extracts.
For a detailed guide on how to work with Excel tables, refer to this article on Data Tables.
Dynamic Ranges
Dynamic Ranges keeps your reports up-to-date by automatically adjusting to changes in your database, eliminating the need for manual updates. Unlike static ranges, Dynamic Ranges ensure that your reports always reflect the most current data, maintaining accuracy and completeness.
For example, when new groupings are added to your data, Dynamic Ranges automatically incorporate these changes, providing a comprehensive and up-to-date view.
Using the 'Dynamic Ranges' Button:
- Add Dynamic Range: Set up ranges that automatically update as new data becomes available.
- Manage Dynamic Ranges: Edit, add, or delete dynamic ranges to maintain full control over how your data is displayed.
For more details, refer to the Dynamic Ranges Overview article.
Filters
Filters allow you to slice and dice your data in Excel, helping you quickly navigate consolidated views to find your numbers efficiently. Filters can be applied across any dimension in your datasets, with options to create table or global filters to refine your reports dynamically. The applied filters can be found in the Filters section within the Datarails add-in.
For more details on adding filters and lists to your reports, refer to the Adding Filters & Lists to Your Reports article.
Lists
Lists are dropdowns drawn directly from a field within a Datarails table, similar to an Excel data validation list. They are useful when you need to reference a specific selection in a function within your workbook or when you want to update data in an individual tab without affecting the entire workbook, unlike global filters.
For more information on adding lists to your reports, refer to the Adding Filters & Lists to Your Reports article.
Reference Date
The Reference Date (formerly known as Date Picker) is a tool that sets the reference date for your reports, making them dynamic by allowing you to adjust the reporting period directly within your Excel workbook. The Reference Date serves as your guide for selecting and changing the report's date, enabling you to work through historical periods or the most recent data available.
The Reference Date is a powerful way to make your reports adaptable and responsive to various time frames, ensuring your analysis stays relevant.
Roll Forward
The Roll Forward feature allows you to effortlessly update your reports to the latest period, keeping your data current without the need for manual adjustments in Excel. This option automatically updates dates, calculations, and data selections in your report to reflect the newest period available in Datarails, saving you time and minimizing errors.
Key Benefits:
- Automatic Updates: Instantly shift your report to the latest period, aligning all data and formulas with the most recent dates.
- Consistent Reporting: Maintain consistent report structures while ensuring your analysis always uses up-to-date information.
Located in the Datarails Flex ribbon, the Roll Forward functionality streamlines the process of keeping your reports relevant and accurate with just a few clicks.
Embedded Range
The Embedded Range (formerly known as Published Range) feature lets you effortlessly share your data by publishing selected ranges from your Excel workbook directly to a Datarails dashboard. This streamlines the process of keeping your presentations and dashboards up-to-date with the latest insights.
- Embedded Range to Dashboard: Share data ranges directly to your Datarails dashboard for real-time analysis and reporting.
For step-by-step instructions, refer to the Embedded Range article.
Export
The Export feature provides flexible options for sharing your reports. It allows you to distribute your data from Datarails in various formats while maintaining control over presentation and accessibility.
To use the Export function, the file must be connected to the Datarails environment.
- Export to PDF: Convert your reports into PDF format for easy sharing and viewing without editing capabilities.
- Export as Values: Share your data as static values, ensuring data integrity by removing formulas.
- Split Reports by Filters: Generate customized versions of your report based on specific filters to tailor views for different audiences.
For more details on sharing your Datarails reports, refer to the Export Reports article.
Info
The Info section in the add-in provides key details about your file, including:
- Filebox Name (found under Process).
- Version tags, such as Scenario (e.g., Scenario = "Actuals") and Date (e.g., Year = 2024).
- An indicator showing if this is the latest version.
Errors & Notifications
The Errors & Notifications feature provides real-time feedback on the health of your reports. A green indicator shows everything is functioning correctly, while a red indicator alerts you to errors, highlighting the details to help you quickly identify and resolve any issues.
This feature ensures you are always aware of your report status, enabling prompt action to maintain accuracy and functionality.
Stay informed about your report status and take prompt action to maintain accuracy and functionality. For more information on resolving errors, refer to our Errors & Troubleshooting Guide.
File errors
When the add-in finds a problem in the file, a File errors notification appears at the top of the task pane. It only shows when there is at least one error. Click it to open the File errors screen, which lists each problem the add-in found in this file. Each row shows the error name, a short explanation, and a help link. Some errors also have a Resolve button, which fixes the problem for you. Double-click a row to jump to the affected cell, where there is one.
Each error is explained in the article for the feature it belongs to:
| Error | Where to find the fix | |:-|:-| | Function deleted | Formulas: Function deleted | | Function failed to refresh | Formulas: Function failed to refresh | | Function syntax error | Formulas: Function syntax error | | Data source unavailable | Formulas: Data source unavailable | | Table failed to refresh | Data Tables: Table failed to refresh | | Missing Function / Table | Data Tables: Missing Function / Table | | Filter error | Filters: Filter error | | Drill down failed | Drill Down: Drill down failed | | Duplicated name ranges | Duplicated name ranges, below | | Protected workbook | Protected workbook, below | | Broken named ranges | Broken named ranges, below | | Protected control sheet | Protected control sheet, below | | Named ranges linked to other workbooks | Named ranges linked to other workbooks, below | | The selected currency is missing from the table | Formulas: The selected currency is missing from the table | | Metric deleted | Formulas: Metric deleted | | Dynamic range failed to refresh | Dynamic Ranges: Dynamic range failed to refresh | | Report failed to refresh | Report Builder: Report failed to refresh |
Duplicated name ranges
In the add-in: "One or more of your function or table names is duplicated — usually from copying sheets — which can affect your results. Click Resolve to delete the duplicates."
Why it happens: Datarails keeps its functions and tables in named ranges that belong to the whole workbook. When you copy a sheet that contains Datarails formulas or tables, Excel creates a copy of those names that belongs only to the new sheet. On that sheet, the copy hides the workbook name, so formulas there may return the wrong values.
How to fix it:
- In the File errors screen, click Resolve on the Duplicated name ranges row. The add-in deletes the sheet-level copies and keeps the workbook names.
- Click Refresh and check your numbers.
If Resolve fails, the workbook is probably protected. Fix Protected workbook first (below), then click Resolve again.
To delete the duplicates by hand, go to Excel's Formulas > Name Manager, sort by Scope, and delete the entries that start with DR_ and are scoped to a sheet rather than to the Workbook.
Protected workbook
In the add-in: "This workbook is protected. Unprotect it from the Review tab, or click Resolve, then refresh."
Why it happens: Datarails saves its data and settings inside the workbook. When the workbook structure is protected, it can't, so refresh and submit don't work correctly.
How to fix it:
- No password: click Resolve on the Protected workbook row, then click Refresh.
- With a password: Resolve can't enter the password for you, and will show "Failed to resolve". In Excel, go to Review > Protect Workbook, enter the password, and click OK. Then click Refresh.
You can protect the workbook again when you're done. You'll need to unprotect it again the next time you work with Datarails in this file.
Broken named ranges
In the add-in: "One or more of your names has an invalid range reference (#REF!). Click Resolve to remove them."
Why it happens: some Datarails names in the workbook point to cells that no longer exist, usually because a row, column or sheet was deleted. Names like these can break formulas and refresh.
How to fix it:
- In the File errors screen, click Resolve on the Broken named ranges row. The add-in deletes only the broken Datarails names (the ones that start with
DR_). It doesn't touch your own names. - Click Refresh and check your numbers.
If Resolve fails, the workbook is probably protected. Fix Protected workbook first, then click Resolve again.
Protected control sheet
In the add-in: "The Datarails control sheet is protected, so the configuration couldn't be saved. Click Resolve to unprotect it."
Why it happens: Datarails keeps the file's settings on a hidden sheet. When that sheet is protected, the add-in can't save changes to its settings.
How to fix it:
- In the File errors screen, click Resolve on the Protected control sheet row.
- Click Refresh.
If the add-in says the sheet is password-protected, contact Support with the file name.
Named ranges linked to other workbooks
In the add-in: "[number] named range(s) reference another workbook; Excel tries to reach it on every open"
Why it happens: the workbook has names that point to another Excel file, usually after copying sheets from another file. Excel tries to reach that file every time the workbook opens. If it can't find it, opening can be slow, and on Excel for Mac it can stall before the add-in loads.
How to fix it:
- In the File errors screen, click Resolve on the row. The add-in deletes the names that point to other workbooks.
- Click Refresh and check your numbers.
If a formula of yours really does need data from the other file, check it after Resolve and reconnect it with Excel's Data > Edit Links.
© Datarails Ltd. All rights reserved.
Updated