Business reporting and revenue analysis often require visibility across multiple transaction types, not just one. Instead of running separate queries for Invoices and Cash Sales, SuiteQL’s UNION lets you merge them into a single, unified dataset—perfect for dashboards, saved searches, or external reporting tools.
As a NetSuite Administrator, using UNION allows you to:
- Build holistic transaction reports
- Analyze revenue across Invoices and Cash Sales together
- Reduce duplicate queries and simplify reporting logic
- Standardize columns for downstream analytics
Example: Unified Invoice and Cash Sale Report
This example combines Invoice and Cash Sale transactions into one result set using UNION. The Query Returns
- Transaction number
- Transaction type (human-readable)
- Status (both internal and display value)
- Unbilled amount
- Customer/entity reference
-- =========================
-- Select invoice transactions
-- =========================
SELECT
tranid AS transaction_id, -- Transaction number (unique ID for each transaction)
BUILTIN.DF(type) AS transaction_type, -- Human-readable transaction type (e.g., "Invoice")
BUILTIN.DF(status) AS status_type, -- Human-readable status (e.g., "Open", "Paid In Full")
status, -- Internal status value
amountunbilled, -- Unbilled amount for the transaction (relevant for open invoices)
entity -- Internal customer/entity ID (join to customer for names, if needed)
FROM transaction
WHERE type = 'CustInvc' -- 'CustInvc' is the internal code for Invoice
UNION
-- =========================
-- Select cash sale transactions
-- =========================
SELECT
tranid AS transaction_id, -- Transaction number (Cash Sale)
BUILTIN.DF(type) AS transaction_type, -- Human-readable transaction type (e.g., "Cash Sale")
BUILTIN.DF(status) AS status_type, -- Human-readable status (e.g., "Deposited", "Not Deposited")
status, -- Internal status value
amountunbilled, -- Unbilled amount for the cash sale (may be zero or not used)
entity -- Internal customer/entity ID
FROM transaction
WHERE type = 'CashSale' -- 'CashSale' is the internal code for Cash Sales
To easily execute a SuiteQL query, see NetSuite Admin Tip | Running SUITEQL Using a Suitelet
The sample code described herein is provided on an "as is" basis, without warranty of any kind, to the fullest extent permitted by law. Oracle + NetSuite Inc. does not warrant or guarantee the individual success developers may have in implementing the sample code on their development platforms or in using their own Web server configurations.
Oracle + NetSuite Inc. does not warrant, guarantee or make any representations regarding the use, results of use, accuracy, timeliness or completeness of any data or information relating to the sample code. Oracle + NetSuite Inc. disclaims all warranties, express or implied, and in particular, disclaims all warranties of merchantability, fitness for a particular purpose, and warranties related to the code, or any service or software related thereto.
Oracle + NetSuite Inc. shall not be liable for any direct, indirect or consequential damages or costs of any type arising out of any action taken by you or others related to the sample code.
We’d love to hear how you integrate SuiteQL into your NetSuite processes! Share your insights and experiences in the NetSuite Admin Corner.