There are three ways to bring in data from another table. Picking the right one avoids most problems.

Union: stacks rows from two tables that have the same columns (for example, this year + last year). Use it when you want more rows, not more columns.

Do: Join tables using their ID column (like project id). IDs are unique and don’t change.

Don’t: Join on names or numbers — they repeat, get typos, or get reused, so rows match wrongly.

Example: Join the SF Project Budget with SF Project on project id, not the number or name. If there is no ID, request one through your usual BuildOps support channel. In cases where there is no Id to join, join on 2 keys (the number or name) and tenant id so you only match within the same customer.

Do: Check whether each row on one side matches just one row on the other (1-to-1) or many (1-to-many).

Don’t: Join a table that has many rows per key and then add up values — the numbers get multiplied.

Example: One project has many budget lines. If you join the project to its lines and sum the contract value, it repeats on every line:

What fan-out looks like:

The total shows $300,000 instead of $100,000, because the value repeats on each line. Roll the lines up first (see tip 4).

Do: Add only the tables and columns the report actually uses.

Don’t: Join every table “just in case.” Extra joins make reports slow and easy to break.

Example: A billings-by-department view on Job Billings Report only needs Job Number, Department Names, Total Amount, and Invoice Issue Date. Leave the rest out.

Do: If one table has many rows per key, total it down to one row per key first, then join. Keep both tables at the same level of detail.

Don’t: Join two detailed tables directly — the rows multiply and totals come out wrong.

Example: To compare budget vs. actuals per project, first total project budget to one row per project id, do the same for actual costs, then join the two. (Same idea for timesheets: total hours_worked up to the project level before joining to project data.)

Do: Treat amounts and hours (total amount, hours worked, labor rate) as numbers to add or average. Group by things like name, date, or department.

Don’t: Put an amount or hours value in the “group by.”

Example: In a Job Billings report, group by department and add up total amount. If you group by Invoice total amount itself, $1,200.50 and $1,200.49 split into two rows — even for the same customer.

Do: Decide which records don’t belong before you build, and filter them out on the main table — do-not-invoice jobs, voided or test records, internal accounts.

Don’t: Leave unwanted rows in. They quietly inflate every total.

Example: A Job Billings report may include “Do Not Invoice” jobs. Filter those out (and apply your exclusion list) so counts and revenue reflect only real work, with values as per the correct definition.

Do: After a join, count the unique id (count distinct), not the rows.

Don’t: Count rows after a join — duplicates make the count too high.

Example: In a Job Billings report, joining SF Job to SF Invoice + SF Invoice Totals and counting rows overstates the number of jobs, because the relationship is 1:many. Count distinct job id instead.

Do: Expect blanks after a left join. Use COALESCE() to fill them (for example, turn a blank amount into 0), and decide how blanks should behave.

Don’t: Ignore blanks. Empty keys never match, and blanks get skipped in some totals and filters — so numbers quietly disagree.

Example: Joining a project to optional change orders leaves blank amounts. Use COALESCE([amount], 0) before summing so the project total stays correct. Alternatively Zn() can also be used to replace nulls in numbers/amounts with 0.

In case of text or date fields being null, based on the use case they can be COALESCED with default value or date.

Example:

Do: Choose one time setting — UTC or local — for the whole report, and build every date the same way.

Don’t: Mix UTC and local columns. The same record can land on different days.

Example: A Job Billings report can pull both Issue Time Utc and Issue Date local from SF Invoice. Pick one (usually local) and use it everywhere so daily and monthly totals match. In case there is no Local timestamp, use ConvertTimezone() to convert to local time zone.

Do: Set control targets on main or base tables (for example: Job Number, Billing Customer Name, Department) before joining. Use a left join from that table.

Don’t: Join everything and filter at the end — this leads to slower load, because it loads all the records and then filters the resulting table.

Example: For a Job Billings report, apply page control targets like Job Number, Department, and Billing Customer Name on the SF Job table, then left join to SF Invoice and SF Invoice Totals.

Do: Use existing aggregated datasets — such as SF Job Stats, SF Service Agreement Stats, SF Service Agreement Property Yearly and Monthly Stats, and SF Project Finance Summary — whenever you can. They already include the complex joins and aggregations needed for reports that rely on totals rather than item-level or sub-line detail. If you need a different aggregation and no dataset exists yet, request one through your usual BuildOps support channel.

Don’t: Run large, complex joins live every time the report opens — this is the main cause of slow loading.

Example: A project P&L that mixes budget, labor, materials, and invoices can be built from the SF Project Ledger or SF Project Finance Summary datasets as one ready-made report (one row per project id).

Do: Give every report sensible default filters, like a date range and department, on the opening page. Make them required where you can.

Don’t: Open with no filters — Sigma loads the whole table first, which is slow.

Example: Default a Job Billings report to the last 30 days and one Department. Picking values before the data loads makes it much faster.

Run through this before you publish a report.