Hi,

i am using the sp_Report stored procedure to retrieve TxnID and Average cost data from InventoryValuationDetail report in VBDemo. Although the query works fine it takes too long. My QuickBooks company data has approximately 600 items and each item is having a different average cost for different transactions. I need the data for all fiscal years so i have to use the DateMacro 'All'.

The query that i am using is:

sp_report InventoryValuationDetail show TxnID, TxnType,Quantity, AverageCost parameters ItemFilterListIDs='Put_ItemID_Here',TxnFilterTypes='All',DateMacro = 'All'

This query is taking alot of time and i am looking for a much more effcient way to get the TxnID and Average Cost data from QuickBooks. One more strange thing that i noticed here was that when i remove the TxnID field from the query the performance improves by almost 50%. But i cant use this option because the TxnID of the transaction is very important for me.

I would really appreciate if someone could help me with the solution to this query.

Thanks in advance!

Dinesh

The QODBC Version information is:

Version 9.00.00.253

Well that wasn't very helpful, I have no idea what version of QuickBooks you are using? Anyway, if your company file is large doing any report with DateMacro = 'All' can take a long time. You should consider using something like:-

sp_report InventoryValuationDetail show TxnID, TxnType,Quantity, AverageCost parameters ItemFilterListIDs='Put_ItemID_Here', TxnFilterTypes='All', DateFrom = {d'2007-01-01'},  DateTo={d'2010-01-01'}

to cover the years of real interest (value) you need instead. Also setting your company file (or QODBC connection) to single user mode can speed up reports.

Thank you very much Tom!

The QuickBooks Edition is 'QuickBooks Enterprise Solutions 9.0.' I am sorry i didnt include that in my previous post.

I will try the date filter solution but in anycase i need records from all fiscal years. Could you please suggest some alternate solution, is there any table from where we can get the TxnID and the AverageCost field values?

Thanks for all your help!

Dinesh

Hi Tom,

After referring to some of the solutions provided by you in the previous post, i replaced the TxnID field in the original Stored Procedure execute statement with RefNumber field. Now the query is running much faster. Thanks

Original Execute Statment:

sp_report InventoryValuationDetail show TxnID, TxnType,Quantity, AverageCost parameters TxnFilterTypes='All', Datefrom = {d'2007-01-01'}

Revised Execute Statement:

sp_report InventoryValuationDetail show RefNumber, TxnType,Quantity, AverageCost parameters TxnFilterTypes='All', Datefrom = {d'2007-01-01'}

However i am really puzzled because of this. Why does the same query take so long to execute if i include 'TxnID' field in the show or where statements. But if i remove the 'TxnID' field then the query runs substantially faster?

Can you please explain this to me.

Thanks again for your valuable help!

Dinesh