The issue:
The client had a created a Annual and Monthly Sales cross-tab report in Crystal Report, which summarised their sales and cost of sales figures by Product, but wanted to calculate a the Percentage Gross Profit
The Solution:
Since Crystal Reports 2008 this calculation has got a whole lot easier with the addition Grid functions
- Right click on any of the Sum of Cost of Good cells on the cross tab
- Select Embedded Summary - Insert Embedded Summary
This will add a 3rd row to the cross-tab, displaying Edit this formula
- Right click on the Edit this formula
- Select Embedded Summary - Edit Calculation Formula
- Enter the following formula:
CurrencyVar Sales := GridValueAt (CurrentRowIndex, CurrentColumnIndex, CurrentSummaryIndex - 2);
CurrencyVar COG := GridValueAt (CurrentRowIndex, CurrentColumnIndex, CurrentSummaryIndex - 1);
If Sales = 0 then 0 else (Sales - COG) % Sales
- Save the formula
Crystal Reports displays the calculation values, on all Group levels, includingr row and column Grand Totals
Note:
The formula works as follows:
The Cross-tab has the summaries in the following order
1st row Sum of Sales 2nd row Sum of Cost of Sales
In the first line of the formula, Sales, references a row 2 rows up from the calculated row, and hence the COG refers to the values 1 row up.
If you have any questions, leave us a comment below, or need any assistance, please do not hesitate to Contact Us |
