How to structure your data for the Financial Report visualization

The Financial Report visualization in DashboardFox renders polished Income Statements (P&L) and Balance Sheets from your underlying data. To do that, DashboardFox needs your data in one of two shapes — Long format or Wide format — and needs a handful of columns that identify accounts, categories, amounts, and time periods.

This article walks through exactly what those two shapes look like, what columns DashboardFox needs, and how DashboardFox auto-detects column roles so you don't have to configure much.

1. Overview

You have two choices for structuring your data:

• Long format — one row per account per date per value type (actual / budget / forecast). This is the natural shape from most accounting databases and is the easiest to produce from a SQL view or a system export.

• Wide format — one row per account with many columns, one column per period-and-value-type combination (e.g., 2024_03_actual, 2024_03_budget). This is the shape a spreadsheet naturally produces.

You can use whichever is more convenient. DashboardFox detects which format you're using and converts long format to wide internally. If you can deliver either, long format is usually simpler to maintain — you don't need to add new columns every month.

Regardless of format, you need the same core information: which account, what category (e.g., Revenue, COGS, Current Assets), and what the value was.

2. Which format should I use?

If you're not sure, pick long format. It's more flexible and DashboardFox will pivot it for you.

3. Required columns — both formats

Every row in your data needs these six pieces of information:

The category value is what tells DashboardFox whether an account is Revenue, COGS, Current Assets, etc. This is the single most important column to get right. See section 7 for the full list.

4. Long format

4.1 What it looks like

Each row is one observation: one account, one date, one value type, one amount. An account with 36 months of actual data and 36 months of budget data will appear on 72 rows (or more, if you have daily data).

| acct | name          | category | subcategory | date       | value_type | amount   |
|------|---------------|----------|-------------|------------|------------|----------|
| 4000 | Product Sales | revenue  | product     | 2024-01-11 | actual     | 85685.10 |
| 4000 | Product Sales | revenue  | product     | 2024-01-19 | budget     | 78990.66 |
| 4000 | Product Sales | revenue  | product     | 2024-02-13 | actual     | 84268.04 |
| 5000 | Materials     | cogs     | materials   | 2024-01-11 | actual     | 32100.00 |

4.2 Required columns for long format

If your column names are different, DashboardFox will still auto-detect most of them (section 8), and anything that can't be auto-detected can be mapped manually in the report builder (section 9).

4.3 Accepted date formats

DashboardFox accepts a wide range of date formats in the date column. You don't need to reformat — pick whatever is natural for your data source.

Tip: If your accounting system gives you dates in multiple formats, use YYYY-MM-DD in your ETL output. It's unambiguous across every locale.

4.4 Value type values

The value type column tells DashboardFox whether each row is actual data, a budget figure, or a forecast. DashboardFox accepts exactly these three values (case-insensitive):

• actual — historical / recorded values

• budget — planned values, used for budget-vs-actual comparisons

• forecast — projected values

If you only have actual data, every row can be actual. The budget and forecast comparisons will simply not appear in the report until you supply those rows.

5. Wide format

5.1 What it looks like

Each row is one account. Period and value-type are combined into column names, with one column per period-and-value-type pair.

| acct | name          | category | subcategory | 2024_01_actual | 2024_01_budget | 2024_02_actual | 2024_02_budget | ...
|------|---------------|----------|-------------|----------------|----------------|----------------|----------------|----
| 4000 | Product Sales | revenue  | product     | 85685.10       | 78990.66       | 84268.04       | 82732.86       | ...
| 5000 | Materials     | cogs     | materials   | 32100.00       | 31500.00       | 33450.00       | 32800.00       | ...

5.2 Period column naming convention

This is the most important part of wide format — DashboardFox reads the time period out of the column name, so the name has to follow one of these patterns exactly:

Rules:

• Use underscores, not dashes or slashes (2024_03_actual, never 2024-03-actual)

• Month numbers can be 01-12 or 1-12 — both work

• Value type is lowercase: actual, budget, or forecast

• The Q in quarterly columns is case-insensitive (Q1 or q1 both work)

Columns that don't match the pattern are ignored as period data. They can still exist in your dataset — they just won't appear on the report's time axis.

5.3 Non-period columns in wide format

Alongside your period columns, wide format still needs the core identifying columns:

Same column-name flexibility applies as in long format — DashboardFox will auto-detect these, or you can map them manually.

6. Minimum viable dataset — what you absolutely need

If you're starting from scratch and just want the simplest possible working dataset, here's the minimum:

Long format (smallest example):

