Summary Fields and Calculation Fields

[Toddski]

Background:

I have a survey database for our company. It surveys employees about their training needs. We ask employees to rank how often they use a particular software and how important training would be.

I have created summary fields that give me the Averages of importance.

Now I want to display the data in a report. I have a sub-summary section when sorted by Training Program name that displays the Avg of Importance for each training program.

The problem:

I need a field that is calculated based on the Importance average Summary Field. If the average is between 4 and 6 display "Important." If the Average is between 6 and 10 display "Very important."

Case and If statements do not seem to work because when you look at the report I always get "Important."

I believe the problem is that my calculation field is not a summary field but you cannot write a custom summary field.

So is there a way to use a calculation in a sub summary?

Thanks,

Todd

Edited July 10, 2007 by Guest

[comment]

You need to use the GetSummary() function in order to reference a sub-summary in a calculation. For example:

Let (

averageByObject = GetSummary ( sAverage ; ObjectID )

;

Case (

averageByObject > 6 ; "Very Important" ;

averageByObject > 4 ; "Important" ;

"Not Important" )

)

Make sure the breakField parameter is the same field used to define the sub-summary part on the layout.

[Toddski]

How the heck did I miss that one.

Works great!

Thanks