Crosstab report.
Row by customer name.
Columns by month.
Also a grand total column
Two summarized fields: Profit & TotalRevenue
I added Embedded Summary to divide Margin by TotalCharge to produce margin: profit as a percent of total revenue by customer.
Here is the embedded summary calculation formula:
If
GridValueAt(CurrentRowindex, CurrentColumnIndex, CurrentSummaryIndex-1) = 0
then
0
else
(GridValueAt(CurrentRowindex, CurrentColumnIndex, CurrentSummaryIndex-2)
/
GridValueAt(CurrentRowindex, CurrentColumnIndex, CurrentSummaryIndex-1) )
*100
This all works, but how do I now get my rows to sort by the profit % (i.e. by the Embedded Summary, or at least by the same formula)? I tried adding a formula Profit/TotalRevenue to the report (not to the Crosstab) on the theory that I could then sort by it, but that generated the overall margin% for all customers combined and put it on each row, rather than calculating the margin within each row.
Edited by brain - 19 Nov 2017 at 8:03pm
