Native Reports

Syntra ODBC can run QuickBooks Desktop’s native reports through QBFC/qbXML. Use this when you want the number that QuickBooks itself would show, including historical dates, instead of reconstructing a statement from cached entity tables.

Open the interactive Native Report Builder to generate a valid CALL qb_report(...) statement, supported enum values, and the matching MCP payload before you run anything.

Why use native reports?

• Current and historical requests come from QuickBooks’ own report engine.
• Human-readable SQL output is the default, so BI tools can consume one row per report line.
• Raw normalized output is also available when you need exact native columns and cells for debugging or custom transformations.
• The same report engine powers MCP and SQL, so results stay aligned across AI workflows, Query Explorer, Power BI, Python, and ODBC clients.

Report output shapes

Human rowset (default)

These procedures return a wide, analyst-friendly rowset:

• CALL qb_report(...)
• CALL qb_report_summary(...)
• CALL qb_report_detail(...)
• report-specific helpers such as CALL qb_inventory_valuation_summary(...)

Every result includes base metadata columns:

• report_name
• report_family
• report_title
• report_subtitle
• report_basis
• status_code
• status_severity
• status_message
• row_number
• row_kind
• row_type
• row_label
• section_path

After those base fields, Syntra adds report-specific value columns derived from the QuickBooks column titles. Examples:

• Inventory Valuation Summary commonly yields columns such as on_hand, avg_cost, asset_value, sales_price, and retail_value.
• Profit & Loss with monthly summarization yields columns such as jan_26, feb_26, mar_26, and similar date-derived names.

Column names are sanitized to SQL-safe snake_case and are stable for a given report layout.

Raw normalized rowset

Use the raw procedures when you need the exact cell-level shape:

• CALL qb_report_raw(...)
• CALL qb_report_summary_raw(...)
• CALL qb_report_detail_raw(...)

These return one row per report cell with fields such as column_title, column_type, cell_value, and cell_data_type.

Generic report procedures

Use the generic procedures when you know the QuickBooks report name:

Procedure	Use it for
CALL qb_report(report_type, start_date, end_date, as_of_date[, options_json])	Any registered report type
CALL qb_report_summary(report_type, as_of_date[, options_json])	As-of summary reports
CALL qb_report_detail(report_type, start_date, end_date[, options_json])	Range-based detail reports
CALL qb_report_raw(...) / qb_report_summary_raw(...) / qb_report_detail_raw(...)	Raw debugging output

Examples:

CALL qb_report('InventoryValuationSummary', NULL, NULL, '2026-03-31', NULL);

CALL qb_report_detail('GeneralLedger', '2026-01-01', '2026-03-31', NULL);

CALL qb_report_raw('InventoryValuationSummary', NULL, NULL, '2026-03-31', NULL);

Convenience report procedures

These wrappers call the same native report engine but avoid the generic report_type argument:

Procedure	Native report
CALL qb_profit_and_loss('YYYY-MM-DD', 'YYYY-MM-DD'[, options_json])	ProfitAndLossStandard
CALL qb_balance_sheet('YYYY-MM-DD'[, options_json])	BalanceSheetStandard
CALL qb_trial_balance('YYYY-MM-DD'[, options_json])	TrialBalance
CALL qb_general_ledger('YYYY-MM-DD', 'YYYY-MM-DD'[, options_json])	GeneralLedger
CALL qb_journal('YYYY-MM-DD', 'YYYY-MM-DD'[, options_json])	Journal
CALL qb_audit_trail('YYYY-MM-DD', 'YYYY-MM-DD'[, options_json])	AuditTrail
CALL qb_inventory_valuation_summary('YYYY-MM-DD'[, options_json])	InventoryValuationSummary
CALL qb_inventory_valuation_detail('YYYY-MM-DD', 'YYYY-MM-DD'[, options_json])	InventoryValuationDetail

Examples:

CALL qb_inventory_valuation_summary('2026-03-31');

CALL qb_profit_and_loss('2026-01-01', '2026-03-31', '{"report_basis":"Accrual"}');

CALL qb_general_ledger(
  '2026-01-01',
  '2026-03-31',
  '{"account_filter":{"full_names":["Checking"]}}'
);

Date modes

Each native report uses one of three date modes:

Date mode	Meaning	Example reports
range	Requires start_date and end_date	ProfitAndLossStandard, GeneralLedger, AuditTrail, OpenInvoices, InventoryValuationDetail
as_of	Requires as_of_date only	BalanceSheetStandard, TrialBalance, InventoryValuationSummary, CustomerBalanceSummary
none	Must omit all dates	PhysicalInventoryWorksheet

Examples:

-- Range report
CALL qb_report('GeneralLedger', '2026-01-01', '2026-03-31', NULL, NULL);

-- As-of report
CALL qb_report('InventoryValuationSummary', NULL, NULL, '2026-03-31', NULL);

-- No-date report
CALL qb_report('PhysicalInventoryWorksheet', NULL, NULL, NULL, NULL);

If you use Syntra Query Explorer, clicking a report in the left-hand Reports list inserts the correct starter template automatically.

options_json

Every generic and convenience report procedure accepts an optional final options_json string. This maps directly to the QuickBooks report modifiers and filters supported by Syntra’s native report engine.

How to pass it

options_json is a JSON object stored inside a SQL string literal:

CALL qb_report(
  'InventoryValuationSummary',
  NULL,
  NULL,
  '2026-03-31',
  '{"report_basis":"Accrual","summarize_columns_by":"Month"}'
);

If the JSON contains a single quote inside a value, double it for SQL string escaping:

CALL qb_report_summary(
  'ProfitAndLossStandard',
  '2026-03-31',
  '{"entity_filter":{"full_names":["Bob''s Store"]}}'
);

