CreateReportSchema
Creates a custom report schema that is based on specified parameters, allowing flexible retrieval of QuickBooks Online report data.
Stored Procedure Specific Information
QuickBooks Online allows only a small subset of columns to be used in the Exec query. The supported columns depend on ReportType.
Note: Only a few reports support multicurrency. The available columns will differ depending on whether multicurrency is enabled or disabled.
To determine if the authenticated account supports multicurrency, check with the following statement:
SELECT CurrencyPrefs_MultiCurrencyEnabled FROM Preferences;
| ReportType | Columns Supported |
| APAging | AccountingMethod, AgingPeriod, Columns, NumPeriods, PastDue, ReportDate, ShipVia, StartDueDate, EndDueDate, Term, Vendor, Indentation |
| APAgingSummary | AgingMethod, Customer, DateMacro, Department, ReportDate, SummarizeColumnsBy, Vendor, NumPeriods |
| ARAgingDetail | AgingMethod, AgingPeriod, Columns, Customer, NumPeriods, PastDue, ReportDate, ShipVia, StartDueDate, EndDueDate, Term, Indentation |
| ARAgingSummary | AgingMethod, Customer, DateMacro, Department, ReportDate, SummarizeColumnsBy, Indentation, NumPeriods |
| BalanceSheetDetail | Class, Customer, DateMacro, Department, StartDate, EndDate, SortBy, SortOrder, Vendor, Indentation |
| BalanceSheetSummary | AccountingMethod, Class, Customer, DateMacro, Department, Item, StartDate, EndDate, SummarizeColumnsBy, Vendor, IncludeUnrealizedGainOrLoss, Indentation |
| CustomerBalanceDetail | AgingMethod, ARPaid, Columns, Customer, Department, ReportDate, ShipVia, SortBy, SortOrder, StartDueDate, EndDueDate, Term, Indentation |
| CustomerBalance | AccountingMethod, ARPaid, Customer, DateMacro, Department, ReportDate, SummarizeColumnsBy |
| CustomerIncome | AccountingMethod, Class, Customer, DateMacro, Department, StartDate, EndDate, SummarizeColumnsBy, Term, Vendor, Indentation |
| CustomerSales | AccountingMethod, Class, Customer, DateMacro, Department, Item, StartDate, EndDate, SummarizeColumnsBy, Indentation |
| ExpensesByVendor | AccountingMethod, Class, Customer, DateMacro, Department, StartDate, EndDate, SummarizeColumnsBy, Vendor |
| GeneralLedgerDetail | Account, AccountingMethod, AccountType, SourceAccountType, Class, Columns, Customer, Locale, DateMacro, Department, SortBy, SortOrder, SourceAccount, StartDate, EndDate, Vendor, Indentation |
| InventoryValuationDetail | Columns, DateMacro, StartDate, EndDate, SvcDateMacro, SvcStartDate, SvcEndDate, GroupBy, Indentation |
| InventoryValuationSummary | DateMacro, Item, ReportDate, SummarizeColumnsBy, Indentation |
| Journal | Columns, DateMacro, JournalCode, SortBy, SortOrder, StartDate, EndDate, Locale, Indentation |
| ProfitAndLossDetail | AccountType, AccountingMethod, Account, SourceAccountType, Class, Columns, Customer, DateMacro, Department, Employee, PaymentMethod, SortBy, SortOrder, StartDate, EndDate, Vendor, IncludeUnrealizedGainOrLoss, Indentation |
| ProfitAndLossSummary | AccountingMethod, Class, Customer, DateMacro, Department, Item, StartDate, EndDate, SummarizeColumnsBy, Vendor, IncludeUnrealizedGainOrLoss, Indentation |
| SalesByClass | AccountingMethod, Class, Customer, DateMacro, Department, Item, StartDate, EndDate, SummarizeColumnsBy, Indentation |
| SalesByDepartment | AccountingMethod, Class, Customer, DateMacro, Department, Item, StartDate, EndDate, SummarizeColumnsBy |
| SalesByProduct | AccountingMethod, Class, Customer, DateMacro, Department, Item, StartDate, EndDate, StartDueDate, EndDueDate, SummarizeColumnsBy |
| StatementofCashFlow | Class, Customer, DateMacro, Department, Item, StartDate, EndDate, SummarizeColumnsBy, Vendor, Indentation |
| TaxSummary | TaxAgencyId, AccountingMethod, DateMacro, StartDate, EndDate, Indentation |
| TransactionList | AccountingMethod, APPaid, ARPaid, BothAmount, Class, Cleared, Columns, CreateDateMacro, Customer, DateMacro, Department, DocNum, DueDateMacro, GroupBy, Item, Memo, ModDateMacro, PaymentMethod, Printed, SortBy, SortOrder, SourceAccountType, StartCreateDate, EndCreateDate, StartDate, EndDate, StartDueDate, EndDueDate, StartModDate, EndModDate, Term, TransactionType, Vendor, Indentation |
| TransactionListWithSplits | Columns, StartDate, EndDate, DateMacro, SourceAccountType, SortBy, SortOrder, DocNum, PaymentMethod, Transactiontype |
| TrialBalance | AccountingMethod, DateMacro, StartDate, EndDate, SummarizeColumnsBy, Locale |
| VendorBalanceDetail | AccountingMethod, APPaid, Columns, DateMacro, Department, DueDateMacro, ReportDate, StartDueDate, EndDueDate, SortBy, SortOrder, Term, Vendor, Indentation |
| VendorBalance | AccountingMethod, DateMacro, Department, ReportDate, SummarizeColumnsBy, Vendor |
For example:
To create the RSD file, it will be generated based on the value of the Location connection property.
EXECUTE CreateReportSchema ReportName='YourReportName', ReportType='APAging'
To return BASE64 encoded data in FileData column.
EXECUTE CreateReportSchema WriteToFile = 'False'
Input
| Name | Type | Description |
| ReportName | String | The name of the report that is to be generated. This name identifies which report definition QuickBooks Online should use. |
| ReportDescription | String | A description for the report. If the report description is not specified, QuickBooks Online selects a description based on the report type. |
| ReportType | String | The name of the ReportType parameter that specifies the exact type of report to generate. This value must match one of the supported QuickBooks Online report types.
使用できる値は次のとおりです。AccountList, APAging, APAgingSummary, ARAgingDetail, ARAgingSummary, BalanceSheetDetail, BalanceSheetSummary, CustomerBalanceDetail, CustomerBalance, CustomerIncome, CustomerSales, ExpensesByVendor, GeneralLedgerDetail, InventoryValuationDetail, InventoryValuationSummary, Journal, ProfitAndLossDetail, ProfitAndLossSummary, SalesByClass, SalesByDepartment, SalesByProduct, StatementofCashFlow, TaxSummary, TransactionList, TransactionListWithSplits, TrialBalance, VendorBalanceDetail, VendorBalance |
| WriteToFile | String | Indicates whether the stored procedure writes the output to a file. This input defaults to true. Set it to false to have the procedure write the output to the file stream that you supply or to return the output as file data. |
| AccountingMethod | String | The accounting method that is used in the report (for example, Cash or Accrual).
使用できる値は次のとおりです。Cash, Accrual |
| AgingPeriod | String | The number of days that define the aging period in the report. |
| Columns | String | The column types that are to be shown in the report. Check the specific report documentation to see the full list of allowed column types. |
| NumPeriods | String | The number of periods to include in the report. |
| PastDue | String | Filters the report contents based on the minimum number of days past due. |
| ReportDate | Date | The starting date that is used for the report. |
| ShipVia | String | Filters the report contents by the shipping method as specified in the invoice's shipping-method reference. |
| Term | String | Filters the report contents to include information for one or more specified terms. Provide a comma-separated list (with one space after each comma) of Term identifiers (Id) |
| Vendor | String | Filters the report contents to include information for one or more specified vendors. Provide a comma-separated list (with one space after each comma) of Vendor Ids. |
| AgingMethod | String | The method that is used for aging transactions in the report.
使用できる値は次のとおりです。Report_Date, Current |
| CustomerID | String | Filters the report contents to include information for one or more specified customers. Provide a comma-separated list (with one space after each comma) of Customer Ids. |
| Department | String | Filters the report contents to include information for one or more specified departments when the company file is configured to use departments. Provide a comma-separated list (with one space after each comma) of Department Ids. |
| Class | String | Filters the report contents to include information for one or more specified classes when the company file is configured to use classes. Provide a comma-separated list (with one space after each comma) of Class Ids. |
| Item | String | Filters the report contents to include information for one or more specified items. Provide a comma-separated list (with one space after each comma) of Item Ids. |
| IncludeUnrealizedGainOrLoss | Boolean | Specifies whether unrealized gains and losses are included in the report. Supported values are true or false. This input is supported only in Canada-based accounts. |
| ARPaid | String | Filters the report contents based on the accounts receivable paid status.
使用できる値は次のとおりです。Paid, Unpaid, All デフォルト値はUnpaidです。 |
| Account | String | Filters the report contents to include information for one or more specified accounts. Provide a comma-separated list (with one space after each comma) of Account Ids. |
| AccountType | String | The account types from which transactions are included in the report. Provide a comma-separated list (with one space after each comma) of account types.
使用できる値は次のとおりです。Bank, AccountsReceivable, OtherCurrentAsset, FixedAsset, OtherAsset, AccountsPayable, CreditCard, OtherCurrentLiability, LongTermLiability, Equity, Income, CostOfGoodsSold, Expense, OtherIncome, OtherExpense, NonPosting |
| SourceAccountType | String | The source account types to include in the report. Provide a comma-separated list (with one space after each comma) of source account types.
使用できる値は次のとおりです。Bank, AccountsReceivable, OtherCurrentAsset, FixedAsset, OtherAsset, AccountsPayable, CreditCard, OtherCurrentLiability, LongTermLiability, Equity, Income, CostOfGoodsSold, Expense, OtherIncome, OtherExpense, NonPosting |
| Locale | String | The locale to use for the report (for example, FR for a French company). |
| JournalCode | String | One or more comma-separated journal codes (for example, AN, BQ1, VT). By default, the report includes data for all journal codes. |
| Employee | String | Filters the report contents to include information for one or more specified employees. Provide a comma-separated list (with one space after each comma) of Employee Ids. |
| PaymentMethod | String | Filters the report contents based on the payment method. Provide a comma-separated list (with one space after each comma) of payment methods such as Cash, Check, Diners Club, American Express, Discover, MasterCard, or Visa. |
| TaxAgencyId | String | The Id of the tax agency for which to generate the report. Read the TaxAgency entity to get valid values for this field. |
| BothAmount | String | Filters the report contents to include transactions for one or more specified amounts. |
| Cleared | String | Filters the report contents to include checks based on their cleared status.
使用できる値は次のとおりです。Cleared, Uncleared, Reconciled, Deposited |
| DocNum | String | Filters the report contents to include transactions that match a specified transaction number, as found in the DocNum field of the transaction object. |
| Printed | String | Filters the report contents based on whether checks are printed.
使用できる値は次のとおりです。All, Printed, To_be_printed |
| Memo | String | Filters the report contents to include transactions that contain one or more specified memo Ids. Provide a comma-separated list (with one space after each comma) of Memo Ids. |
| TransactionType | String | Filters the report contents by the transaction type.
使用できる値は次のとおりです。CreditCardCharge, Check, Invoice, ReceivePayment, JournalEntry, Bill, CreditCardCredit, VendorCredit, Credit, BillPaymentCheck, BillPaymentCreditCard, Charge, Transfer, Deposit, Statement, BillableCharge, TimeActivity, CashPurchase, SalesReceipt, CreditMemo, CreditRefund, Estimate, InventoryQuantityAdjustment, PurchaseOrder, GlobalTaxPayment, GlobalTaxAdjustment, Service Tax Refund, Service Tax Gross Adjustment, Service Tax Reversal, Service Tax Defer, Service Tax Partial Utilisation |
| APPaid | String | Filters the report contents by the accounts-payable paid status.
使用できる値は次のとおりです。Paid, Unpaid, All デフォルト値はUnpaidです。 |
| SortBy | String | The column that is used to sort report rows. Specify one of the columns from the Columns input to control the sort order. |
| SortOrder | String | The sort order for the report rows (for example, Ascending or Descending).
使用できる値は次のとおりです。ascend, descend デフォルト値はascendです。 |
| SummarizeColumnsBy | String | The criteria by which to group the report results.
使用できる値は次のとおりです。Total, Month, Week, Days, Quarter, Year, Customers, Vendors, Classes, Departments, Employees, ProductsAndServices |
| GroupBy | String | The field within each transaction by which to group results.
使用できる値は次のとおりです。Name, Account, Transaction Type, Customer, Vendor, Employee, Location, Payment Method, Day, Week, Month, Quarter, Year, None |
| DateMacro | String | A predefined date range that is used to automatically populate the StartDate and EndDate inputs. Use this stored procedure input when you want the report to cover a standard reporting range without specifying dates manually.
使用できる値は次のとおりです。Today, Yesterday, This Week, This Week-to-date, Last Week, Last Week-to-date, Next Week, Next 4 Weeks, This Month, This Month-to-date, Last Month, Last Month-to-date, Next Month, This Fiscal Quarter, This Fiscal Year, This Fiscal Year-to-date, Last Fiscal Year, Last Fiscal Year-to-date, Next Fiscal Year |
| StartDate | Date | The starting date of the report. This date must be earlier than the ending date. |
| EndDate | Date | The ending date of the report. This date must be later than the starting date. |
| CreateDateMacro | String | A predefined account-creation date range that is used to automatically populate the StartCreateDate and EndCreateDate inputs. Use this when you want the report to cover a standard date range for account creation without specifying dates manually.
使用できる値は次のとおりです。Today, Yesterday, This Week, This Week-to-date, Last Week, Last Week-to-date, Next Week, Next 4 Weeks, This Month, This Month-to-date, Last Month, Last Month-to-date, Next Month, This Fiscal Quarter, This Fiscal Year, This Fiscal Year-to-date, Last Fiscal Year, Last Fiscal Year-to-date, Next Fiscal Year |
| StartCreateDate | Date | The starting date of account creation to include in the report. This date must be earlier than the ending date. |
| EndCreateDate | Date | The ending date of account creation to include in the report. This date must be later than the starting date. |
| DueDateMacro | String | A predefined due-date range that is used to automatically populate the StartDueDate and EndDueDate inputs. Use this when you want the report to cover a standard reporting range without specifying dates manually.
使用できる値は次のとおりです。Today, Yesterday, This Week, This Week-to-date, Last Week, Last Week-to-date, Next Week, Next 4 Weeks, This Month, This Month-to-date, Last Month, Last Month-to-date, Next Month, This Fiscal Quarter, This Fiscal Year, This Fiscal Year-to-date, Last Fiscal Year, Last Fiscal Year-to-date, Next Fiscal Year |
| StartDueDate | Date | The starting due date for receivables. This date must be earlier than the ending due date. |
| EndDueDate | Date | The ending due date for receivables. This date must be later than the starting due date. |
| ModDateMacro | String | A predefined account-modification date range that is used to automatically populate the StartModDate and EndModDate inputs. Use this when you want the report to cover a standard reporting range for account modifications without specifying dates manually.
使用できる値は次のとおりです。Today, Yesterday, This Week, This Week-to-date, Last Week, Last Week-to-date, Next Week, Next 4 Weeks, This Month, This Month-to-date, Last Month, Last Month-to-date, Next Month, This Fiscal Quarter, This Fiscal Year, This Fiscal Year-to-date, Last Fiscal Year, Last Fiscal Year-to-date, Next Fiscal Year |
| StartModDate | Date | The starting date when accounts were modified. This date must be earlier than the ending modification date. |
| EndModDate | Date | The ending date when accounts were modified. This date must be later than the starting modification date. |
| SvcDateMacro | String | A predefined service-date range that is used to automatically populate the SvcStartDate and SvcEndDate inputs. Use this when you want the report to cover a standard reporting range without specifying dates manually.
使用できる値は次のとおりです。Today, Yesterday, This Week, This Week-to-date, Last Week, Last Week-to-date, Next Week, Next 4 Weeks, This Month, This Month-to-date, Last Month, Last Month-to-date, Next Month, This Fiscal Quarter, This Fiscal Year, This Fiscal Year-to-date, Last Fiscal Year, Last Fiscal Year-to-date, Next Fiscal Year |
| SvcStartDate | Date | The starting service date of the items to include in the report. This date must be earlier than the ending service date. |
| SvcEndDate | Date | The ending service date of the items to include in the report. This date must be later than the starting service date. |
| Indentation | Boolean | Indicates whether spaces are added at the start of values to visually identify totals and grouped values in the report output. |
Result Set Columns
| Name | Type | Description |
| Result | String | Indicates whether the stored-procedure execution completed successfully or failed. The stored procedure returns the Result output so that calling applications can check the execution status. |
| SchemaFile | String | The generated schema file that is produced by the stored procedure. The schema file contains the structure of the report. |
| Columns | String | The number of columns that are found in the generated report. This output helps applications validate and map returned data. |
| FileData | String | The Base64-encoded downloaded file content. The FileData output is returned only when the WriteToFile parameter is set to false and the FileStream parameter is not set. |