Knowledgebase
[QODBC-Desktop] How to create sp_reports using Microsoft Excel 2007
Posted by Juliet (QODBC Support) on 25 July 2011 06:05 AM

How to create sp_reports using Microsoft Excel 2007

Instructions

To extract sp_reports from QuickBooks into Microsoft Excel Spreadsheet, you should use Microsoft Query to accomplish that.

 

How to extract sp_reports using Microsoft Excel 2007

Open Microsoft Query in Excel

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

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

 

Create a New Microsoft Query in Excel

Select the specified DSN you want to use to connect to QuickBooks file.

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 "SQL" button to input your own sp_report query as below, here we take a CustomerBalanceDetail report as an example:

Press Execute button to get results in Microsoft Query:

 

Return Records to Excel Spreadsheet

To return records to Excel Spreadsheet, select Menu File -> Return Data to Microsoft Office Excel.

(80 vote(s))
Helpful
Not helpful

Comments (5)
Pepper
20 September 2012 03:56 PM
Extremely helpful article, plasee write more.
Jose Cordova
19 January 2018 05:45 PM
Can I make the dateto or datefrom parameters to be asked or prompt in a Excel report?
Thanks
Jose
Jack
22 January 2018 07:48 AM
Hi Jose,

Yes, You can Use Prompted Date Ranges in MS Excel with sp_reports.

Please refer:
https://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2991
Christine
28 March 2018 07:28 PM
Is there a way to exclude the subtotals when using an sp_report? In particular I would like the AR Aging Detail to return the detail without the subtotals.
Jack
02 April 2018 12:59 PM
Hi Christine,

In QODBC, You can remove subtotals and blank lines in sp_report by adding below condition.

where RowType='DataRow'

For example:

sp_report UnpaidBillsDetail show Text, Blank, TxnType, Date, RefNumber, DueDate, Aging, OpenBalance parameters DateMacro = 'Today' where RowType='DataRow'

sp_report ARAgingDetail show Text, Blank, TxnType, Date, RefNumber, PONumber, Name, Terms, DueDate, Aging, OpenBalance parameters DateMacro = 'Today', AgingAsOf = 'Today' where RowType='DataRow'
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).