How to extract sp_reports using Microsoft Excel

Open Microsoft Query in Excel

Open Microsoft Excel, get external data from Microsoft Query via Data Menu -> "Get Data -> From Other Sources->From Microsoft Query" as below:

Note: In recent versions of Microsoft Excel (including Excel 365), the Microsoft Query (Legacy) feature is hidden by default from the Get Data tab.
Please refer to Troubleshooting - How to enable Microsoft Excel 365 - Legacy Microsoft Query.

It will take some time to get the DSN list:

In the "Choose Data Source" Window, Select the QuickBooks Data DSN you set up with the QODBC driver. This is one of our pre-installed DSN names or one that you have created.

Note: Uncheck option "Use Query Wizard to Created/edit Queries" since we do not need to import any tables here.

Note: Youed to Select the QuickBooks Online Data DSN.

C for using QuickBooks Online, reportlose the "Add Tables" window by clicking the "Close" button.

Press the "SQL" button to input your sp_report query as below. Here we take a BalanceSheetDetail report as an example:

sp_report BalanceSheetDetail parameters DateMacro = 'ThisMonthToDate'

Click the "OK" button to close the warning message:

Press Execute button to get results in Microsoft Query:

Return Records to Excel Spreadsheet

Select Menu File -> Return Data to Microsoft Excel to return records to Excel Spreadsheet.

Also, Refer:

How to use the QuickBooks Reporting Engine with QODBC

How to use the QuickBooks Reporting Engine with QODBC Online

Tags: QuickBooks Online, QBO, Excel, MS Query, QODBC Online, Sp_reports, Balance sheet detail

Problem Description:

I am working in Excel. I have set up three pass-through queries and had them work on referencing cells for DateFrom and DateTo parameters. The queries were SummarizeColumnsBy by Month.

The queries suddenly stopped working, only returning Amount_1 and not Amount_1 through Amount_12.

If I manually put the date in the command text, all 12 months will return.

Solution:

Please ensure that you pass both the dates (i.e., start date and end date) in the parameter value. There should be 12 months difference between the two dates to get all the 12-month amount.

Also, please ensure that you pass the date in the correct format through the parameter.

Please use the below format & check again:

YYYY-MM-DD

For Example:

DateFrom: 2017-01-01

DateTo: 2017-12-31

Problem Description:

I am using QODBC for exporting QuickBooks reports in MS Excel. I am using the BalanceSheetStandard report for specific date ranges as below.

sp_report BalanceSheetStandard show Text, Label, Amount parameters DateFrom = {d'2020-01-01'},DateTo = {d'2020-06-30'}, SummarizeColumnsBy = 'TotalOnly'

I want to pass the date value in the parameter. So, I can execute reports for a specific date period without changing the query.

Do you know how to pass a value from Excel to the DateFrom and DateTo parameters?

Solution:

Please follow the below steps for using Prompted Date Ranges in MS Excel with sp_reports.

First, You need to export the report to MS Excel.

Please refer to how to create sp_reports using Microsoft Excel 2007 to export reports in MS Excel.

I have already exported the BalanceSheetStandard report to MS Excel.

On the "Data" tab, click the "Queries & Connections" button in the Excel sheet.

In the Workbook Connection Window, Right-click on the connection name & click the "Properties" button to view detailed information:

Navigate to the Definition tab. You can view the Connection string and Command text.

In the Excel command text, You need to replace the actual date value with the question mark for the parameter you want & save it by clicking the "OK" button.

For Example: sp_report BalanceSheetStandard show Text, Label, Amount parameters DateFrom = ?,DateTo = ?, SummarizeColumnsBy = 'TotalOnly'

Now, whenever you Refresh Data:

Excel will prompt for the parameter value.

Please use the below format for providing the date value.

YYYY-MM-DD

Please enter both parameter values one by one & click "OK."

The report data get refreshed with the parameter date value.

Problem Description:

