Hi - I am an old hand at QuickBooks but new to QODBC, SQL and Crystal Reports.  I am using QODBC v.8, QuickBooks 2008 and Crystal Reports 8.5 (old, I know).

I would like to generate a Job Profitability Summary report showing only jobs that were invoiced last month, but showing all job costs regardless of date.  The report in QuickBooks limits both revenue and cost by the date range entered, so I've been fooling around with trying to get my results from the stored procedure.  Thoughts:

• Maybe if I could see the SQL behind the stored procedure, I could figure out how to edit it to get the results I want.  I have run across  the sp_helptext command but it doesn't seem to work in this world - not supported by QODBC?
• Maybe I could use the stored procedure in combination with SQL in Crystal reports.  One problem is that Crystal Reports 8.5 does not have the Add Command; I think I have to instead use Crystal SQL Designer to generate the query, which appears to lock me out of making any changes in Crystal Reports.  I would upgrade to Crystal XI if I had some confidence that I will be able to modify.

Thanks for any help you can give me!

There's no SQL behind QODBC Stored Procedures. They are fixed and simply call the QuickBooks reporting engine. See: Using Job Type parameter for Job Profitability Detail Report for the SQL Statement to use in your report.

See also: How do I use the QuickBooks Reporting Engine with QODBC? I've heard something about sp_report ? 

Thanks for your quick reply, Tom!

Any thoughts about how to get the Job Profitability Summary report I need?

I am intrigued by this comment in the ODBC documentation: 

"Use sp_report (see Data Layouts) to use the QuickBooks report engine that already “relates” the tables. You can relate an sp_report and a table to get a variety of results".

How does one relate a table to an sp_report in Crystal Reports?  Could I somehow relate an sp_report to the customer:jobs table to filter my results by job status?  Or by jobs that are active?

Unfortunately I am using Crystal Reports 8.5, (no Add command) but it would be worth upgrading if I could get this report filtered the way I want.

Thanks!

The statement is saying that a sp_report result set could be linked to a table to expose more information. As the actual link is on a detail result (not a summary or total), it would normally be done using a sub-report in Crystal Reports XI. The standard syntax for a JobProfitabilitySummary is:

sp_report JobProfitabilitySummary show Text, Label, AmountActualCost, AmountActualRevenue,
AmountDifferenceActual parameters DateMacro = 'All', SummarizeColumnsBy = 'TotalOnly'

Use

SP_PARAMETERS JobProfitabilitySummary

to list all the parameters available to you.