Hi

I am trying to extract a report showing total profitability per customer, for all jobs.  The closest I can get is "Job Profitability Summary" but that shows sub amounts for each job under a customer.

Is there a parameter I can pass to this sp_report command, to output a single liune total per customer and to NOT include the inidividual job totals?:

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

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'

It's a mixture, unfortunately - I have some clients with jobs, and some without - eg:

Client 1

             Job 1

             Job 2

Client 2

Client 3

             Job 1

             Job 2

So, I want to be able to get a single line total for each of the 3 clients, including any jobs which may or may not exist for each client.

Is there a way to do that?

Thanks

Ok, this did the trick for me:

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

Hi Tom

Thanks for your reply - your suggestions was a big help, and it's almost working how I want it to.

I still have one small problem - the query you have given me correctly displays the subtotals for thos customers that have multiple sub-jobs, and correnlty displays only the customer total for thos customers who have NO sub-jobs.

BUT, for those customers which DO have sub-jobs, and who also have some sales invoiced to the main customer entity (not a sub-job), the query also displays a "$customername - Other" row which contains the total profit invoiced to the main customer (not any of the sub-jobs).

I have tried variations on your use of the LOCATE function, but so far with no success - for example:

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 and {fn LOCATE('Other', "RowData", 1)} = 0

I've also tried putting the 2 locates together in brackets, but that didn't work either:

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 and {fn LOCATE('Other', "RowData", 1)} = 0)

These variations both appeared to produce the same output as the query you gave me.

So - is there a way to supress those extra rows with "- Other" at the end of them?

Your help would be appreciated.

Thanks.