NetAsset - Query Based Reports

NetAsset out of the box scripted reports are all query reports on the backend that utilize SQL. 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 NetAsset > Reports > All Reports > New.
Give the report a recognizable name and select Query 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 no information is showing in the report, try putting date filters in for the From and To Transaction Date. Some queries use dynamic date filters in the SQL and the report requires these fields to be filled out to populate.

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 NetAsset Internal IDs excel to easily give the main out of the box fields in NetAsset 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:

The final ORDER BY must use quoted column aliases or ordinal positions — never table.column. The generator runs query reports through NetSuite's paged SuiteQL execution, which re-applies your trailing ORDER BY outside its pagination wrapper, where only the output columns exist. ORDER BY assettype.name fails with Unknown identifier 'assettype.name'. Available identifiers are: {}; ORDER BY "Asset Type" or ORDER BY 2 works. GROUP BY is unaffected.
BUILTIN.DF() only works on real table columns. Applied to a column that came through a UNION or a derived subquery, it fails with the same "Unknown identifier" error. Join the name table instead (e.g., LEFT JOIN subsidiary s ON ... and select s.name).
Custom filters inject at {{WHERE}}. If your report uses custom filters (configured through the report filters app), place {{WHERE}} — uppercase, one space each side — immediately after a WHERE clause where appended AND ... conditions are valid, e.g. WHERE 1 = 1 {{WHERE}} GROUP BY .... Every column a custom filter references must be in scope at that spot. Without the marker, clauses are appended after your query's last WHERE, which may not be the scope you intended in a nested query.
Always include {min_asset_id} and {max_asset_id} somewhere in the SQL. If they're absent and a user sets the Min/Max Asset filter, a legacy fallback appends AND CUSTOMRECORD_FA_ASSET.ID >= ... to your query — which errors unless that exact table alias is in scope. A harmless no-op opts you out: AND (1 = 1 OR {min_asset_id} IS NULL OR {max_asset_id} IS NULL).
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
    asset.name                                      AS "Asset Name",
    assettype.name                                  AS "Asset Type",
    asset.custrecord_fa_ast_in_service_date         AS "In-Service Date",
    asset.custrecord_fa_ast_orig_capitalized_value  AS "Original Cost"
FROM customrecord_fa_asset AS asset
LEFT JOIN customrecord_fa_asset_type AS assettype
    ON assettype.id = asset.custrecord_fa_ast_type
WHERE asset.custrecord_fa_ast_in_service_date >= TO_DATE('{as_of_start_date_placeholder}', 'YYYY-MM-DD')
  AND asset.custrecord_fa_ast_in_service_date <= TO_DATE('{as_of_date}', 'YYYY-MM-DD')
ORDER BY 2, 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.

`{start_date}`	The From Transaction Date filter value	Reformatted date string; prefer the `{as_of_start_date}` pattern below for explicit format control
`{end_date}`	The To Transaction Date filter value	Same caveat
`{as_of_date}`	The To date in `YYYY-MM-DD`; defaults to today when the filter is blank	Use as `TO_DATE('{as_of_date}', 'YYYY-MM-DD')`
`{as_of_start_date}`	A complete expression: `TO_DATE('...','YYYY-MM-DD')` when a From date is set, the literal `NULL` when blank	Do not wrap it in quotes or TO_DATE — write `mydate >= {as_of_start_date}` directly, and guard the blank case: `({as_of_start_date} IS NULL OR mydate >= {as_of_start_date})`
`{current_date}`	Today's date
`{accounting_period}`	The Accounting Period filter value
`{min_asset_id}` / `{max_asset_id}`	The Min/Max Asset filter values, or `NULL` when blank	Include these even if you don't use them
`{{WHERE}}`	Custom filter clauses (each begins with `AND ...`)	Uppercase, one space on each side

Date comparisons: prefer TO_DATE('{as_of_date}', 'YYYY-MM-DD') over comparing bare strings like '{start_date}'. The bare form relies on implicit format conversion and can silently mis-filter; the TO_DATE form is what NetAsset's shipped query reports use.

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
`Unknown identifier 'x.y'. Available identifiers are: {}`	Final ORDER BY uses `table.column`	ORDER BY quoted aliases or ordinals
Same error, but on a `BUILTIN.DF(...)` column	DF on a UNION/subquery-derived column	Join the name table
Same error, mentioning `CUSTOMRECORD_FA_ASSET.ID`, only when the asset filter is used	Legacy asset-range injection	Add the `{min_asset_id}`/`{max_asset_id}` no-op
Nonsense parse error; query looks fine	A `--` comment ate the rest of the collapsed query	Remove all comments
Query returns everything, ignores date filters	Unrecognized token (e.g., `{from_date}`) passed through as literal text	Use only the tokens in the reference table
Works in SuiteQL editor, fails in the report	Paged vs. unpaged execution	Check the ORDER BY and BUILTIN.DF(); retest in the generator
Custom filter has no effect	`{{WHERE}}` marker missing, lowercase, or in the wrong scope	See custom filter tip above.

Was this article helpful?