| acct | name  | category | date       | value_type | amount  |
|------|-------|----------|------------|------------|---------|
| 4000 | Sales | revenue  | 2024-01-01 | actual     | 10000.00|
| 5000 | COGS  | cogs     | 2024-01-01 | actual     |  4000.00|
| 6000 | Rent  | opex     | 2024-01-01 | actual     |  2000.00|

That's enough to produce a basic P&L for January 2024. Subcategory is optional. Add more months/years by adding more rows.

Wide format (smallest example):

| acct | name  | category | 2024_01_actual |
|------|-------|----------|----------------|
| 4000 | Sales | revenue  |        10000.00|
| 5000 | COGS  | cogs     |         4000.00|
| 6000 | Rent  | opex     |         2000.00|

Same three accounts, same result.

7. Category values — what to put in the category column

The category column is what tells DashboardFox how to classify each account into the report's structure. DashboardFox is forgiving about casing and spacing — "Current Assets", "current_assets", and "CURRENT ASSETS" are all treated the same. It also accepts common synonyms.

7.1 Income Statement (P&L) categories

7.2 Balance Sheet categories

7.3 What happens if my category value doesn't match?

If you have a category value that doesn't match any of the accepted names (e.g., selling_expenses, intangibles, deferred_revenue), DashboardFox will flag it as "unmapped" in the report builder. You'll see a Category Mapping table where you can choose which canonical category each of your values should map to. This is a one-time setup per report.

Tip: If you control the SQL view that feeds DashboardFox, it's easiest to map to the canonical values directly in the view's CASE statement. If you're working from a spreadsheet export, use a lookup column to translate your source categories to the canonical ones.

8. Subcategory values — what to put in the subcategory column

Subcategory is optional and free-form. DashboardFox doesn't require specific values — whatever you put becomes a visual subgroup inside its category on the report.

For example, under revenue, you might have subcategories like product, service, subscription, licensing. Under current_assets, you might use cash, receivables, inventory, prepaid.

Suggestions for common subcategories:

Use the same subcategory spelling consistently. "Cash" and "cash" become two separate subgroups on the report.

If you don't want subgroups, either leave the subcategory column blank or omit it entirely.

9. Auto-detection — how DashboardFox matches your columns

When you build a report, DashboardFox scans your dataset's columns and tries to figure out which column plays which role. Here's exactly what it looks for:

Matching rules:

• Case-insensitive — Account Name, ACCOUNT_NAME, account name, AccountName all match

• Spaces and underscores are interchangeable — Value Type and value_type match the same way

• Partial matches count — a column named period_date will match the "date" rule because it contains date

9.1 What if auto-detect gets it wrong?

It happens — for example, a column named GL_Account_Number contains the word account, so DashboardFox might auto-detect it as the Account Name (label) column instead of the Account Code. Two things help:

1. The report builder always shows you what was detected. Review the column picks before saving.

2. You can manually override any pick in the builder's column-mapping section. See section 10 below.

9.2 What if my columns don't match any of these names?

You can use any column names you want — auto-detect just saves you a step. If your columns are GLAcct, Dept, FiscalPeriod, etc., DashboardFox won't auto-detect them, but you can manually map each column to its role in the report builder.

10. Manual column mapping — when auto-detect isn't enough

Every Financial Report has a column-mapping section in its settings where you can explicitly tell DashboardFox which of your columns plays which role. Use this when:

• Your column names don't match the patterns in section 9

• Auto-detect picked the wrong column for a role

• You want to use a non-default column (e.g., use LegalEntity as your Account Name instead of acct)

The mapping section offers one dropdown per role (Account Code, Account Name, Category, Subcategory, Date, Value Type, Amount). You pick the column from your dataset for each role. DashboardFox remembers your picks and uses them on every render.

Tip: If you build many reports from the same dataset, it's easier to name your columns to match DashboardFox's auto-detect patterns once in your SQL view. Every new report starts correctly configured with zero clicks.

11. Building P&L vs Balance Sheet reports

Both report types use the same data shape. The difference is in which categories you provide:

• A P&L (Income Statement) report expects accounts with categories: revenue, cogs, opex, other_income, other_expense, tax

• A Balance Sheet report expects accounts with categories: current_assets, fixed_assets, current_liabilities, long_term_liabilities, equity

If you have both kinds of accounts in one dataset, that's fine — DashboardFox will render each report using only the relevant categories and ignore the rest. You don't need separate datasets.

