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.

  • 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.

  • You can export your report to CSV or PDF using the provided buttons at the top of the Report Generator page.

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:

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 >= '{start_date}'
  AND asset.custrecord_fa_ast_in_service_date <= '{end_date}'
ORDER BY assettype.name, asset.name

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.
  • 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.
Was this article helpful?

Knowledge Center