I recently ran into an issue when querying the inventory for each lot number. In QuickBooks, there is a report called "Lot Numbers in Stock." You can click on the Lot Numbers on that report, and it opens a Quick View that shows the On Hand QTY for each Lot Number.

I've searched through the tables with the help of QODBC, and the only table I could find that appears to offer the information was the "BuildAssembly" table. However, the Quantity On Hand does not match what shows when I run the report in QuickBooks.

Could you please point me in the right direction to where I can query this information using QODBC?

Solution:

I am not sure How the "Lot Number In Stock" or "Serial Number In Stock" report is showing QuantityOnHand & which table it is using. You can verify QuantityOnHand from BuildAssembly through QuickBooks UI & QODBC, which will be identical.

The BuildAssembly table will show QuantityOnHand details when Assembly was built. It will not conduct the current Quantity as shown in the report.

Also, I'm sorry to inform you that the "Lot Number In Stock" or "Serial Number In Stock" report is not available through the Intuit SDK, so they are not available through QODBC.

QODBC is an ODBC driver for QuickBooks. It uses the QuickBooks SDK to communicate with QuickBooks, which means if Intuit doesn't expose one feature to the application in SDK, QODBC could not do it either.

Refer: How to export QuickBooks data not available in QODBC

Problem Description:

I'm trying to extract QuickBooks budget data to SQL for reporting purposes. However, I don't see any tables that would hold the budget. My research is telling me this may not be possible. Can someone please tell me how I can extract Budget data?

Solution:

We are sorry to inform you that the Budget table is unavailable through the Intuit SDK, so budget-related information is unavailable through QODBC.

QODBC is an ODBC driver for QuickBooks. QODBC uses the Intuit QuickBooks SDK to communicate with QuickBooks, which means if Intuit doesn't expose the field/table/feature to the application in SDK, QODBC could not do it either.

Refer: How to export QuickBooks data not available in QODBC

You can refer Budget related reports for Budget details.

You can refer below reports.

sp_report BalanceSheetBudgetOverview show Amount_Title, Label, Amount parameters FiscalYear = 2015, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account'

sp_report BalanceSheetBudgetVsActual show Amount_Title, Budget_Title, Difference_Title, Percent_Title, Text, Label, Amount, Budget, Difference, Percent parameters FiscalYear = 2015, BudgetCriterion = 'Accounts,' SummarizeBudgetColumnsBy = 'Date,' SummarizeBudgetRowsBy = 'Account'

sp_report ProfitAndLossBudgetOverview show Amount_Title, Text, Label, Amount parameters FiscalYear = 2015, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account'

sp_report ProfitAndLossBudgetPerformance show Amount_Title, Text, Label, Amount parameters FiscalYear = 2003, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account'

sp_report ProfitAndLossBudgetVsActual show Amount_Title, Budget_Title, Difference_Title, Percent_Title, Text, Label, Amount, Budget, Difference, Percent parameters FiscalYear = 2015, BudgetCriterion = 'Accounts,' SummarizeBudgetColumnsBy = 'Date,' SummarizeBudgetRowsBy = 'Account'

Refer: To how to use the QuickBooks Reporting Engine with QODBC for more details.

Problem Description 1:

Does sp_report support "Like" in the parameter?

For example, sp_report CustomTxnDetail shows Text, Blank, TxnType, Date, RefNumber, Name, Memo, Account, ClearedStatus, SplitAccount, Debit, Credit, RunningBalance parameters AccountFullName Like '%Chase Credit Card%,' SummarizeRowsBy = 'TotalOnly.'

Solution 1:

You can use like in sp_report as below:

For example, sp_report CustomTxnDetail shows Text, Blank, TxnType, Date, RefNumber, Name, Memo, Account, ClearedStatus, SplitAccount, Debit, Credit, RunningBalance parameters SummarizeRowsBy = 'TotalOnly' where AccountFullName Like '%Chase Credit Card%.'

Problem Description 2:

On some sp_reports like OpenInvoices or SalesByCustomerDetail, I don't see where I can put the parameter for the sales rep.

