Knowledgebase: Sp_commands & Reports
[QODBC-Desktop] How to run Job Profitability Summary Report Using QODBC
Posted by brad waddell on 12 March 2009 05:21 PM

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

(108 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).