CreateReportSchema

Generates a schema file for custom QuickBooks reports, providing a structure for tailored data extraction.

CreateReportSchema

CreateReportSchema creates a schema file based on the specified report. This schema adds a table to your existing list that corresponds with the results of your report, which can then be queried like other tables. (Reports from the QuickBooks are not modeled by driver as queryable tables by default.)

The generated schema file outlines the metadata for the report, such as columns and column data types. You can edit the file to adjust data types, rename columns, and include or exclude columns.

Updating a Report Schema

In the following example, the SP CreateReportSchema creates a new report using TestReportTest1 as a base template. It appends new columns to TestReportTest1 and creates a new report, named TestReport2. The new report is saved as ...\\TestReportTest2.rsd.

EXECUTE [CreateReportSchema] 
    [ReportName] = "TestReportTest2",
    [CustomFieldIdsPrimitive] = "1459925,1459928",
    [CustomFieldIdsDropdown] = "1469785",
    [CustomDimensionKeyIds] = "13539564",
    [BaseReportName] = "TestReportTest1", 
    [FileName] = "...\\TestReportTest2.rsd"

Input


Name	Type	Description
ReportName	String	The name of the report. If not specified, the ReportType is used as the default name.
ReportDescription	String	A brief description of the report. If not provided, a description based on the ReportType is used.
ReportType	String	Specifies the type of report for which the schema will be created. The allowed values are 1099DETAIL, APAGINGDETAIL, APAGINGSUMMARY, ARAGINGDETAIL, ARAGINGSUMMARY, AUDITTRAIL, BALANCESHEETBUDGETOVERVIEW, BALANCESHEETBUDGETVSACTUAL, BALANCESHEETDETAIL, BALANCESHEETPREVYEARCOMP, BALANCESHEETSTANDARD, BALANCESHEETSUMMARY, CHECKDETAIL, COLLECTIONSREPORT, CUSTOMDETAIL, CUSTOMERBALANCEDETAIL, CUSTOMERBALANCESUMMARY, CUSTOMSUMMARY, DEPOSITDETAIL, EMPLOYEEEARNINGSSUMMARY, EMPLOYEESTATETAXESDETAIL, ESTIMATESBYJOB, EXPENSEBYVENDORDETAIL, EXPENSEBYVENDORSUMMARY, GENERALLEDGER, INCOMEBYCUSTOMERDETAIL, INCOMEBYCUSTOMERSUMMARY, INCOMETAXDETAIL, INCOMETAXSUMMARY, INVENTORYSTOCKSTATUSBYITEM, INVENTORYSTOCKSTATUSBYVENDOR, INVENTORYVALUATIONDETAIL, INVENTORYVALUATIONSUMMARY, ITEMESTIMATESVSACTUALS, ITEMPROFITABILITY, JOBESTIMATESVSACTUALSDETAIL, JOBESTIMATESVSACTUALSSUMMARY, JOBPROFITABILITYDETAIL, JOBPROFITABILITYSUMMARY, JOBPROGRESSINVOICESVSESTIMATES, JOURNAL, MISSINGCHECKS, OPENINVOICES, OPENPOS, OPENPOSBYJOB, OPENSALESORDERBYCUSTOMER, OPENSALESORDERBYITEM, PAYROLLITEMDETAIL, PAYROLLLIABILITYBALANCES, PAYROLLREVIEWDETAIL, PAYROLLSUMMARY, PAYROLLTRANSACTIONDETAIL, PAYROLLTRANSACTIONSBYPAYEE, PENDINGSALES, PHYSICALINVENTORYWORKSHEET, PROFITANDLOSSBUDGETOVERVIEW, PROFITANDLOSSBUDGETPERFORMANCE, PROFITANDLOSSBUDGETVSACTUAL, PROFITANDLOSSBYCLASS, PROFITANDLOSSBYJOB, PROFITANDLOSSDETAIL, PROFITANDLOSSPREVYEARCOMP, PROFITANDLOSSSTANDARD, PROFITANDLOSSYTDCOMP, PURCHASEBYITEMDETAIL, PURCHASEBYITEMSUMMARY, PURCHASEBYVENDORDETAIL, PURCHASEBYVENDORSUMMARY, SALESBYCUSTOMERDETAIL, SALESBYCUSTOMERSUMMARY, SALESBYITEMDETAIL, SALESBYITEMSUMMARY, SALESBYREPDETAIL, SALESBYREPSUMMARY, SALESTAXLIABILITY, SALESTAXREVENUESUMMARY, TIMEBYITEM, TIMEBYJOBDETAIL, TIMEBYJOBSUMMARY, TIMEBYNAME, TRIALBALANCE, TXNDETAILBYACCOUNT, TXNLISTBYCUSTOMER, TXNLISTBYDATE, TXNLISTBYVENDOR, UNBILLEDCOSTSBYJOB, UNPAIDBILLSDETAIL, VENDORBALANCEDETAIL, VENDORBALANCESUMMARY.
IncludeRowtype	Boolean	A boolean indicating whether the rowtype column should be included in the output schema. The default value is FALSE.
ReportPeriod	String	The date range for the report in the format fromdate:todate. You can omit either value for an open-ended range or use a macro such as TODAY, THISMONTH, or LASTYEAR. Supported formats include yyyy-MM-dd.
AccountType	String	The specific type of account to include in the report. The allowed values are NONE, ACCOUNTSPAYABLE, ACCOUNTSRECEIVABLE, ALLOWEDFOR1099, APANDSALESTAX, APORCREDITCARD, ARANDAP, ASSET, BALANCESHEET, BANK, BANKANDARANDAPANDUF, BANKANDUF, COSTOFSALES, CREDITCARD, CURRENTASSET, CURRENTASSETANDEXPENSE, CURRENTLIABILITY, EQUITY, EQUITYANDINCOMEANDEXPENSE, EXPENSEANDOTHEREXPENSE, FIXEDASSET, INCOMEANDEXPENSE, INCOMEANDOTHERINCOME, LIABILITY, LIABILITYANDEQUITY, LONGTERMLIABILITY, NONPOSTING, ORDINARYEXPENSE, ORDINARYINCOME, ORDINARYINCOMEANDCOGS, ORDINARYINCOMEANDEXPENSE, OTHERASSET, OTHERCURRENTASSET, OTHERCURRENTLIABILITY, OTHEREXPENSE, OTHERINCOME, OTHERINCOMEOREXPENSE.
AccountList	String	A comma-separated list of account names or IDs to include in the report. Use with AccountListType for additional filtering.
AccountListType	String	Specifies whether the AccountList represents account names or account IDs. The allowed values are LISTID, FULLNAME, LISTIDWITHCHILDREN, FULLNAMEWITHCHILDREN.
EntityType	String	The type of entity to include in the report, such as Customers or Vendors. The allowed values are NONE, CUSTOMER, EMPLOYEE, OTHERNAME, VENDOR.
EntityList	String	A comma-separated list of entity names or IDs to include in the report. Use with EntityListType for additional filtering.
EntityListType	String	Specifies whether the EntityList represents entity names or entity IDs. The allowed values are LISTID, FULLNAME, LISTIDWITHCHILDREN, FULLNAMEWITHCHILDREN.
ItemType	String	The type of items to include in the report, such as Inventory or NonInventory. The allowed values are NONE, ALLEXCEPTFIXEDASSET, ASSEMBLY, DISCOUNT, FIXEDASSET, INVENTORY, INVENTORYANDASSEMBLY, NONINVENTORY, OTHERCHARGE, PAYMENT, SALES, SALESTAX, SERVICE.
ItemList	String	A comma-separated list of item names or IDs to include in the report. Use with ItemListType for additional filtering.
ItemListType	String	Specifies whether the ItemList represents item names or item IDs. The allowed values are LISTID, FULLNAME, LISTIDWITHCHILDREN, FULLNAMEWITHCHILDREN.
ClassList	String	A comma-separated list of class names or IDs to include in the report. Use with ClassListType for additional filtering.
ClassListType	String	Specifies whether the ClassList represents class names or class IDs. The allowed values are LISTID, FULLNAME, LISTIDWITHCHILDREN, FULLNAMEWITHCHILDREN.
ModifiedDateRange	String	The range of modification dates to filter the report, in the format fromdate:todate. You can omit either value for an open-ended range or use macros. The allowed values are ALL, TODAY, THISWEEK, THISWEEKTODATE, THISMONTH, THISMONTHTODATE, THISQUARTER, THISQUARTERTODATE, THISYEAR, THISYEARTODATE, YESTERDAY, LASTWEEK, LASTWEEKTODATE, LASTMONTH, LASTMONTHTODATE, LASTQUARTER, LASTQUARTERTODATE, LASTYEAR, LASTYEARTODATE, NEXTWEEK, NEXTFOURWEEKS, NEXTMONTH, NEXTQUARTER, NEXTYEAR..
DetailLevel	String	The level of detail to include in the report, such as Summary or All. The allowed values are ALL, ALLEXCEPTSUMMARY, SUMMARYONLY.
SummarizeColumnsBy	String	Determines how data is grouped and labeled across the columns of the report, such as by Date or Account. The allowed values are NONE, ACCOUNT, BALANCESHEET, CLASS, CUSTOMER, CUSTOMERTYPE, DAY, EMPLOYEE, FOURWEEK, HALFMONTH, INCOMESTATEMENT, ITEMDETAIL, ITEMTYPE, MONTH, PAYEE, PAYMENTMETHOD, PAYROLLITEMDETAIL, QUARTER, SALESREP, SALESTAXCODE, SHIPMETHOD, TERMS, TOTALONLY, TWOWEEK, VENDOR, VENDORTYPE, WEEK, YEAR.
IncludeSubColumns	String	A boolean indicating whether subcolumns should be included in the report.
IncludeColumns	String	A comma-separated list of columns to include in the report, such as ACCOUNT, DATE, or AMOUNT.
IncludeAccounts	String	Indicates whether the report should include all accounts or only those currently in use. The allowed values are ALL, INUSE.
SummarizeRowsBy	String	Determines how data is grouped and labeled across the rows of the report, such as by Account or Customer. The allowed values are NONE, ACCOUNT, BALANCESHEET, CLASS, CUSTOMER, CUSTOMERTYPE, DAY, EMPLOYEE, FOURWEEK, HALFMONTH, INCOMESTATEMENT, ITEMDETAIL, ITEMTYPE, MONTH, PAYEE, PAYMENTMETHOD, PAYROLLITEMDETAIL, QUARTER, SALESREP, SALESTAXCODE, SHIPMETHOD, TAXLINE, TERMS, TOTALONLY, TWOWEEK, VENDOR, VENDORTYPE, WEEK, YEAR.
ReportCalendar	String	Specifies the type of year used in the report, such as Fiscal or Calendar. The allowed values are NONE, CALENDARYEAR, FISCALYEAR, TAXYEAR.
ReturnRows	String	Specifies whether the report should include all rows, only rows with active information, or only rows with nonzero values. The allowed values are NONE, ACTIVEONLY, NONZERO, All.
ReturnColumns	String	Specifies whether the report should include all columns, only columns with active information, or only columns with nonzero values. The allowed values are NONE, ACTIVEONLY, NONZERO, All.
PostingStatus	String	Allows filtering the report for posting, nonposting, or both types of transactions. The allowed values are EITHER, NONPOSTING, POSTING.
ReportAsOf	String	Returns open balance information as of the specified date. The allowed values are REPORTENDDATE, TODAY.
TransactionTypes	String	A comma-separated list of transaction types to include in the report, such as INVOICE, BILL, or CREDITMEMO.
ReportBasis	String	Specifies whether the report is based on Cash, Accrual, or the default setting in QuickBooks. The allowed values are ACCRUAL, CASH, NONE.
FiscalYear	String	The fiscal year to query for budgets in the report.
BudgetCriterion	String	Specifies what the budget covers, such as Accounts or Classes. The allowed values are NONE, ACCOUNTS, ACCOUNTSANDCLASSES, ACCOUNTSANDCUSTOMERS.
SummarizeBudgetColumnsBy	String	Determines how budget data is grouped and labeled across the columns of the report. The allowed values are NONE, CLASS, CUSTOMER, DATE.
SummarizeBudgetRowsBy	String	Determines how budget data is grouped and labeled across the rows of the report. The allowed values are NONE, CLASS, CUSTOMER, ACCOUNT.

Result Set Columns


Name	Type	Description
Result	String	Indicates whether the operation succeeded or failed.
SchemaFile	String	The file containing the generated report schema.
Columns	String	The number of columns identified in the report schema.

ODBC Driver for QuickBooks

CreateReportSchema