Supported top-level keys

Core modifiers

Key	Example	Notes
display_report	true	Pass-through display flag
report_date_macro	"ThisMonthToDate"	Macro instead of explicit report dates
report_basis	"Cash" or "Accrual"	Supported on many accounting reports
report_detail_level_filter	"All"	QuickBooks detail-level modifier
report_posting_status_filter	"Posting"	Posting-status modifier
report_modified_date_range_filter	{ "from_date": "2026-01-01", "to_date": "2026-03-31" }	Modified-date filter
report_modified_date_range_macro	"Last30Days"	Alternative to explicit modified-date range
txn_type_filter_list	["Invoice", "SalesReceipt"]	Transaction-type filter

List filters

These keys accept the same list-filter object shape:

• account_filter
• entity_filter
• item_filter
• class_filter

List-filter object keys:

Key	Example	Notes
type_filter	"Bank"	QuickBooks type filter
list_ids	["80000001-1234567890"]	Exact QuickBooks ListID matches
full_names	["Checking"]	Exact QuickBooks full names
list_id_with_children	"80000001-1234567890"	Match a parent plus children
full_name_with_children	"Income:Retail"	Match a parent name plus children

Example:

CALL qb_report(
  'GeneralLedger',
  '2026-01-01',
  '2026-03-31',
  NULL,
  '{"account_filter":{"full_names":["Checking"]},"entity_filter":{"full_names":["Acme Corp"]}}'
);

Summary-report options

These options affect general summary reports such as Profit & Loss, Balance Sheet, Sales by Customer Summary, and Inventory Valuation Summary:

Key	Example	Notes
summarize_columns_by	"Month"	Changes the report layout
include_subcolumns	true	Include QuickBooks subcolumns when supported
report_calendar	"FiscalYear"	Calendar/fiscal report mode
return_rows	"All"	QuickBooks row-return modifier
return_columns	"All"	QuickBooks column-return modifier

Detail-report options

These options affect general detail reports such as General Ledger, Journal, Audit Trail, Open Invoices, and Inventory Valuation Detail:

Key	Example	Notes
summarize_rows_by	"Account"	Detail row summarization
include_column_list	["TxnType", "RefNumber", "Amount"]	Restrict the native columns returned
include_accounts	"InUse"	Account inclusion mode
report_open_balance_as_of	"2026-03-31"	Open-balance reference date

Metadata and BI behavior

The report layout is part of the result schema. In practice that means:

• row-only filters such as account_filter or entity_filter keep the same columns and only change which rows come back
• shape-changing options such as summarize_columns_by, return_columns, or include_column_list can change the visible report columns
• ODBC metadata stays stable for a given report layout so BI tools can prepare the query cleanly

Report-specific caveats

MissingChecks requires an account selection

QuickBooks requires this report to target a real account. type_filter alone is not enough.

Use one of:

• account_filter.list_ids
• account_filter.full_names
• account_filter.list_id_with_children
• account_filter.full_name_with_children

Example:

CALL qb_report(
  'MissingChecks',
  '2026-01-01',
  '2026-03-31',
  NULL,
  '{"account_filter":{"full_names":["Bank of America"]}}'
);

PhysicalInventoryWorksheet takes no dates

This report must be called without start_date, end_date, or as_of_date:

CALL qb_report('PhysicalInventoryWorksheet', NULL, NULL, NULL, NULL);

InventoryValuationDetail is blocked on QuickBooks Enterprise 22.0 US

Syntra blocks InventoryValuationDetail on QuickBooks Enterprise Solutions 22.0 US because the underlying QuickBooks SDK path has caused repeatable QuickBooks Desktop crashes in that environment.

The block is environment-specific. The same report was verified separately on QuickBooks Enterprise Solutions 24.0 CA.

On affected 22.0 US environments, Syntra returns an explicit error instead of sending the request through to QuickBooks.

Some reports depend on QuickBooks features or setup

Syntra blocks known unsafe requests before they reach QuickBooks when the report would otherwise trigger a modal dialog and freeze unattended ODBC or MCP sessions.

Examples include:

• site-based inventory valuation reports when Advanced Inventory Sites is not enabled
• lot/serial-by-site reports when the company file does not support those features
• sales tax reports when the company file has no configured sales tax account or data

In those cases Syntra returns an explicit error instead of waiting for a QuickBooks desktop dialog to be acknowledged.

Not every report supports every modifier

QuickBooks itself limits which filters and options apply to each report. If you pass an unsupported combination, Syntra returns a clear error rather than silently changing the request.

Discovering available reports

You can discover the current catalog in three places:

• Query Explorer: left-hand Reports section
• MCP: list_native_reports
• SQL: use the report names documented by QuickBooks Desktop’s General Summary and General Detail report families

The current native catalog includes the QuickBooks Desktop General Summary and General Detail families. That covers reports such as:

• BalanceSheetStandard
• BalanceSheetSummary
• CustomerBalanceSummary
• InventoryValuationSummary
• ProfitAndLossStandard
• SalesByCustomerSummary
• SalesByItemSummary
• TrialBalance
• AuditTrail
• GeneralLedger
• Journal
• MissingChecks
• OpenInvoices
• PurchaseByVendorDetail
• SalesByCustomerDetail
• TxnListByDate
• UnpaidBillsDetail
• VendorBalanceDetail

See also

• Query Explorer for browsing the built-in report catalog and auto-generated CALL qb_report(...) templates
• MCP Server Setup for using the same native report engine from Claude, Cursor, and other MCP clients
• Special Commands for non-report CALL procedures such as qb_void, qb_sync_table, and qb_rebuild_all