Constructelligence
Overview
Free tools Pricing Demo Join the beta
Guide · data

Setting up a construction reporting database in SQL Server

Dashboards, WIP schedules and forecasts should not run against the live accounting system. The usual setup is a reporting copy: the ERP's tables copied into SQL Server or Azure SQL on a schedule, read through a login that can only read. This guide sets one up — getting the data in, locking access down, making it fast, and checking every load before anyone trusts it.

Updated · 13 minute read

Key takeaways
  • Report from a copy of the ERP in SQL, never the live system.
  • One read-only login per tool; connect with ApplicationIntent=ReadOnly.
  • Put views in front of copied tables so reports survive changes to the copy.
  • Check freshness, orphans, text amounts, padded keys and row counts after every load.

The shape of a reporting setup

Four pieces, each with one job:

  1. The ERP — where accounting happens. Nothing reads it but the copy job.
  2. The copy job — moves the ERP's tables into SQL on a schedule (nightly, hourly, or near-real-time).
  3. The reporting database — SQL Server or Azure SQL, one database per company, holding the copied tables plus any views you add.
  4. Read-only logins — one per tool (the BI tool, the dashboard, an analyst), each able to read and nothing else.

Reporting from a copy keeps heavy queries away from the system people are posting in, lets you add indexes and views the ERP would not allow, and gives every tool the same numbers at the same refresh time.

ERPlive postings Copy jobhourly / nightly Reporting DB staging → swap rpt views Power BI Dashboards Excel / scripts one read-only login per tool
Reports read views over a copy, never the ERP: loads swap in whole, and each tool reads through its own read-only login.

Getting the ERP data into SQL

How the copy is made depends on where the ERP keeps its data:

ERPWhere the data livesCommon copy route
Sage 300 CREActian Zen (formerly Pervasive) files on the serverA scheduled replication over the Timberline ODBC driver into SQL Server
QuickBooks DesktopThe company fileA sync tool that writes the company file's lists and transactions to SQL
Cloud ERPs (Vista, Intacct, Acumatica…)The vendor's cloudThe vendor's API or data export, loaded on a schedule

