Introduction
Most finance teams spend the first days of every close exporting files, cleaning them up, and rebuilding the same reports by hand. Datarails replaces that cycle with a single flow: bring your data in once, structure it once, and every report and dashboard built on it updates itself.
This guide walks you through that flow in your own Datarails environment, end to end, using one General Ledger file. You don't need any prior Datarails experience. By the time you finish, you'll have a working P&L report with a Gross Profit line, and you'll understand how each stage contributes to it:
- Data inputs: get your data in one place. Upload files or connect your source systems so your financial data is collected automatically instead of chased by email. At this stage, the data is stored but not yet ready for reporting.
- Data mapping: create a single source of truth. The Data Mapper turns raw files into structured, standardized data in a table, cleaned and enriched with custom columns and lookups so every report draws on the same numbers.
- Dashboards: see performance at a glance. As soon as data is mapped, it's ready to power dashboards and widgets.
- Functions and reports: build once, refresh every cycle. Functions connect your structured data to the Report Builder and the Datarails Excel Add-in, so reports refresh with the latest numbers instead of being rebuilt.
Once you understand this flow, you can set up your real data with confidence and quickly find the cause when a number doesn't look right.
Estimated time: 30–40 minutes
What you'll build:
- A folder and a monthly General Ledger filebox, with one month of data uploaded
- A Data Mapper that loads that data into its own table, with two custom columns (Reporting Month and Amount)
- A Chart of Accounts lookup that adds account groupings to your data
- A function and a P&L report with a calculated Gross Profit row
Before you start
You'll work directly in your company's Datarails environment. That might be a brand-new environment with nothing set up yet, or one your team already uses for reporting. The steps are the same either way, and they're designed so nothing you build here changes data or reports that already exist.
What you need:
Admin or Contributor access. These roles can create folders and fileboxes in the Workspace. If you don't see Workspace in the left side panel, or the New button is missing, ask your Datarails Admin for access.
-
The two sample files:
Download each one as an Excel file (File > Download > Microsoft Excel (.xlsx)).
Keep this work separate from your reporting. You'll create everything under its own names: a Getting started folder, a Getting started GL table (Datarails shows it as Getting_started_GL), and a function and report labeled the same way. That way, the sample data never mixes with your real numbers:
- In a brand-new environment, your environment may already include a pre-built Financials table. Leave it for your real data, which your Datarails team will help you set up.
- In an existing environment, other people's folders, Fileboxes, tables, lookups, and functions are already in place. Don't select or edit them in this guide. Always create your own as the steps describe.
Tip: Datarails keeps each table's data separate, so mapping the sample file to your own table has no effect on existing reports or dashboards.
Key terms
| Key terms | What it is |
|---|---|
| Workspace | Where your data inputs live, organized in folders. |
| Filebox | A home for one recurring data source, such as your monthly General Ledger. Each upload is saved as a new version, so you keep a full history. |
| Table | Where Datarails stores structured data that reports and dashboards read from. |
| Data Mapper | The tool that moves data from a filebox into a table, and cleans and enriches it along the way. |
| Lookup | A reference file, such as a Chart of Accounts, that adds groupings to your data. |
| Function | A saved definition of which number to report on and from which table. You select it when you build reports. |
Ready, set, go!
Section 1: Inputs
The outcome: all your financial data lands in one organized place, with every version kept, instead of scattered across inboxes and shared drives.
Data reaches Datarails in two ways:
- Integrations connect directly to your ERP, HRIS, CRM, or database and bring data in automatically, on a schedule or on demand with Sync now. Your Datarails Integrations team helps you set these up.
- Manual uploads let you add any file to a filebox in a few clicks. You'll use this method here.
Either way, data in a Filebox is stored, not yet structured. It becomes reportable only after it's mapped to a table, which happens in Section 2.
Walkthrough 1: Create a Filebox and upload your data
You'll create a Getting started folder, add a monthly GL Filebox to it, and upload the General Ledger sample.
1. Create a folder
In the left side panel, click Workspace. Click New, then select New Folder.
If your Workspace is empty, that's expected in a brand-new environment. If it already has folders, leave them as they are.
2. Name the folder
Type Getting started, then click Confirm.
3. Open the folder
Click the Getting started folder to open it. Its tag shows None until it holds data.
4. Create a Filebox
Click New, then select New Filebox.
5. Name the Filebox and choose its scenario
In File name, type GL. Leave Scenario set to Actuals. The scenario tells Datarails what kind of data this is (Actuals, Budget, or Forecast), so reports can compare them.
6. Set the date tag
Under Tags, make sure Date is turned on and Reporting Month is selected. This means every file you upload is tagged with the month it belongs to, which is what lets Datarails keep each month separate and report on any period.
7. Create the Filebox
Click Create & Close. Your GL Filebox appears in the folder, ready for its first file.
Tip: Upload File creates the Filebox and opens the upload window in one step. This guide uses Create & Close so you also learn the upload path you'll use every month.
8. Open the GL Filebox menu
In the GL row, click the three dots. Its tag shows None until you upload the first file.
9. Click Upload
Select Upload.
10. Add your file
In the Upload file window, drag the General Ledger sample into the upload area, or click click to browse and select it. You can add several files at once; each one becomes a separate version.
11. Select the reporting period
Under Month, the current month is filled in for you. Open the date picker and select the month the data belongs to. In this example, that's October 2026.
12. Upload the file
Click Upload.
13. Confirm the upload
A Files uploaded successfully message appears. The GL filebox now shows today's date under Last submission and a Monthly tag. Your General Ledger is stored in Datarails and ready to map.
Section 2: Data Mapper
The outcome: data that's consistent and report-ready, no matter what format it arrived in, without editing the source file.
The Data Mapper moves the data stored in a filebox into a table in the Datarails database. Along the way, you choose which columns to keep, clean and filter the data, and add custom columns: calculated fields that don't exist in the original file but that your reports need.
Once a Data Mapper is set up, every new file uploaded to that filebox is mapped the same way automatically, so the preparation work you do today is never repeated.
In this walkthrough, you'll map the file's headers and create two custom columns:
-
Reporting Month converts each transaction date to the last day of its month, so every report can group results by month. Formula:
EOMONTH([Reference Date]) -
Amount combines the separate debit and credit columns into one value your reports can sum. Formula:
[Debit (USD)] - [Credit (USD)]
Walkthrough 2: Map your data
You'll create a new Getting started GL table, tell Datarails which row holds the column headers, and add the Reporting Month and Amount custom columns.
Video and step-by-step instructions
1. Open the Data Mapper
Go to Workspace > Getting started, click the three dots in the GL row, and select Data mapper. The Data Mapper opens in a new browser tab, showing the file you uploaded.
2. Create a table for this data
The Data Mapper Settings window opens. Click New + next to Table.
If a table such as Financials is already selected, don't use it. In an existing environment, that table feeds your team's reports. In a brand-new one, it's reserved for your real data.
3. Name the table
In Add new table, type Getting started GL, then click Save. Datarails replaces spaces in table names with underscores, so the table appears as Getting_started_GL from now on.
4. Select the table
Your new table is selected, and Mapper Name is filled in for you (gl_getting_started_gl). Click Select.
5. Choose the flat table format
In Select table type, click Flat table. This tells Datarails that each row in the file is a single record, as in a typical General Ledger export. The window closes on its own.
6. Start adding headers
In the Headers section on the left, click + Add headers.
7. Select the header cells
In the file preview, click each header cell in row 1, from Reference Date (A1) to Credit (USD) (H1). The selected cells are listed under Cells as you go.
8. Finish adding headers
Click Done. Each header now maps to a field of the same name, and the Mapped fields preview at the bottom shows your data as it will be stored.
9. Add a custom column
In the Custom Columns section, click + Add custom column.
10. Name the custom column
In Column name (map to), type Reporting Month.
11. Add the function
Under Functions, search for EOMONTH, then double-click it to add it to the formula.
12. Add the field
Under Fields, search for Reference Date, then double-click it. It's added inside the parentheses, so the formula reads EOMONTH([Reference Date]).
13. Apply the custom column
Click Apply.
Repeat steps 9–13 to create the Amount custom column. You can type the formula [Debit (USD)] - [Credit (USD)] straight into the Formula box, or double-click the fields to add them.
14. Check your custom columns
Both custom columns are listed in the Custom Columns section and appear at the right end of the Mapped fields preview. Reporting Month shows each transaction's month-end date, and Amount shows debits minus credits as a single value.
15. Publish and scan
Click Publish & scan. Datarails saves the mapping and loads the data from your GL filebox into the Getting_started_GL table.
16. Confirm the mapping is saved
A Saved Successfully message appears. Your data is now structured, stored in its own table, and ready for dashboards and reports. From now on, every file you upload to the GL filebox is mapped the same way automatically.
Section 3: Lookups
The outcome: reports organized the way your business thinks, by account group, department, or region, without maintaining mapping columns in every source file.
A lookup connects your table to a reference file, such as a Chart of Accounts, and adds its groupings and hierarchies to every matching record. Update the lookup once, and the change flows through to every report and dashboard built on that table.
For example, connecting a General Ledger to a Chart of Accounts lookup adds fields like account group and P&L line to each transaction, so you can report on Revenue and Expenses instead of hundreds of individual accounts.
As with an Excel lookup, the lookup file and your table must share at least one common key. For a General Ledger and a Chart of Accounts, the keys might be Account ID and Currency, or Account ID and Entity.
For the lookup to match correctly:
- Key names must match the field names in the Data Mapper exactly. "Account ID" and "Account ID number" won't match.
- Keys must be the left-most columns, starting in cell A1. If your keys are Account ID and Currency, Account ID goes in A1 and Currency in B1. The groupings and hierarchies you want to add go in the columns to the right.
- Values must match too. The spelling of each key value in the lookup file must match the value in your data.
Datarails flags any values in your data that aren't in the lookup as unmapped items, so nothing falls out of your reports unnoticed.
Walkthrough 3: Configure a lookup
You'll upload the Chart of Accounts sample as a new lookup and connect it to your Getting_started_GL table.
Video and step-by-step instructions
1. Open Lookups
In the left side panel, click Setup, then click Lookups.
2. Start a new lookup
The Lookups page is titled Mappings. In a brand-new environment it's empty; in an existing one, you may already see a Chart of Accounts or other lookups here. Leave them as they are and click New Lookups.
3. Name the lookup
In Name, type Getting started Chart of Accounts.
4. Add the lookup file
Drag the Chart of Accounts sample into the upload area, or click it and select the file.
5. Create the lookup
Click Create. The Add lookup connection window opens.
6. Connect the lookup to your table
- Target table: select Getting_started_GL. If another table, such as Financials, is selected, change it.
- Sheet name: select the sheet that holds the Chart of Accounts. The sample has one sheet, so it's selected for you.
- Key length: the number of key columns, counted from the left of the lookup file. For the sample, that's 1 (Account ID).
7. Keep Fill unmapped items selected
Leave Fill unmapped items selected. Any accounts in your data that are missing from the lookup are added to it automatically, so you can map them instead of losing them from reports.
8. Save the lookup connection
Click Save. Datarails rescans the table and adds the lookup fields to your data. Close the Lookup configuration window.
9. Check the key field in the Data Mapper
Reopen the Data Mapper: go to Workspace > Getting started, click the three dots in the GL row, and select Data mapper.
In the Mapped fields preview, a binoculars icon now appears next to Account ID. Hover over it to see that it's a key field and which fields it brings in.
10. Review the lookup columns
Scroll the preview to the right. The columns with green headers are the fields the lookup added: Account Group L1, Account Group L2, P&L Line, and Sign.
Walkthrough 3.1: Refine a custom column with lookup fields
Lookup fields can be used in custom columns too. Here, you'll use the Sign field from the Chart of Accounts so each amount shows with the sign finance teams expect in reports. For example, revenue is recorded as a credit, so it currently shows as a negative Amount. Multiplying by Sign turns it positive.
Video and step-by-step instructions
1. Open the Amount column menu
In the GL Data Mapper, in the Custom Columns section, click the three dots next to Amount.
2. Edit the custom column
Click Edit.
3. Update the formula
In Formula, wrap the existing formula in parentheses and multiply it by the Sign field, so it reads ([Debit (USD)] - [Credit (USD)]) * [Sign]. You can type [Sign] or double-click Sign in the Fields list. The parentheses make sure the sign applies to the full amount, not just the credit.
4. Apply the change
Click Apply.
5. Publish and scan
Click Publish & scan to apply the updated formula to all the data in the table.
6. Confirm the change
A Saved Successfully message appears. In the preview, revenue rows now show a positive Amount.
Section 4: Outputs
The outcome: reports and dashboards that update themselves with the latest data, so each close starts with answers instead of copy and paste.
As soon as data is mapped, it's dashboard-ready. You can build widgets and dashboards to track KPIs and trends right away. Learn more in Dashboards overview.
To use the data in reports, create a function. A function defines which table to read, which numeric field to total, and how to aggregate it. Create a function once and reuse it in any number of reports:
- In the Report Builder, to build reports by dragging and dropping fields, without writing formulas. Learn more in Report Builder overview.
- In the Datarails Excel Add-in, to pull live data into your existing Excel models with Datarails formulas. Learn more in The Datarails Excel Add-in: Formulas.
Walkthrough 4: Create a function and your first report
You'll create a function on your Getting_started_GL table and use it to build a P&L report with a Gross Profit row in the Report Builder.
Create a function
1. Open Functions
In the left side panel, click Setup, then click Functions.
2. Start a new function
The List of Functions opens. In an existing environment, you'll see functions your team already uses, such as Value. Don't edit them; reports depend on them. Click the + icon at the top right.
3. Name the function
In Name, type Getting started Amount. Datarails replaces spaces in function names with underscores, so it's saved as Getting_started_Amount. This is the name you'll select in the Report Builder and in Excel formulas. Functions are shared with everyone in your organization, so a clear name helps others recognize what it's for.
4. Select the table
Under Table, select Getting_started_GL. Its fields appear below, grouped by category.
5. Add the value
Search for Amount and drag it into Value. It shows as Sum of Amount: this is the number the function totals.
6. Set the default aggregation field
Under Default Aggregation Field, select Reporting Month. This lets reports total amounts by month.
7. Save the function
Click Save.
Build a report with the Report Builder
Video and step-by-step instructions
1. Open the Report Builder
In the left side panel, click Reporting, then click Report builder.
2. Start a new report
Click the + icon at the top right of the list.
3. Name the report
At the top of the Report Builder, in Report Name, type Getting started P&L.
4. Select the function
Under Function, select Getting_started_Amount. The fields of your table appear under Fields.
5. Lay out the report
Drag Reporting Month into Columns and Account Group L2 into Rows. You can search for a field by name. The Preview updates as you go, showing totals for each account group by month.
6. Filter to P&L items
Click the filter icon on Account Group L2. Click Select none, select Revenue and Expenses, then click Apply.
7. Change the sort order
Click the sort icon on Account Group L2. In Edit Field Sorting, set Type to Z to A, then click Save. Revenue now appears above Expenses.
8. Add a custom row
Click the Add custom row icon next to Rows. Custom rows stay in place when the report refreshes, so you never have to rebuild them by hand.
9. Define the custom row
- Name: type Gross Profit.
-
Formula: type
[Revenue]-[Expenses]. - Position: leave Below and select Expenses.
10. Save the custom row
Click Save. Gross Profit appears below Expenses in the preview.
11. Save the report
Click Save report.
12. Confirm the report is saved
A Report saved message appears. Your P&L report is ready, and it updates automatically every time new data is uploaded to the GL filebox and mapped. To work with it in Excel, click Download to Excel.
Tip: For metrics you'll use across many reports and dashboards, such as Gross Profit, EBITDA, or Net Income, use calculated values instead. Datarails includes more than 20 ready-made calculated values on the Financials table, which you'll use once your real data is set up.
What's next
You've built the same flow that powers every Datarails report:
- Inputs: a General Ledger stored and versioned in one place.
- Data Mapper: structured, standardized data, enriched with custom columns.
- Lookups: data organized by a Chart of Accounts, with gaps flagged automatically.
- Outputs: a reusable function and a P&L report that refreshes every cycle.
Apply it to your own data. Your real setup follows the same steps, using your own General Ledger, Chart of Accounts, and the Financials table. In a brand-new environment, your Datarails implementation team will guide this with you, and Integrations can bring your data in automatically instead of uploading it each month.
Clean up when you're done. Everything you created is labeled Getting started, so it's easy to tell apart from your real work. You can keep it as a reference, or archive the GL Filebox from its three-dot menu (Archive) so the sample data no longer appears in your Workspace. To remove the table, lookup, function, or report, ask your Datarails Admin.
From here, the same structured data can power dashboards, Excel models through the Datarails Excel Add-in, and plain-language analysis through the Datarails AI Connector.
Happy building!
© Datarails Ltd. All rights reserved.
Updated