Which one is the parameter for the Sales Rep?

Solution 2:

The SalesRep filter in OpenInvoices and SalesByCustomerDetail report is not available through the Intuit SDK, so they are not available through QODBC.

QODBC is an ODBC driver for QuickBooks. It uses the QuickBooks SDK to communicate with QuickBooks, which means if Intuit doesn't expose one feature to the application in SDK, QODBC could not do it either.

But, You can use the available column name in the sp_report by specifying with a where clause for filtering report data as below.

For Example, the SalesRep column is available in the OpenInvoices and SalesByCustomerDetail report, So you can use it in the sp_report query for filtering report data as below:

sp_report OpenInvoices show TxnType_Title, Date_Title, RefNumber_Title, PONumber_Title, Terms_Title, DueDate_Title, Aging_Title, OpenBalance_Title, Text, Blank, TxnType, Date, RefNumber, PONumber, Terms, DueDate, Aging, OpenBalance parameters DateMacro = 'Today' where SalesRep = 'John Doe.'

sp_report SalesByCustomerDetail show TxnType_Title, Date_Title, RefNumber_Title, Memo_Title, Name_Title, Item_Title, Quantity_Title, UnitPrice_Title, Amount_Title, RunningBalance_Title, Text, Blank, TxnType, Date, RefNumber, Memo, Name, Item, Quantity, UnitPrice, Amount, RunningBalance parameters DateMacro = 'ThisMonthToDate' where SalesRep = 'John Doe.'

Problem Description:

How to Auto Add relationship Between two tables in Crystal Report using QODBC?

Solution:

A relationship can be extracted using the ODBC function "SQLForeignKeys" or "SP_FOREIGNKEYS."

Usage: SP_FOREIGNKEYS tablename tablename

Instruction: Returns a record set of the detailed relationship information of two tables.

Example: sp_foreignkeys Customer Invoice

Crystal reports application automatically uses the relationship method and sprovides a user-friendly graphic interface to view the relationships between two or more tables.

To Auto Add relationship Between two tables in Crystal Report, Please create a new report in Crystal Report & connect to QuickBooks Data through QODBC. Please refer to How to use QODBC with Crystal Reports for connection detail.

I am assuming that you have referred above link & connected to QuickBooks from Crystal Report using QODBC.

The connection was made with QuickBooks through "QuickBooks Data" DSN & you can see the list of tables and windows list of tables available.

Select the table you want to add to the Crystal Report & click the ">" button. In this example, I am selecting the Customer & Invoice table.

The Customer & Invoice table data is added to the Crystal Report. Please click the "Next" button for link tables.

Please select the "By Key" option & Crystal Report will automatically link tables according to the Primary Key & Foreign Key relationship between the two tables of QODBC.

You can see that the Invoice table's "CustomerRefListID" is related to the Customer table's "listed" field.

Suppose you select the "By Name" option. In that case, Crystal Reports will not consider the Primary Key & Foreign Key relationship between two tables of QODBC & it will link according to a similar column name.
For Example Invoice table's ClassRefListID with the Customer table's "listed."

The table fields are now available for selection in your report. Now, You can perform any operation on table data using Crystal Reports.

In this example, I have added the Customer table's Phone, Email, and Balance fields & Invoice table's CustomerRefFullName, RefNumber, and SubTotal fields in the report.

Problem Description:

I am using sp_report through QODBC & I have noticed that using QuickBooks UI, we can add multiple TxnFilterTypes like 'Invoice,' 'CreditMemo,' and 'SalesReciept,' etc.

How can I add the same using sp_report through QODBC?

Solution:

You can use multiple TxnFilterTypes as below:

TxnFilterTypes='Check', 'Invoice,' 'CreditMemo'

For Example:

sp_report ARAgingDetail show Text, Blank, TxnType, Date, RefNumber, PONumber, Name, Terms, DueDate, Aging, OpenBalance parameters DateMacro = 'Today,' TxnFilterTypes='Check,' 'Invoice,' 'CreditMemo,' AgingAsOf = 'Today.'

