How to run Job Profitability Summary Report Using QODBC

Note: When trying to extract a report showing total profitability per customer for all jobs. A sp_report named Job Profitability Summary could get what you want.

Syntax in QODBC

Normal Syntax in QODBC

sp_report JobProfitabilitySummary show AmountActualCost_Title, AmountActualRevenue_Title, AmountDifferenceActual_Title, Text, Label, AmountActualCost, AmountActualRevenue, AmountDifferenceActual parameters DateMacro = 'ThisYearToDate', SummarizeColumnsBy = 'TotalOnly'

Syntax with Parameters

If you're only interested in Customers with Jobs, then adding a Where RowType = 'SubtotalRow' will only show the totals:

sp_report JobProfitabilitySummary show AmountActualCost_Title, AmountActualRevenue_Title,
AmountDifferenceActual_Title, Text, Label, AmountActualCost, AmountActualRevenue,
AmountDifferenceActual parameters DateMacro = 'ThisYearToDate',
SummarizeColumnsBy = 'TotalOnly' Where RowType = 'SubtotalRow'

Syntax with Single Line Total for each of the clients

If you want to be able to get a single line total for each of the clients, including any jobs which may or may not exist for each client, try the query below:

sp_report JobProfitabilitySummary show AmountActualCost_Title, AmountActualRevenue_Title,
AmountDifferenceActual_Title, Text, Label, AmountActualCost, AmountActualRevenue,
AmountDifferenceActual parameters DateMacro = 'ThisYearToDate',
SummarizeColumnsBy = 'TotalOnly' Where RowType = 'SubtotalRow'
or {fn LOCATE(':', "RowData", 1)} = 0