NetLease - Query Based Reports

The NetLease Generate Report page is where new out of the box query reports and custom built query reports can be viewed. While out of the box scripted reports only support limited customization, clients can create fully custom query based reports by writing SQL and creating a Query Report. This is a good option for clients with specific reporting needs and internal technical teams.

Creating a Query Report

Navigate to NetLease > Reports > All Reports > New.
Give the report a recognizable name and select Query (Standard) or Query (Waterfall) as the Type. Optional to assign the report to a category and/or mark as a favorite to easily find it later.

Click Save. When the page reloads, click Edit to return to the report in edit mode. New fields will be available under the Advanced Configuration tab.
Put the SQL query code in the Report Query box.

Group By fields are not currently supported with Query Reports. Leave those fields on this tab blank.

Click Save. Once the page reloads, click Preview Report.
The report will load in the Report Generator page. Use the Report dropdown to flip between other query reports you have created and any out of the box query reports.

If your report has no information, try populating the date filters. Some queries use dynamic date filters in the SQL and require these fields to be filled out to populate. Queries written with BETWEEN are the exception — blank filters fall back to an unbounded range, so those return everything.

Filters can be expanded at the bottom of the page. To export the report click the drop down in the top left corner and select how you want the report exported.

SQL AI Writing Tips

While Netgain employees cannot help write SQL without a paid contract, there are a couple good tricks that can get any user creating their own reports using an LLM (aka AI).

Use an LLM like Claude Code, ChatGPT Codex, etc. LLMs (especially LLMs designed for coding like these mentioned) are very adapt at getting an SQL query created for you with a clear prompt.
Prompt the LLM to "create a query that is workbench friendly". Our Report Query box is able to read code in this format and it's less error prone.
Provide the internal IDs for any custom fields you want referenced. Download this NetLease Internal IDs excel to easily give the main out of the box fields in NetLease records to your LLM. Any incorrect IDs referenced in the code will throw an error.
You can use the code '{start_date}' to reference the field entered in "From Transaction Date" on the Report Generator page and '{end_date}' to reference the "To Transaction Date" box for dynamic date filters in your report.
Remove any comments in the code. Comments are not supported and cause errors. Look for the use of "--" on individual lines of text or "/* abcdefg */" for multi-line blocks of comments. See examples below:

ORDER BY: ordinal positions (ORDER BY 1) or quoted column aliases (ORDER BY "Year") are the safest form. Table-qualified columns work in NetLease but not in NetAsset's report generator.
Column aliases become the report's headers. Use AS "Column Name" (quoted, spaces allowed) for every output column.
Group By dropdowns on the report record are not supported for query reports. Leave them blank; if your report needs grouping, write the GROUP BY in the SQL itself.

Here is an example query for reference when creating:

SELECT
    EXTRACT(YEAR FROM sl.custrecord_laa_lse_period_start_date) AS "Year",
    COUNT(DISTINCT sl.custrecord_laa_lse_lease)                AS "Lease Count",
    SUM(sl.custrecord_laa_lse_payment)                         AS "Total Payments"
FROM customrecord_laa_lse_schedule_line sl
INNER JOIN customrecord_laa_lease ls ON ls.id = sl.custrecord_laa_lse_lease
WHERE sl.isinactive = 'F'
  AND ls.isinactive = 'F'
  AND sl.custrecord_laa_lse_transaction_type = 1
  AND sl.custrecord_laa_lse_period_number > 0
  AND sl.custrecord_laa_lse_period_start_date
      BETWEEN TO_DATE('{start_date}', 'MM-DD-YYYY')
          AND TO_DATE('{end_date}',   'MM-DD-YYYY')
GROUP BY EXTRACT(YEAR FROM sl.custrecord_laa_lse_period_start_date)
ORDER BY 1

Filter Placeholder Reference

The report generator substitutes these tokens into your SQL before running it. No other {...} tokens are recognized — anything else passes through as literal text and breaks the query.

Token	Substituted with	Notes
`{start_date}`	The From Transaction Date filter value, as `M-D-YYYY` (e.g. `5-1-2026`)	Pair with `TO_DATE('{start_date}', 'MM-DD-YYYY')`. Blank filter falls back to `1-1-1900`, so the query runs unbounded rather than failing
`{end_date}`	The To Transaction Date filter value, as `M-D-YYYY`	Same. Blank falls back to `12-31-9999`
`{current_date}`	Today's date, in the account's date format	Format follows user preferences, so don't pair it with a fixed mask
`{waterfall_columns}`	Generated month or year `SUM(CASE ...)` columns	Query (Waterfall) report type only. On a standard Query report it's replaced with a no-op placeholder column

For date comparisons, prefer TO_DATE('{start_date}', 'MM-DD-YYYY') over comparing bare strings. The bare form relies on implicit format conversion and can silently mis-filter.

Internal Testing

If you have our Shared Transaction tool, there is a Dev SQL testing tool buried in that product that helps debug any errors you may be hitting with your query.

Go to Netgain > Development > SuiteQL Editor.
Paste your SQL code in the top right box on the page on line 1.

Date fields: While you likely want to use the dynamic date fields for your actual query, they are not supported in the SuiteQL Editor, so you will need to change the dates to hard coded options like in the example below. Accepted date format is 'X-X-XXXX'.

Click the green Execute button in the top right corner to run the SQL and verify results are as expected in the bottom right side box. Errors logs will show for straight forward issues with the code, such as incorrect IDs. Which is why this can be a helpful place to test your code!

Once your code is working here, you can copy and paste it directly into the Report Query field on the Report record and save it.

Troubleshooting

Symptom	Likely cause	Fix
Nonsense parse error; query looks fine	A `--` or `/* */` comment ate the rest of the query	Remove all comments. The generator collapses the query to one line before running, so a comment swallows everything after it
Query returns everything, ignores date filters	Unrecognized token (e.g. `{as_of_date}`) passed through as literal text	Use only the four tokens in the reference table
`Unknown identifier` on a `BUILTIN.DF(...)` column	DF applied to a column from a UNION or derived subquery	Join the name table and select its `name`
Date filter matches nothing	Format mask doesn't match the token's `M-D-YYYY` output	Use `TO_DATE('{start_date}', 'MM-DD-YYYY')`
Report shows stale figures after changing filters	The query errored; the previous run's results stay on screen	Open the browser console — the app logs `[NL Report Generator] query failed`

Was this article helpful?