Problem Description:

I would like to know if QODBC will convert QuickBooks Pro Desktop invoices into MS Excel Report.

The attached invoice is similar to what we need to export to MS Excel. It doesn't have to be exact but needs to be able to show the descriptions, etc.

Solution:

You can export QuickBooks Invoices into MS Excel format using QODBC.

You can do it through VBA in MS Excel.

The first step is to download Invoice Template in MS Excel.

You can download the template by clicking "New," & you can find the template under "Office.com Templates." Click on "Invoices" to see the list of Invoice templates.

You can download the template by selecting the template as per your requirement & click on the "Download" button to download the template. I have downloaded the "Service invoice (Garamond Gray Design)" template in this example.

The invoice Template is available with sample data. You can modify the template as per your requirement.

I have made changes to the template. I have changed some existing captions & added ITEM & ITEM DESCRIPTION. I have removed company information, logo, CUSTOMER ID, SALESPERSON, JOB, and LINE TOTAL.

We will fill out this information dynamically by VBA code. We will add a button asking for the RefNumber of Invoice by entering RefNumber VBA code will fetch Invoice detail for particular RefNumber & fill details in Invoice template.

Now we will add a button & we will write code for the button click event. On button click, it fetches the Invoice from QuickBooks & fills it in the template.

You can add a button from the Developer tab in MS Excel. If the Developer tab is unavailable, follow the steps below to display it.

Click the "File" tab, and click "Options."

Click the "Customize Ribbon" category. In the "Main" Tabs list, select the "Developer" checkbox, and then click "OK."

On the "Developer" tab, in the Controls group, click "Insert," and then under "ActiveX Controls," click "Command Button."

A Command Button is added. I have renamed it to "Enter Invoice #." Double-click on the command button to write code.

Write the below code for export QuickBooks Invoices into MS Excel Report format.

Application Source Code:

Private Sub CommandButton1_Click()

ActiveWorkbook.Sheets("ServiceInvoice").Range("F3").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("F4").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("A3").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("B3").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("C3").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("B4").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("B5").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("B6").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("B9").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("B10").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("B11").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("D15").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("F15").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("F39").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("A18:A38").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("B18:B38").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("C18:C38").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("D18:D38").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("E18:E38").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("F18:F38").ClearContents

Const adOpenStatic = 3
Const adLockOptimistic = 3
Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL
Dim InvNo As String

sConnectString = "DSN=Quickbooks Data;OLE DB Services=-2;"

InvNo = InputBox("Enter RefNumber:", "InvNo")
If (InvNo = "") Then
    Exit Sub
End If
sSQL = "SELECT TxnID,CustomerRefFullName,TxnDate,RefNumber,BillAddressAddr1,BillAddressAddr2,BillAddressCity,BillAddressState,BillAddressPostalCode,ShipAddressAddr1,ShipAddressAddr2,ShipAddressCity,ShipAddressState,ShipAddressPostalCode,TermsRefFullName,DueDate,Subtotal,InvoiceLineItemRefFullName,InvoiceLineDesc,InvoiceLineQuantity,InvoiceLineRate,InvoiceLineAmount FROM InvoiceLine where RefNumber = '" & FnRef(InvNo) & "'"


Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")

oConnection.Open sConnectString
oRecordset.activeconnection = oConnection
oRecordset.Open sSQL

