Crystal Reports 2020 Not Dividing Correctly In Formula Fields?

[former_member764331]

I have a cross tab report that takes orders, amount of frames, total sales, etc. for each rep and shows how much they have each day and then totals. I am trying to add a formula field that simply takes the "Sales Amount" divided by "Frames" to give me the average cost per frame. When I do this calculation it is giving me a number that is not correct, I am not sure what is it doing to pull the number. For example on the screen shot in the total column, I want the bottom one (@Avg Frame) to be 5635/92 which equals 61.25, but it is giving me 115.55. The current formula in the field is

IF {Command.Sales Amount} = 0 THEN 0 ELSE {Command.Sales Amount}/{Command.Frames} .

When I alter the formula to just have {Command.Sales Amount} alone it shows the correct value (5,635) and when I do {Command.Frames} it shows 92. So then why am I not getting 61.25 when I do the division formula? Note the rep names aren't shown for privacy. Thank you!

[DellSC · Accepted solution]

The problem is that you can't use a formula like that in a Cross-Tab and have it work. Instead, you have to use Cross-Tab functions.

To do this, select the cell where you want to put the value, right-click and select "Embedded Summary" and then "Insert Embedded Summary". This will add a formula named {@Embedded Summary} to the cell. Right-click on the summary and select "Embedded Summary" and then "Edit Calculation Formula" I think the formula you want to use is something like this:

If GridValueAt(CurrentRowIndex - 2, CurrentColumnIndex, CurrentSummaryIndex-1) = 0 then
  0
else
  GridValueAt(CurrentRowIndex - 1, CurrentColumnIndex, CurrentSummaryIndex-1) / GridValueAt(CurrentRowIndex - 2, CurrentColumnIndex, CurrentSummaryIndex-1)

Unfortunately, the "Grid" functions will not appear in the function list and will cause a validation error if you're just creating a formula the standard way - you have to access the formula editor through the Cross-Tab as described above for them to work.

-Dell

[former_member764331 · Accepted solution]

Thank you, Dell. I inserted the Embedded Summary, but I am having some trouble with the formula. What I want is a formula that will divide each reps "Sales Amount" with their "Frames", so on each row, this calculation should be different since each row is a different rep. I am having trouble taking your formula and adding my fields into it.

[DellSC · Accepted solution]

The formula works relative to where it's at in the Cross-Tab - you don't need to add field names. You may have to tweak the relative CurrentRowIndex - x to get the correct fields.

-Dell

[former_member764331 · Accepted solution]

Thank you Dell. I haven't worked with this type of formula before so I was a bit confused. But after experimenting with it for a while I think I got it to do what I needed. Thank you very much for your help.