Tom,

I've got the v7 driver installed and pulled some example reports from QB and now my customer wants a combination of Job Estimates vs Actuals Summary and Time by Job Summary reports.

Where can I find the SQL behind these stored procedures? I need to get the Total hours by active job and combine that with some calculations in a Estimates vs Actuals report.

Thanks

There's no SQL behind sp_reports. sp_reports simply calls the QuickBooks reporting engine in a similar way the QuickBooks user interface works using a range of show columns and parameters. I would need to see an example of the two reports and what you want to combine to see if I can extract the information like I did in: Table Releations for TimeByName 

The reports are:

Time by Job Summary

and filtered by:

With a report:

Time by Job report:

I need to filter the hours by active estimate but I'm using the names of the jobs in this filter because there isn't a active estimate option:

That produces a report like:

When I have the hours numbers for each job I need to use them as a Job Time column in the final report so I can calculate total spend on each job.

Thanks for all your help.

Because you using Excel, I suggest you run both the How do I run a Job Estimates vs. Actuals Summary Report? and How do I run a Time by Job Summary Report? reports within your worksheet and create the calculated columns as outlined.

To match the Job Estimates vs. Actuals Summry Report lines a little better try:

sp_report JobEstimatesVsActualsSummary show Label, AmountEstCost_1 as "Est. Cost",
AmountActualCost_1 as "Act. Cost", AmountEstRevenue_1 as "Est. Revenue",
AmountActualRevenue_1 as "Act. Revenue"
parameters DateMacro = 'All', SummarizeColumnsBy = 'TotalOnly'
where Label > ' ' and Label not like 'Total%'

Tom,

I'm running a Time by Job Summary Report like this:

• sp_report TimeByJobSummary show Label, DurationHours_1 as "TotalHours" parameters DateMacro = 'All',SummarizeColumnsBy = 'TotalOnly' where Label like 'Total%'

and it is pulling back all of the jobs with their total hours.

How do I query for just the active jobs? My client has 17 active jobs but the query returns 440 rows.

Same thing with the JobEstimatesVsActualSummary query:

• sp_report JobEstimatesVsActualsSummary show Label, AmountEstCost_1 as "Est. Cost",
  AmountActualCost_1 as "Act. Cost", AmountEstRevenue_1 as "Est. Revenue",
  AmountActualRevenue_1 as "Act. Revenue"
  parameters DateMacro = 'All', SummarizeColumnsBy = 'TotalOnly'
  where Label > ' ' and Label not like 'Total%'

This query returns 310 rows but the customer only has 17 active jobs.

Thanks for your help.

Ok, for the first report, try:-

sp_report TimeByJobSummary show Label, DurationHours_1 as "TotalHours" parameters DateMacro = 'All',SummarizeColumnsBy = 'TotalOnly', ReturnRows='ActiveOnly' where Label like 'Total%'

I couldn't find an active parameter for the second report, so you will need to name the 17 jobs you are only interested in instead:-

sp_report JobEstimatesVsActualsSummary show Label, AmountEstCost_1 as "Est. Cost",
AmountActualCost_1 as "Act. Cost", AmountEstRevenue_1 as "Est. Revenue",
AmountActualRevenue_1 as "Act. Revenue"
parameters DateMacro = 'All', SummarizeColumnsBy = 'TotalOnly' ,
EntityFilterFullNames = 'FullName1', 'FullName2', 'FullNameN'
where Label > ' ' and Label not like 'Total%'

Tom,

I couldn't get this query to work

• sp_report JobEstimatesVsActualsSummary show Label, AmountEstCost_1 as "Est. Cost",
  AmountActualCost_1 as "Act. Cost", AmountEstRevenue_1 as "Est. Revenue",
  AmountActualRevenue_1 as "Act. Revenue"
  parameters DateMacro = 'All', SummarizeColumnsBy = 'TotalOnly' ,
  EntityFilterFullNames = 'FullName1', 'FullName2', 'FullNameN'
  where Label > ' ' and Label not like 'Total%'

Couldn't figure out what value FullName needed to be.

But this query returns good data:

• sp_report JobEstimatesVsActualsSummary show Text, Label, AmountEstCost_1 as "Est. Cost",AmountActualCost_1 as "Act. Cost", AmountEstRevenue_1 as "Est. Revenue", AmountActualRevenue_1 as "Act. Revenue" parameters DateMacro = 'All', SummarizeColumnsBy = 'TotalOnly', EntityFilterFullNamewithchildren = 'Layton Construction'

The problem is I can't get the query to work with more than one EntityFilter. How do I do that?

The TimeByJobSummary  query:

• sp_report TimeByJobSummary show Label, DurationHours_1 as "TotalHours" parameters DateMacro = 'All',SummarizeColumnsBy = 'TotalOnly', ReturnRows='ActiveOnly' where Label like 'Total%'

returns all rows. What field on the job time records do the ActiveOnly parameter look at?

Would QReportBuilder make this report more doable or would it be easier to pull the QB data into sql server express and do regular queries?

Thanks

EntityFilterFullNames are found in the RowData column, for example:

sp_report JobEstimatesVsActualsSummary show RowData, Label, AmountEstCost_1 as "Est. Cost",
AmountActualCost_1 as "Act. Cost", AmountEstRevenue_1 as "Est. Revenue",
AmountActualRevenue_1 as "Act. Revenue"
parameters DateMacro = 'All', SummarizeColumnsBy = 'TotalOnly' ,
EntityFilterFullNames = 'Abercrombie, Kristy:Remodel Bathroom',
'Lamb, Brad:Room Addition', 'Pretell Real Estate:75 Sunset Rd.'
where Label > ' ' and Label not like 'Total%'

 For the TimeByJobSummary report try using IN instead with a Total prefix in front of the Customer's Job FullName:

sp_report TimeByJobSummary show Label, DurationHours_1 as "TotalHours"
parameters DateMacro = 'All',SummarizeColumnsBy = 'TotalOnly',
ReturnRows='ActiveOnly' where Label IN ('Total Abercrombie, Kristy:Remodel Bathroom',
'Total Lamb, Brad:Room Addition', 'Total Pretell Real Estate:75 Sunset Rd.')

Tom,

Well, querying for specific customers:jobs worked out good. In VB Demo it pulls back good record sets. When I tired to get this into Excel I ran into a 255 character limit on the SQL. Any ideas of how to get around this?

Would I be able to do this easier in QReportsBuilder? Pulling this data through Excel is getting pretty convuloted. Would QReportsBuilder be able to pull this data into a passive link table and then into a report?

Thanks

Chris