Datarails Connect offers a robust solution for extracting data from your organization's systems into Excel through the use of formulas. These formulas are pivotal for pulling the necessary data with precision, and the process is facilitated by a wizard comprising four straightforward steps. Let's delve into each step to understand how to efficiently create formulas in Datarails Connect.

Step One & Two: Selecting System and Table/Report

The initial steps of the wizard involve selecting the system and table or report from which you intend to query data. Here's how to navigate through these steps effectively:

• Choose the System: Identify the system you wish to query from the provided options. If the desired system is not immediately visible, you can access the complete list by clicking on "see all systems." Alternatively, reach out to your administrator or Datarails support for assistance in adding the required system.
• Select Table/Report: Once the system is chosen, proceed to select the specific table or report you wish to query. If the desired table or report is not visible, click on "see all tables" to explore the full list. If access to the required table/report is restricted, contact your administrator or Datarails support for necessary permissions.

Step Three: Field Selection and Aggregation

After identifying the system and table/report, the next step involves selecting the specific fields and defining aggregation functions as per your analysis requirements. Follow these guidelines for a smooth process:

• Choose Fields: Select the field(s) you wish to extract data from. If the required field is not immediately visible, click on "see all fields" to access it.
• Aggregation Functions: Determine the aggregation function necessary for your calculations, such as summing revenues, averaging returns, or identifying highest/lowest figures. Alternatively, choose "unique values" for fetching specific items without applying aggregation.
• Add Time Frame (Optional): If your analysis requires data aggregation within a defined time frame, utilize the "Add time frame" option and specify the date field for measurement.

Step Four: Adding Conditions

In the final step of the Create Formula wizard, you'll add conditions to your query to refine the data retrieval process. Here's how to do it effectively:

• Selecting Filter Conditions: Choose from three options:
  • Specific Values: Select specific values existing in the source system.
  • Excel Cell Reference: Reference a cell in your spreadsheet for dynamic filtering.
  • Free Text or Date Range: Manually enter filter criteria for numeral, textual, or date fields.
• Using Logical Operators: Combine multiple conditions using the "AND" logical operator to ensure each condition must be satisfied for a record to be included in the result set.
• Excel Cell Reference Tip: When referencing a cell in Excel, replace the placeholder in the formula with the required cell address for accurate filtering.

By following these steps, you can efficiently create formulas in Datarails Connect, enabling precise data extraction tailored to your analysis needs. For further assistance or queries, don't hesitate to reach out to our support team.

Editing Formula Conditions

If you wish to add more conditions to an existing formula, you don't need to create a new one. Simply right-click on the Excel formula bar, choose "Datarails" from the context menu, and then select "Add Conditions."

From the screen that appears, you can add more conditions and select the required values for each, whether it is a single value, multiple values, or a range.

Updated July 06, 2024 14:07