Crystal Reports 2013 - Calculation in Cross Tab

[Former Member]

I have a report where I have two numbers in each cell of a cross tab, and I need to get a third by dividing one of the numbers by the other. I am displaying room revenue and occupied rooms, and I need to calculate the third field to get Average Daily Rate.

I did try creating an actual ADR field in the report wherein I divide

sum({fdroomstat.room_revenue})/sum({@Rooms Occupied})

But then when I add it to Summarized Fields I am forced to use an aggregation so I use AVG... I thought that worked fine for each individual cell but I have just realized that's not even the case.

In any case, when you get to row and column totals, instead of adding all the revenue and dividing by all the rooms occupied, you get a random number that is meaningless.

What am I missing? I am certain this is simple but I just cannot see how to do it.

Any help would be much appreciated! Thank you.

[abhilash_kumar · Accepted solution]

Hi Rayna,

Try this please:

1. Right-click the second summary cell > Embedded Summary > Insert Embedded Summary.

2. A blank cell with the text "Edit this formula" will be inserted.

3. Right-click this new cell > Embedded Summary > Edit Calculation Formula > Use this code:

GridValueAt(CurrentRowIndex, CurrentColumnIndex, 1) / GridValueAt(CurrentRowIndex, CurrentColumnIndex, 0)

-Abhilash

[Former Member · Accepted solution]

Abhilash,

After searching this site for some three hours yesterday for this solution and noting that you provided nearly every correct answer relating to cross tab questions, I rather thought you might know the way to do this. You did not disappoint!!

Thank you ever so much. You are a genius! This worked like a charm.

Rayna

[abhilash_kumar · Accepted solution]

I'm glad I could help!

-Abhilash