ActiveWorkbook.Sheets("ServiceInvoice").Activate
ActiveWorkbook.Sheets("ServiceInvoice").Range("F3").Value = oRecordset.Fields("TxnDate")
ActiveWorkbook.Sheets("ServiceInvoice").Range("F4").Value = oRecordset.Fields("RefNumber")
ActiveWorkbook.Sheets("ServiceInvoice").Range("A3").Value = oRecordset.Fields("CustomerRefFullName")
ActiveWorkbook.Sheets("ServiceInvoice").Range("B4").Value = oRecordset.Fields("BillAddressAddr1")
ActiveWorkbook.Sheets("ServiceInvoice").Range("B5").Value = oRecordset.Fields("BillAddressAddr2")
ActiveWorkbook.Sheets("ServiceInvoice").Range("B6").Value = oRecordset.Fields("BillAddressCity") + " " + oRecordset.Fields("BillAddressState") + " " + oRecordset.Fields("BillAddressPostalCode")
ActiveWorkbook.Sheets("ServiceInvoice").Range("B9").Value = oRecordset.Fields("ShipAddressAddr1")
ActiveWorkbook.Sheets("ServiceInvoice").Range("B10").Value = oRecordset.Fields("ShipAddressAddr2")
ActiveWorkbook.Sheets("ServiceInvoice").Range("B11").Value = oRecordset.Fields("ShipAddressCity") + " " + oRecordset.Fields("ShipAddressState") + " " + oRecordset.Fields("ShipAddressPostalCode")
ActiveWorkbook.Sheets("ServiceInvoice").Range("D15").Value = oRecordset.Fields("TermsRefFullName")
ActiveWorkbook.Sheets("ServiceInvoice").Range("F15").Value = oRecordset.Fields("DueDate")
ActiveWorkbook.Sheets("ServiceInvoice").Range("F39").Value = oRecordset.Fields("SubTotal")

Dim count As Integer
count = 0
Do While Not oRecordset.EOF
    ActiveWorkbook.Sheets("ServiceInvoice").Range("B" + Trim(Str(18 + count))).Value = oRecordset.Fields("InvoiceLineItemRefFullName")
    ActiveWorkbook.Sheets("ServiceInvoice").Range("C" + Trim(Str(18 + count))).Value = oRecordset.Fields("InvoiceLineDesc")
    ActiveWorkbook.Sheets("ServiceInvoice").Range("D" + Trim(Str(18 + count))).Value = oRecordset.Fields("InvoiceLineQuantity")
    ActiveWorkbook.Sheets("ServiceInvoice").Range("E" + Trim(Str(18 + count))).Value = oRecordset.Fields("InvoiceLineRate")
    ActiveWorkbook.Sheets("ServiceInvoice").Range("F" + Trim(Str(18 + count))).Value = oRecordset.Fields("InvoiceLineAmount")
    count = count + 1
    oRecordset.MoveNext
Loop

oRecordset.Close
Set oRecordset = Nothing
oConnection.Close
Set oConnection = Nothing

End Sub

We need to add the below function for passing the RefNumber parameter value in the query.

Public Function Nz(ByVal Value, Optional ByVal ValueIfNull = "")

    Nz = IIf(IsNull(Value), ValueIfNull, Value)

End Function


Function FnRef(MyRef As String) As String

MyRef = Nz(MyRef, "")
FnRef = MyRef
End Function

You can check functionality by clicking on the "Enter Invoice #" button.

MS Excel will prompt for RefNumber, as shown in the below sample. Enter a RefNumber value and click "OK."

The report is available for particular RefNumber.

You can print the report by clicking "Print." The Invoices in MS Excel report format are shown below.

Please click here to download Sample Report.

Problem Description:

I am running the QODBC report "CustomSummary" and comparing it to what should be the identical report in QuickBooks. I am encountering an issue where the Balance Sheet accounts report zero amounts (vs. the values I see in QuickBooks). In contrast, the Income Statement accounts report the correct amount.

Could you advise if there is a parameter I need to change when running the stored procedure?

The procedure I am running is as follows:

sp_report CustomSummary show Label, Amount parameters DateMacro = 'LastYear', SummarizeRowsBy = 'Account', SummarizeColumnsBy = 'Month', Calendar = 'FiscalYear', ReturnRows = 'All', ReturnColumns = 'All'

I have set up the below parameter QuickBooks UI.

Solution:

You are facing a mismatch issue because QuickBooks SDK is passing the parameter AccountFilterType='IncomeAndExpense' by default in the report query & you have removed the Account Filter parameter from QuickBooks UI.

There is no option to remove the default account filter from the report query.

