Knowledgebase: QODBC
[QODBC-Desktop] Troubleshooting - QODBC CustomSummary report not reconciling with QuickBooks CustomSummary.
Posted by Jack - QODBC Support on 15 June 2017 10:23 AM

Troubleshooting - QODBC CustomSummary report not reconciling with QuickBooks CustomSummary.

Problem Description:

I am running the QODBC report "CustomSummary" and comparing to what should be the identical report in QuickBooks and I am encountering an issue where the Balance Sheet accounts are reporting zero amount (vs. the values I see in QuickBooks), while the Income Statement accounts appear to be reporting 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:

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

I have setup below parameter QuickBooks UI.

Solution:

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

There is no option to remove 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 end for support removing the default account filter through QuickBooks SDK.

As a workaround, You need to follow below procedure.

Run the report twice & export in two separate Excel sheet.

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 using 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 and for the amount fields & update based on the logic of 1st dataset amount minus 2nd dataset amount (as the IncomeAndExpense amounts need to have their signs reversed). This produces the same end result as running the Custom Summary report out of the UI with no AccountType filter.

(3 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).