Crystal Reports 10: Nested Formula

Question

I've a report which shows five different activity codes. I'd like to sum all like codes and place a total for each of the 5 activity codes in a group footer. I have it working for one activity code, but would like to write a formula which will look for all five and total each. This is what I have:

(if {@activitycode_id}= "Not Ready" then {iActivityCodeStat.ActivityTime}else 0)

What is the proper syntax to add additional ifs into one formula? And then the best approach to totaling each one?

Thank you!

Answer 1

The two ways that I would do this are to use the switch statement or use running totals.

In this case you are specifying that if the activity code is "Not Ready" then use the ActivityTime and if you added to this it would lump the other values into this formula field. That said, I don't think this is the method you want to use.

For what you want, I think you'd be better suited to create a running total field based on {iActivityCodeStat.ActivityTime} and in the "Evaluate" section you should use a formula for each status like:

{@activitycode_id}= "Not Ready"

Then when you put each of these formula fields in your group footer you will see a total for that section where the activity code is "Not Ready". Don't forget to set the "Reset" section in the setup of the running total to "On change of group" and select the group the the field is in so that you get a different value for each section and not a running total of all sections.

Hope this helps.

Answer 2

If place of the if-then-else statement, try the select-case structure.

Select {@activitycode_id}

Case "Not Ready": {iActivityCodeStat.ActivityTime}
Case "Sleeping": {iActivityCodeStat.ActivityTime}
//else
Default: 0

If you are trying to total each activity code independently, you could insert a CrossTab.

If you want more flexibility than a CrossTab, you'll need to have a formula field that isolates each activitycode so it can be summed individually. For example:

//{@Not Ready}
If {@activitycode_id}= "Not Ready" Then {iActivityCodeStat.ActivityTime} Else 0

//{@Sleeping}
If {@activitycode_id}= "Sleeping" Then {iActivityCodeStat.ActivityTime} Else 0