Troubleshooting - QODBC CustomSummary report is not reconciling with QuickBooks CustomSummary.

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.