This is a limitation of the QuickBooks SDK that it does not support removing the default account filter.

QODBC is an ODBC driver for QuickBooks. It uses the QuickBooks SDK to communicate with QuickBooks, which means if Intuit doesn't expose one feature to the application in SDK, QODBC could not do it either.

We have raised enhancement request QBWG-47383 at Intuit's end to support removing the default account filter through QuickBooks SDK.

As a workaround, You need to follow the below procedure.

Run the report twice & export it in two separate Excel sheets.

Refer: How to create sp_reports using Microsoft Excel

1st using the filter AccountFilterType= 'BalanceSheet' (returning all rows and columns)

sp_report CustomSummary show Label, Amount parameters DateMacro = 'LastYear', SummarizeRowsBy = 'Account', SummarizeColumnsBy = 'Month', Calendar = 'FiscalYear', ReturnRows = 'All', ReturnColumns = 'All',AccountFilterType= 'BalanceSheet'

2nd, use the filter AccountFilterType='IncomeAndExpense' (returning all rows and columns).

sp_report CustomSummary show Label, Amount parameters DateMacro = 'LastYear', SummarizeRowsBy = 'Account', SummarizeColumnsBy = 'Month', Calendar = 'FiscalYear', ReturnRows = 'All', ReturnColumns = 'All',AccountFilterType= 'IncomeAndExpense'

Then join the two resulting datasets for the amount fields & update based on the logic of the 1st dataset amount minus the 2nd dataset amount (as the IncomeAndExpense amounts need to have their signs reversed). This produces the same result as running the Custom Summary report out of the UI with no AccountType filter.

Problem Description:

I would like to know if QODBC will convert QuickBooks Pro Desktop invoices into Microsoft Access.

The attached invoice is similar to what we need to export to Access. It doesn't have to be exact but needs to be able to show the descriptions, etc.

Solution:

You can export QuickBooks Invoices into MS Access report format using QODBC.

The first step is to create a new MS Access database & link the InvoiceLine table to MS Access.

Open MS Access. You can find it via Windows Start->All Programs->Microsoft Office.

A default database filename, Database1, appears in the box. You can edit this to a more relevant name, such as QuickBooks Link. accdb. Save the file as type "Microsoft Office Access Databases."

Then Click "Create" to create a blank database.

After the new database is created, Navigate to "External Data":

Build a connection via External Data->More->ODBC Database, or directly click the ODBC database button on the External Data menu:

Under the Get External Data window, you will get two options. We will use "Link to the data source by creating a linked table":

After selecting the access method and clicking OK, you will get a Select Data Source window.

Select the QuickBooks Data DSN you set up with the QODBC driver. This can be one of our pre-installed DSN names or one that you have created. (Here, I choose the default DSN as the Example.)

For 64-bit MS Office, change QRemote DSN to QuickBooks Data 64-bit QRemote is the default DSN created when installing QODBC.

Access will return a list of available tables in the Select Tables window. Please, select the tables you want to extract data from. In this example, I am selecting the InvoiceLine table.

The linked table was added to MS Access.

Now, We will design a new report from Create->Report Wizard

The Report Wizard shows showing Linked table & available fields.

Select the fields you want to show in the report & click "Next."

Report Wizard showing selected fields. Click "Next" for more configuration.

You can sort records in ascending or descending order up to four fields & click "Next."

Select report Layout & click "Next."

Insert the report name & select "Modify the report's design," & click "Finish" to design a report.

The report design window is opened & Drag the table field into the report as per your requirement & design the information.

Select table field assigns a group to table fields by clicking the "Add a Group" button.

The select field for Group. In this example, I have selected "TxnID" all group fields are grouped for a particular TxnID.

The Group is created. Click "More."

Select "With a header section" & "With a footer section" to enable Group Header & Footer.

If you want to print one invoice per page (i.e., Only one invoice on a single page), click on the group footer for TxnID & set the Force New Page property box drop-down arrow, and select Before Section.