For Balance Sheet specifically: DashboardFox validates that Total Assets = Total Liabilities + Equity and shows an indicator on the report. If your data doesn't balance, you'll see a warning with the difference amount — useful for spotting data-quality issues.

12. Complete examples

12.1 P&L — Long format (first few rows)

| acct | name              | category  | subcategory | date       | value_type | amount   |
|------|-------------------|-----------|-------------|------------|------------|----------|
| 4000 | Product Sales     | revenue   | product     | 2024-01-31 | actual     | 85685.10 |
| 4000 | Product Sales     | revenue   | product     | 2024-01-31 | budget     | 78990.66 |
| 4100 | Service Revenue   | revenue   | service     | 2024-01-31 | actual     | 42150.00 |
| 5000 | Raw Materials     | cogs      | materials   | 2024-01-31 | actual     | 28400.00 |
| 5100 | Direct Labor      | cogs      | labor       | 2024-01-31 | actual     | 15200.00 |
| 6000 | Sales & Marketing | opex      | sales_marketing | 2024-01-31 | actual | 12000.00 |
| 6100 | Office Rent       | opex      | facilities  | 2024-01-31 | actual     |  4500.00 |
| 8000 | Corporate Tax     | tax       |             | 2024-01-31 | actual     |  5820.00 |

12.2 P&L — Wide format (first few rows)

| acct | name          | category | subcategory | 2024_01_actual | 2024_01_budget | 2024_02_actual | 2024_02_budget |
|------|---------------|----------|-------------|----------------|----------------|----------------|----------------|
| 4000 | Product Sales | revenue  | product     |        85685.10|        78990.66|        84268.04|        82732.86|
| 5000 | Raw Materials | cogs     | materials   |        28400.00|        28000.00|        30100.00|        29500.00|
| 6000 | Sales & Mkt   | opex     | sales_marketing |    12000.00|        12000.00|        13200.00|        12500.00|

12.3 Balance Sheet — Long format (first few rows)

| acct | name                | category              | subcategory | date       | value_type | amount    |
|------|---------------------|-----------------------|-------------|------------|------------|-----------|
| 1000 | Cash                | current_assets        | cash        | 2024-01-31 | actual     | 119812.66 |
| 1100 | Accounts Receivable | current_assets        | receivables | 2024-01-31 | actual     |  64300.00 |
| 1200 | Inventory           | current_assets        | inventory   | 2024-01-31 | actual     |  48500.00 |
| 1500 | Office Equipment    | fixed_assets          | equipment   | 2024-01-31 | actual     |  32000.00 |
| 2000 | Accounts Payable    | current_liabilities   | payables    | 2024-01-31 | actual     |  28900.00 |
| 2500 | Long-term Loan      | long_term_liabilities | other       | 2024-01-31 | actual     | 125000.00 |
| 3000 | Retained Earnings   | equity                |             | 2024-01-31 | actual     | 110712.66 |

Note for Balance Sheet: because balance sheets are point-in-time (not totals over a period), the date column represents the date of the snapshot. If you're pre-aggregating to monthly, use the last day of each month.

12.4 Balance Sheet — Wide format (first few rows)

| acct | name                | category              | subcategory | 2024_01_actual | 2024_02_actual | 2024_03_actual |
|------|---------------------|-----------------------|-------------|----------------|----------------|----------------|
| 1000 | Cash                | current_assets        | cash        |       119812.66|       121450.00|       123100.00|
| 1100 | Accounts Receivable | current_assets        | receivables |        64300.00|        68100.00|        71200.00|
| 2000 | Accounts Payable    | current_liabilities   | payables    |        28900.00|        30100.00|        31500.00|
| 3000 | Retained Earnings   | equity                |             |       110712.66|       115300.00|       120400.00|

13. Common pitfalls

14. Pre-built templates

If you pull financial data from a major accounting system (Xero, QuickBooks, NetSuite, etc.), we offer pre-built Excel templates that reshape those exports into the structure described above. See [link TBD] for the current list of available templates.

Summary

1. Pick a format — long for database/ETL, wide for spreadsheets. Long is usually easier to maintain.

2. Provide the core columns — account code, name, category, subcategory, value, and date/period.

3. Use the accepted category values in section 7 so DashboardFox recognizes your accounts without manual mapping.

4. For wide format, follow the column naming convention — YYYY_MM_actual, YYYY_MM_budget, etc.

5. Let auto-detect do the work. If it gets something wrong, override it in the builder's column mapping section.

If you can deliver data that matches this guide, building a financial report in DashboardFox is mostly a matter of picking the preset (P&L or Balance Sheet) and hitting save.