Crystal Reports & SQL Reporting Services Training Consultants
Account Log in:
Username:
Password:
Don't let today's disappointments cast a shadow on tomorrow's dreams
 
Unknown
Institute of IT Trainers - Freelance Trainer of the Year 2006 & 2009
Liverpool Business Connect Member
  Maximum Impact Solutions Limited - Reporting Solutions, Creating Answers
Reporting Solutions - Creating Answers, Crystal Reports, Dashboarding (Xcelsius) & SQL Reporting Services

The Maximum Impact Solutions Blog Feed ME!!!!

15
January

How Do I Create a Cross Tab Ratio Calculation in Crystal Reports XI

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

  1. Right click on any of the Sum of Cost of Good cells on the cross tab
  2. Select Embedded Summary  - Insert Embedded Summary

    This will add a 3rd row to the cross-tab, displaying Edit this formula

  3. Right click on the Edit this formula
  4. Select Embedded Summary - Edit Calculation Formula
  5. 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

  6. 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

MAKE A COMMENT

Name *

Email Address *

Comment *