The report is designed. You can add labels & assign a group to table fields. You can view the information by clicking on "View."

The actual report will look as below in View mode.

You can select "Print Preview" mode to see all Invoice reports.

The Invoices in MS Access report format are shown below.

Please click here to download Sample Report.

Problem Description:

I would like to know if QODBC will convert QuickBooks Pro Desktop invoices into Crystal Reports.

The attached invoice is similar to what we need to export to Crystal Report. It doesn't have to be exact but needs to be able to show the descriptions, etc.

Solution:

You can export QuickBooks Invoices into Crystal Report format using QODBC.

The first step is to create a new report.

The Database Expert Window will be open. Expand the "Create New Connection" Folder:

Expand the "ODBC (RDO)" Folder:

The following screen will appear. Select "QuickBooks Data" DSN and Click "Next," finally click "Finish" on this screen:

There is no UserID and Password required for connecting QuickBooks Data using QODBC. So you can click "Finish" directly.

A list of QuickBooks tables will appear and is ready for selection:

Crystal Report will return a list of available tables in the Select Tables window. Please, select the tables you want to extract data from. In this example, I am selecting the InvoiceLine table.

For the table added to Crystal Report, click "Next" to add table fields in the report.

The Report Wizard shows showing table & available fields.

Now, Select the field which you want to show in the report by clicking the ">" button & click "Next."

Add group information to the report. We will group data by selecting the TxnID field and, clicking the ">" button, then clicking "Finish."

The report designer will show selected fields.

Now, We will add Customer details, Billing & Shipping details, TxnDate, RefNumber, Terms, and DueDate in the Group Header section. We will add InvoiceLine information (i.e., Item, Description, Quantity, Rate & Amount field) in the detail section. We will add SubTotal in the Group Footer section.

You can add the caption in report data using Text Object from Menu Insert >> Text Object.

In this example, I have added the below Text Objects.

You can draw the box in the report using Menu Insert >> Box.

In this example, I have drawn a box around the "Ship To" details

You can draw a line in the report using Line from Menu Insert >> Line.

I have drawn the below line in the "Ship To" Box.

Same way, I have drawn Box & Line on other report details.

If you want to print one invoice per page (i.e., Only one invoice on a single page), then select "Section Expert" & click on the Group Footer, enable the "New Page After" drop-down arrow, and click "OK."

You can view the report by clicking on the "Preview" tab.

The actual report will look like the below sample in Preview mode.

You can add the RefNumber parameter in the report. So you can see the invoice for a particular Invoice#. You can add the RefNumber parameter in the information to limit the records or view a single invoice.

You can add parameters by right-clicking on "Parameter Fields" & click "New."

Insert the parameter name, Choose "Static" as a list of values & choose "RefNumber" in the value field and click "OK."

Add a parameter to the report by dragging and dropping.

We need to link the RefNumber parameter with the RefNumber value in the report.

For linking, Right-click on "RefNumber" & click "Select Expert...".

In the select expert window, select "is equal to" & "{?RefNumber}" parameter & click "OK."

When you run the report, the crystal report will prompt for RefNumber, as shown in the sample below. Enter a RefNumber value and click "OK."

The report available for particular RefNumber.

Please click here to download Sample Report.

Typical Profit & Loss Budget Overview By Class Report (for the current Fiscal Year to Date) in QuickBooks

Note: The QuickBooks Sample files set the QuickBooks date to December 15, 2021. The current Fiscal Year shown above is January 1 through December 15, 2021.

Report Displayed in QODBC

Open Microsoft Query in Excel

Note: Make sure you have installed Microsoft Query Add-on first. To learn how to install Microsoft Query, please refer to Microsoft Office with MS Query and QODBC.

Note: In recent versions of Microsoft Excel (including Excel 365), the Microsoft Query (Legacy) feature is hidden by default from the Get Data tab.
Please refer to Troubleshooting - How to enable Microsoft Excel 365 - Legacy Microsoft Query.

Please start Microsoft Excel & Create a blank workbook.