Whatever the route, decide three things up front: how often (hourly is enough for most cost reporting; payroll-heavy views may want the day's close), full or incremental (full reloads are simpler and self-healing; incremental is faster on large ledgers but needs a reliable changed-date), and what happens on failure (keep the last good copy; never leave half-loaded tables for reports to read).

Load into a staging schema and swap it in when the load completes — reports then see either the old copy or the new one, never a half-loaded mix.

CalculatorHow old can the reporting data be?

Sage 300 CRE to SQL Server, specifically

Sage 300 CRE keeps each company's data as its own set of files, read through the Timberline ODBC driver, with tables grouped by module — job cost, accounts payable, accounts receivable, payroll, contracts, equipment. Three decisions shape the copy:

Running reports straight through the ODBC driver works for a single report, but every refresh then reads the live company files while people post — the reason to copy first.

A read-only login, and nothing more

Every tool gets its own login with read access only. In an Azure SQL database, a contained user needs no server login:

-- Azure SQL: run in the company database
CREATE USER reporting_reader WITH PASSWORD = '<a long random password>';
ALTER ROLE db_datareader ADD MEMBER reporting_reader;
DENY INSERT, UPDATE, DELETE, EXECUTE TO reporting_reader;

On SQL Server, create the login at the server and the user in each company database:

CREATE LOGIN reporting_reader WITH PASSWORD = '<a long random password>';
USE CompanyA;
CREATE USER reporting_reader FOR LOGIN reporting_reader;
ALTER ROLE db_datareader ADD MEMBER reporting_reader;

The explicit DENY is belt and braces: db_datareader already cannot write, but a later role grant can't quietly widen a login that has writes denied. Then, in the connection string — with the password supplied from the tool's secret store rather than typed into it:

Server=tcp:yourserver.database.windows.net,1433;Initial Catalog=CompanyA;
User ID=reporting_reader;Encrypt=True;ApplicationIntent=ReadOnly;

ApplicationIntent=ReadOnly sends the connection to a readable secondary where the database has one (Azure SQL read scale-out, Always On availability groups), so reporting load never touches the primary. Restrict the server firewall to the machines that report, and give each tool its own login so one can be revoked without breaking the rest.

Names and views: give reports a stable contract

Replication tools name tables after the source, and two copies of the same ERP can use different conventions — one company's job_cost_detail table is another's detail_job_cost. Reports written straight against copied tables break when either changes. Put a thin layer of views in front:

CREATE VIEW rpt.job_cost AS
SELECT RTRIM(d.job) AS job, d.cost_code, d.cost_type,
       CAST(d.transaction_date AS date) AS posted_on,
       CAST(d.amount AS decimal(18,2)) AS amount
FROM erp.job_cost_detail AS d;

The view is the contract: reports read rpt.job_cost, and when the copy changes, only the view is edited. It is also where to fix the copy's quirks once — trimming padded keys, casting text amounts, naming columns the way the business talks.

Several companies: consolidated reporting

With one database per company, portfolio reports need the companies side by side. On SQL Server, a view can read across databases on the same server:

CREATE VIEW rpt.job_cost_all AS
SELECT 'CompanyA' AS company, * FROM CompanyA.rpt.job_cost
UNION ALL
SELECT 'CompanyB' AS company, * FROM CompanyB.rpt.job_cost;

Azure SQL Database does not allow three-part names across databases, so there the choices are an elastic query, loading every company into one database with a company column, or letting the reporting tool combine them. Whichever you use, this is where one company quietly drops out of a total if its columns differ — which is why the per-company views should expose exactly the same columns and types.

CompanyArpt.job_costCompanyBrpt.job_costCompanyCrpt.job_costrpt.job_cost_allUNION ALL + companyPortfolio reportone queryAzure SQL Database: no three-part names — use an elastic query,one database with a company column, or combine in the BI tool.Count companies in the view after every load — one can drop out silently.
One database per company, one view across them. The check at the bottom is the one that catches the quiet failure.

Checks to run after every load

A copy that loaded without an error can still be wrong. Five queries catch most of it:

1. Freshness — the latest posting should be recent:

SELECT MAX(transaction_date) AS latest_posting FROM erp.job_cost_detail;

2. Orphans — cost on jobs the job master doesn't have (a master that loaded late, or a job deleted at the source):

SELECT d.job, COUNT(*) AS lines, SUM(d.amount) AS amount
FROM erp.job_cost_detail AS d
LEFT JOIN erp.job AS j ON j.job = d.job
WHERE j.job IS NULL
GROUP BY d.job;

3. Amounts stored as text — the copy tool guessed a type wrong, and SUM will fail or round:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'erp'
  AND (COLUMN_NAME LIKE '%amount%' OR COLUMN_NAME LIKE '%total%')
  AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar');

4. Padded keys — fixed-width job numbers arrive with trailing spaces. SQL Server's = ignores trailing spaces, so SQL joins still work, but Power BI, Excel and scripts do not, and "J-1104 " silently fails to match "J-1104":

SELECT COUNT(*) AS padded FROM erp.job WHERE DATALENGTH(job) <> DATALENGTH(RTRIM(job));

5. Row counts against the source — per table, compared with the ERP's own count after each load. A table that shrank without a purge is a failed load.

Making it fast

Almost every construction report filters by job and date, and sums amounts. One covering index on the cost ledger answers most of them:

CREATE NONCLUSTERED INDEX ix_job_cost_detail_job_date
ON erp.job_cost_detail (job, transaction_date)
INCLUDE (amount, cost_type, cost_code);

Create it as the copy's owner — the read-only login can't, by design — and re-create it after a full reload if the copy tool drops and rebuilds tables. Beyond that: select the columns a report needs rather than SELECT *, filter by date in the query rather than in the BI tool, and schedule heavy refreshes after the copy finishes rather than during it.

Power BI: import or DirectQuery

Power BI can either import the data on a schedule or query it live with DirectQuery:

ImportDirectQuery
Speed in the reportFast — data is in the modelEvery visual queries the database
FreshnessAs of the last dataset refreshAs of the last load into SQL
Load on the databaseOnce per refreshEvery time anyone opens a page
FitsMost job cost, WIP and AP/AR reportingVery large ledgers, or near-real-time needs

For a copy that refreshes hourly or nightly, import is usually the right default. An on-premises SQL Server needs the on-premises data gateway for scheduled refresh in the Power BI service; Azure SQL does not. Point Power BI at the rpt views, never the copied tables, and give it its own read-only login. The dashboards and BI guide covers what to put on the pages.

Connecting reporting tools

With the copy, the login and the checks in place, any tool that speaks SQL can read it — Power BI, Excel, a script, or a platform built for construction data. Constructelligence connects with the read-only connection string above, switches company per request, and reports which of its dashboard modules each company database can serve, column by column, before anyone opens a report. It never writes to the database. For moving data between systems in the first place, see the data migration guide; for Sage-specific reporting, the Sage 300 CRE reporting guide.

Checklist
Reporting load checks in Excel, filled in with example rows — columns: Check, Query or source, Expected, Last result, Checked on
The reporting load checks as it opens in Excel: example rows in italics, calculated columns shaded.
Free template · Reporting load checks (Excel & CSV)An Excel workbook with drop-downs, validation and formulas built in — or the same columns as a CSV for Google Sheets and Numbers.
Download Excel (.xlsx)
What this template captures

Each reporting-database check with its query, the expected result, the last result and when it was checked.

5 columns: 5 you fill in. In the Excel version, 1 column reject entries of the wrong type (a date column only takes dates, an amount column only numbers), the header row stays frozen with filters on it, and the workbook opens on an Instructions sheet that lists every column below.

Every column, and how it is captured
ColumnTypeWhat goes in it
CheckTextFree text.
Query or sourceTextFree text.
ExpectedTextFree text.
Last resultTextFree text.
Checked onDateEnter the date.

See it on real-looking numbers

Constructelligence is a construction intelligence platform: it reads your ERP, project and field systems read-only and does this arithmetic every week, for every job. The demo runs it on a sample eight-job portfolio.

Try the demoJoin the private beta

Frequently asked questions

Should I run reports directly against my construction ERP?

It is better not to. A reporting copy in SQL Server or Azure SQL keeps heavy queries away from people posting in the ERP, lets you add indexes and views, and gives every tool the same numbers at the same refresh time.

How do I create a read-only SQL login for reporting?

Create a user in each company database and add it to db_datareader; on Azure SQL a contained user (CREATE USER … WITH PASSWORD) needs no server login. Deny INSERT, UPDATE, DELETE and EXECUTE explicitly, give each tool its own login, and connect with ApplicationIntent=ReadOnly.

How often should the reporting database refresh?

Hourly is enough for most job cost reporting; nightly suits month-end packs. More important than frequency is that a failed or partial load never replaces the last good copy — load into staging and swap it in when complete.

Why do my job numbers not match in Power BI when they match in SQL?

Fixed-width job numbers often carry trailing spaces. SQL Server ignores trailing spaces when comparing with =, but Power BI, Excel and most scripts do not. Trim them in a view (RTRIM) so every tool sees the same key.

Can Power BI connect directly to Sage 300 CRE?

It can, through the Timberline ODBC driver, but every refresh then reads the live company files while people are posting, and each report repeats the same joins. A SQL copy with views in front is faster, safer and gives every tool the same numbers.

Should the reporting database be SQL Server or Azure SQL?

Either works. An on-premises SQL Server sits next to an on-premises ERP and needs the Power BI gateway for scheduled refresh; Azure SQL needs no server to maintain, offers read scale-out for ApplicationIntent=ReadOnly, but does not allow cross-database views, so multi-company reporting needs a different approach.

How do I report across several companies?

Keep one database per company with identical reporting views, then combine them: a UNION ALL view across databases on SQL Server, or an elastic query or a single consolidated database on Azure SQL. Check that every company's view exposes the same columns and types, or one company silently drops out of totals.

What indexes does a construction reporting database need?

Start with one covering index on the job cost ledger keyed on job and transaction date, including amount, cost type and cost code. Most dashboard queries filter by job and date and sum amounts, so that one index serves most of them.

CI
Written by the Constructelligence teamConstruction finance and software. Worked examples use the sample demo portfolio; formulas are standard practice. Reviewed September 2026.
Construction data migration: move job cost history without losing a dollarHow to migrate construction accounting and job cost data: what to move, how much history, cost code crosswalks, month-end cutover, and the tie-outs that prove nothing was lost.Sage 300 CRE reporting: job cost, WIP and dashboards without rebuilding ExcelHow contractors report from Sage 300 CRE — job cost, committed cost, WIP and forecasts — the usual report designer, Crystal and Excel routes, where they break, and how to get weekly answers instead.Construction dashboards and BI: Power BI, Excel or construction intelligence?How contractors build construction dashboards — Excel, Power BI and construction intelligence platforms compared — the KPIs a dashboard needs, why construction data is hard to model, and what it costs to maintain.

More in Data & integrations

All data & integrations resources →