--- title: Report Designer description: "Build your own reports: choose the records with a query, lay out the page, add your logo, group, subtotal and calculate, including by invoice line." url: "https://theasst.com/docs/report-designer/" updated: "2026-10-08" category: Reports --- # Report Designer Report Designer lets you build your own reports. Choose which records to include with a simple query, design the page the way you want it to look, add your logo, group the rows, and add subtotals, totals and calculations, even down to individual invoice lines. Reports are saved so you can run them again whenever you need them. ## Building a Report 1. Enter a **Report Name** and pick a **Datasource**: Invoice, **Invoice Lines**, Client, Item, Inventory, Purchase Order, Open Purchase Orders, or Payment. 2. Pick an **Output Type**: PDF, CSV, or Excel (XLSX). PDF gives you the full page designer described below. 3. Click fields under **Available Fields** to add them to the report. 4. Optionally add a **Sort by** field, a **Query**, and **Filters**. 5. Click **Save**, then **Run Report**. You only see the datasources and fields you have **Reports** permission for. If a report uses data you don't have permission to report on, it won't open or run, and you'll be told which area is the problem. Ask your administrator to adjust your permissions on the Permissions page. ### Which datasource? - **Invoice** lists one row per invoice. - **Invoice Lines** lists one row per **invoice line**, along with the invoice's date, status and client. Use it when you want to see, filter, group or total individual lines, such as a service or an item type. - The others work the same way: one row per record, and for Purchase Orders and Payments, their lines too. ### Changing the datasource You can switch the datasource without starting over. Everything that exists in the new datasource stays: columns, styling, groups, totals, text, images, the query, and the report name. Anything that doesn't exist in the new datasource, such as a Notes column when you move to Invoice Lines, is left out, and a message lists exactly what was removed. Filters belong to the old datasource, so they are cleared. Save first if you might want to go back. ## Choosing Which Records to Include: the Query The **Query** box limits which records appear. Leave it blank to include everything. A query can be as simple as one condition, or as complex as you need, using AND, OR, NOT and parentheses: ``` (invoice.total >= 75 AND invoice.client.state != "TX") OR (invoice.salesperson = "Mary Smith" AND invoice.date > "5/5/2025" AND invoice.date < "10/4/2026") ``` You don't have to type field names. The list at the right of the box shows the fields available for your datasource; click one to add it to the query. Click a related group, such as **Client** or **Invoice lines**, to open it and reach its fields (for example `invoice.client.state`). Queries are available on the Invoice, Invoice Lines, Client and Item datasources. ### What you can write | To say | Write | | --- | --- | | equal / not equal | `=` `!=` | | greater / less | `>` `>=` `<` `<=` | | text contains / starts / ends | `CONTAINS` `STARTS WITH` `ENDS WITH` | | one of several values | `IN ("Open", "Paid")` | | within a range (inclusive) | `BETWEEN 100 AND 500` | | blank / not blank | `IS NULL` `IS NOT NULL` | | combine | `AND` `OR` `NOT` and parentheses | - Put text in **quotes**: `"Mary Smith"`. - Write dates as **month/day/year with a four-digit year**: `"5/5/2025"`. - Write amounts plainly: `75` or `75.50`. - Text comparisons ignore upper and lower case. ### A few things worth knowing - `invoice.client.state != "TX"` also includes clients that have **no state at all**, because "not Texas" is true for them too. - `invoice.client.state` is the state on the client's **first** address. To match **any** of the client's addresses, use `invoice.client.addresses.state`. - A date that was never filled in, such as the paid date on an unpaid invoice, is never "before" any date. ### Choosing only certain invoice lines There are two different questions you can ask about lines: - **"Does the invoice have a line like this?"** Write `invoice.lines.item_type = "Service"` (note the **s** in `lines`). Any invoice with at least one Service line is included, and **all** of its lines are shown. `invoice.lines.item_type != "Labor"` means "has **no** Labor line". This works on the Invoice datasource. - **"Show me only the lines like this."** On the **Invoice Lines** datasource, write `invoice.line.item_type = "Service"` (note: `line`, singular). Only the Service lines are listed, and other lines on the same invoices are left out. This is what you want for line reports and line totals. For example, on Invoice Lines: ``` invoice.date >= "10/1/2026" AND invoice.date <= "10/31/2026" AND invoice.line.item = "Delivery" AND invoice.status != "Voided" ``` Under **This line** in the field list you'll find the line's item, item type, description, quantity, unit price and amount. `invoice.line.item = "Delivery"` matches one specific item; `invoice.line.item_type = "Service"` matches every service-type item. ### Check Query: syntax help Click **Check Query** at any time. If something is wrong, the problem is highlighted and explained in plain language. When TBA can work out what you meant, it shows a corrected version, and you decide with **Yes, use this** whether to accept it. For example, it can add missing quotes, fix a mistyped field name, add a missing closing parenthesis, or insert a missing AND. A query is always checked before it runs, and nothing is changed unless you accept it. ### Changing the query for one run If you have permission to modify reports, **Run with a different query…** opens a box pre-filled with the saved query. Edit it, then click **Run once with this query** to run the report with it, without changing the saved report. This is handy for changing the dates each month without editing the report. The page's **▶ Run Report** button is different: it saves the report and runs the query in the Query box on the page. Everyone else runs the report with the query it was saved with. ## Designing the Page (PDF) For PDF output, the **Page Layout** shows the page as a stack of **bands**. Each band prints at a different time: | Band | Prints | | --- | --- | | Page Header | at the top of every page | | Report Header | once, on the first page | | Column Headings | under the page header on every page | | Group Header 1-3 | when a group starts (see Groups, below) | | Detail | once for every row of data | | Group Footer 1-3 | when a group ends | | Report Footer | once, after the last row | | Page Footer | at the bottom of every page | | Page Layer | behind everything, on the whole sheet | **To add a column:** click a field under Available Fields. TBA adds the data to the Detail band and a bold heading above it in Column Headings, lined up with each other. **To add anything else:** click a band to choose where it goes, then use **+ Text**, **+ Line**, **+ Image**, **+ Total** or **+ Calc**. Click any element to select it, then: - **Drag** it to move it, or drag its edges to resize it. Arrow keys nudge it one point (hold Shift for ten); **Delete** removes it. - Use the box above the page to set its **text** or **field**, **font** (Helvetica or Times), **size**, **bold**, **italic**, **underline**, **color**, **alignment**, and its exact **X, Y, width and height**. - **Duplicate** copies it. - Drag the bottom edge of a band, or type a **height**, to make the band taller or shorter. The Detail band's height is the height of every row, so keep it close to one line of text. You can style anything this way: column headings, data, labels, titles, totals. ### Special text Text elements can include these, and they are replaced when the report runs: - `{page}`: the page number - `{pages}`: the total number of pages ("Page 2 of 5") - `{date}`: today's date - `{title}`: the report's title ### Long text Turn on **Wrap** for a text element or field and long text wraps onto several lines inside its width. The row grows to fit, so nothing is cut off or overlaps. Without Wrap, text stays on one line and is trimmed to fit its box. ## Adding Your Logo or Another Image Click **+ Image** to place an image anywhere on the page. The picker shows your **company logo** (the one you set under Company Info) and any images you've uploaded for reports. Click one to place it; then drag and resize it like any other element. The image keeps its proportions inside its box. To add a new image, choose a file in the picker and click **Upload** (JPG, PNG, GIF or WebP). Uploaded images are shared by all of your company's reports. The × on an image deletes it from the library. For a **watermark** or letterhead, select the **Page Layer** band before clicking **+ Image**. It covers the whole sheet, margins included, and prints behind everything else on every page. Positions in that band are measured from the corner of the page. ## Groups, Subtotals and Totals To group the rows, choose **+ Add group by…** and pick a field. The list has two parts: **Record fields** (such as the invoice date, status or client) and, on datasources with lines, **Line-item fields** (such as Item Type or Item). You can add up to three groups, and set each one A-Z or Z-A. The report is sorted by the groups first. Each group adds a **Group Header** (starting with the group's value) and a **Group Footer** to the layout. ### Subtotals for any column 1. Make sure the columns you want to total are in the Detail band (click them under Available Fields). 2. Click **Subtotals…** next to the group. A box lists every data column. 3. For each column, choose **Sum**, **Average**, **Minimum**, **Maximum**, **Count**, or (none). Amounts and numbers offer all five; dates offer Minimum, Maximum and Count; text offers Count. 4. Edit the **Label** (such as "Subtotal"), and tick **Also show these as grand totals in the Report Footer** if you want a grand total too. Click **Apply**. TBA places each total in the group's footer directly **under its column**, matching the column's position, width and alignment. Reopen **Subtotals…** at any time to change your choices; it remembers them. If you later move a column, reopen it and click Apply to line the totals up again. **Grand totals…** (next to the group controls) does the same for the Report Footer on its own, with or without groups. You can also add a single total by hand: click a footer band and use **+ Total**. Where a total sits decides what it adds up: - in a **Group Footer**: that group - in the **Report Footer**: the whole report (a grand total) - in the **Page Footer**: that page Turn on **repeat header** for a group to reprint its header at the top of each page the group continues onto. A few notes: - Totals use exact amounts, to the cent. Blank values are skipped, so an average counts only the rows that have a value. - When a report lists lines under each invoice, an invoice-level amount such as the invoice total is added **once per invoice** within each group, page and the whole report, never once per line. An invoice with lines in two groups counts once in each group, but only once in the grand total. - Groups, subtotals and totals apply to **PDF** output. ### Example: daily totals for one service To list every Delivery line for a month, with a subtotal for each day and a grand total: 1. Choose the **Invoice Lines** datasource and **PDF**. 2. Click these fields: Invoice #, Client, Line Description, Quantity and Line Amount. 3. In the Query box, enter: ``` invoice.date >= "10/1/2026" AND invoice.date <= "10/31/2026" AND invoice.line.item = "Delivery" ``` 4. Choose **+ Add group by…**, and under **Record fields** pick **Invoice Date**. 5. Click **Subtotals…** next to the group, set **Line Amount** to **Sum**, tick the grand total box, and click **Apply**. 6. Save and click **Run Report**. Each day's Delivery lines appear under a date heading, followed by that day's subtotal, with a grand total at the end. ## Calculations **+ Calc** adds a calculated value. In a **Detail** band, it works on each row's fields. Fields are written in square brackets, and appear as `[table.field]`; the easiest way to get them right is the **Insert a field** list, which fills them in for you: ``` [invoice.invoice_tot] * 0.0825 [invoice_line.amount] * 2 ``` In a **footer**, it can use totals: ``` sum([invoice.invoice_tot]) * 0.0825 sum([invoice_line.amount]) / count() round(avg([invoice.invoice_tot]), 2) ``` Available functions are `sum`, `avg`, `min`, `max`, `count`, `round` and `abs`, with `+ - * /` and parentheses. Click **Check** to make sure the calculation is valid. Set **Decimals** to control how many decimals show. If a calculation can't be worked out, for example dividing by zero, it prints nothing. If you place something where it can't print, such as a total in the Detail band, a warning appears above the page. ## Running the Report and Saving the File **Run Report** opens the result in a new browser tab. A PDF opens in the viewer, while CSV and Excel files download. When you save the file, its suggested name is the report name followed by today's date, such as `October Deliveries_2026-10-05.pdf`. If nothing matches the report's query and filters, the page says **"No records matched this report"** so an empty report is never a mystery. Check the dates in the query, and the site you are logged into, if you didn't expect that. ## CSV and Excel Output CSV and Excel reports list the columns you chose, one per field, with the query and filters applied. The page design, groups, totals and calculations apply to **PDF** only. On the Invoice Lines datasource, CSV and Excel also list one row per line. ## Saving **Save** keeps the report for reuse. **Load** brings it back later, and **Delete** removes it. **New** clears everything to start over. You need the matching permission on the Permissions page to add, change, delete or run reports. ## Tips - Start by clicking the fields you want, then restyle: it's quicker than building bands by hand. - Run the report often while you build it to see how it prints. - Use **Duplicate** to repeat a styled element. - Keep group totals close to the data they sum, and check a grand total against a report you already trust. - To total a particular kind of line, use the **Invoice Lines** datasource and `invoice.line...` in the query. Use `invoice.lines...` only when you want whole invoices that contain such a line. ## Computer Required The Report Designer is a drag-and-drop page designer, so it always shows the regular desktop layout, even on a phone. Use a computer to build reports. Once saved, you can run them from your phone, though the result is a PDF, CSV, or Excel file that opens in your phone’s own viewer. See [Using TBA on Your Phone](https://theasst.com/docs/using-tba-on-your-phone/).