Please select the "Data" tab and click "From Other Sources->From Microsoft Query."

Create a New Microsoft Query in Excel

Select the DSN you want to use to connect to the QuickBooks file. (Here, I choose the default data source with a sample company file opened.)

Note: Uncheck option "Use Query Wizard to Created/edit Queries" since we do not need to import any QuickBooks tables here.

Close the "Add Tables" window by clicking the "Close" button.

Press the "SQL" button to input your sp_report query as below. Here we take a ProfitAndLossBudgetOverview report as an example:

sp_report ProfitAndLossBudgetOverview show Amount_Title, Text, Label, Amount parameters FiscalYear = 2021, BudgetCriterion = 'AccountsAndClasses', SummarizeBudgetColumnsBy = 'Class', SummarizeBudgetRowsBy = 'Account'

Press an OK button to get results in Microsoft Query:

Return Records to Excel Spreadsheet

Select Menu File -> Return Data to Microsoft Office Excel to return records to Excel Spreadsheet.

Choose how you want to import data into your workbook & click "OK."

Data exported in the workbook.

Note: QuickBooks does not support creating a budget by class or other criteria for the Balance Sheet. So it is not available through QODBC reports (i.e., BalanceSheetBudgetOverview & BalanceSheetBudgetVsActual).

Refer: Can you create a budget for balance sheet items by class?

How to identify exported report data relation.

Please refer below screenshot for exported report data relation.

Also, Refer

How to use the QuickBooks Reporting Engine with QODBC Online

QODBC Report Parameter & Schema details.

Problem Description:

For months I've been trying to get a Sales journal report. All I seem to get is either too detailed (by items) or too summary (sum by customer). I need a list of invoices by class.

A report by class showing CLASS, Inv #, Inv DATE, CUSTOMER NAME, Inv TOTAL AMT).

All I seem to get is either too detailed (by items) or too summary (sum by customer).

I haven't been able to figure out how. I'm extracting this info from a GL data dump, but I have to use lots of code and work around.

Is there such a report?

Solution:

You can refer SalesByCustomerDetail for the required details. Please refer to the sample query for the same.

For Example:

sp_report SalesByCustomerDetail show Text, Blank, TxnType, Date, RefNumber, Memo, Name, Item, Class, Quantity, UnitPrice, Amount, RunningBalance parameters DateMacro = 'ThisMonthToDate.'

Also, There are other Sales reports available you can refer below-listed reports:

SalesByCustomerSummary, SalesByItemDetail, SalesByItemSummary, SalesByRepDetail, SalesByRepSummary

Also Refer: How to use the QuickBooks Reporting Engine with QODBC

You can find most of the QuickBooks report using the above link. You can change report parameters as per your requirement.

Also, Refer QODBC table & report schema site to get details about each report column and supported parameters.

Problem Description:

How do I run a Collections Report?

Solution:

A typical Collections Report in QuickBooks looks like this:

With QODBC, the same report can be generated using stored procedure reports like this:

sp_report CollectionsReport show Text, Blank, TxnType, Date, RefNumber, PONumber, Terms, DueDate, Aging, OpenBalance parameters DateMacro = 'Today,' AgingAsOf = 'ReportEndDate.'

Problem Description:

I am executing BalanceSheetDetail, GeneralLedger, Journal, ProfitAndLossDetail, and TxnDetailByAccount reports & it is showing summary information which earlier showed detailed information. Rows are now grouped as "--Multiple--." 

Solution:

When you open the report from QuickBooks UI, you will notice the below message box.



If a message box appears, values are shown as consolidating in the QuickBooks UI report, and the same is with an exported report through QuickBooks SDK.

Changing your report preference from QuickBooks UI to see detailed row reports would be best. To change your preference, navigate to Edit Menu -> Preferences -> Report & Graphs -> Company Preferences Tab.



Uncheck the "Collapse Transactions" option & click "OK."



After changing the above option, you can get a detail row in the report in QuickBooks UI and the exported information through QuickBooks SDK.