Sum calculated field by Group 1

[IceRuby]

Crystal reports version 10, ODBC connection to SQL 2000 database

I am creating an employee FTE report, drawing on payroll summary records.
Records are limited via range parameter linked to payroll end date field.

To calculate FTE value I need to sum eft records, per employee and divide this total by the number of pays within parameter date range. If the total, per employee is above 1 then total value should be 1 else just the summed value.

e.g. {@eft total}
if sum({eft record}, {employee no})/no of pays > 1 then 1 else sum({eft record}, {employee no})/no of pays

From here is where I am having trouble, total employee values must then be reported by type. (Group #1)

e.g
Group #1 (Asco)
if {dbo_t101_position.t101f155_asco_code} = "1" then "Managerial" else
if {dbo_t101_position.t101f155_asco_code} = "2" then "Professional" else
if {dbo_t101_position.t101f155_asco_code} in ["3", "4", "7", "9"] then "Operative" else
if {dbo_t101_position.t101f155_asco_code} in ["6", "5", "8"] then "Clerical"

Group #2 (Employee no) (suppressed)

Details (suppressed)
{Emp no.} {eft record} {pay end date}

Group # 2 (footer)
{@eft total)

Report should look like:-

Managerial 3.50
Professional 2.25
Operative 103.11
Clerical 55.22

EFT calculated value for each employee (Group #2) works fine but cannot get Crystal to sum employee totals for Group #1 (Asco)

Appreciate your assistance.

 

[lbass]

Use a variable. Create three formulas:

//{@reset} to be place in GH1:
whileprintingrecords;
numbervar sumeft;
if not inrepeatedgroupheader then
sumeft := 0;

//{@accum} to be placed in the Group #2 header or footer:
whileprintingrecords;
numbervar sumeft := sumeft + {@yoursummary};

//{@display} to be placed in the GF1:
whileprintingrecords;
numbervar sumeft;

-LB

 

[IceRuby]

Thanks very much, worked a treat.

Can you point me to any references on how and when to create variables vs using crystal summation or running totals?

 

[Madawc]

In reply to your second question, IceRuby, variables are more work, but also give you more control. It's a matter of style: I avoid them except for functions where only variables can be used. This includes Shared Variables, which are used to pass back values from subreports for further processing.

Broadly, there are several ways to find totals: running totals, summary totals and variables. Right-click on a field and choose Insert to get a choice of Running Total or Summary. Or else use the Field Explorer, the icon that is a grid-like box, to add running totals.

Running totals allow you to do clever things with grouping and formulas. They also accumulate for each line, hence the name. The disadvantage is that they are working out at the same time as the Crystal report formats the line. You cannot test for their values until after the details have been printed. You can show them in the group footer but not the group header, where they will be zero if you are resetting them for each group.

Summary totals are cruder, but are based directly on the data. This means that they can be shown in the header. They can also be used to sort groups, or to suppress them. Suppress a group if it has less than three members, say. They default to 'Grand Total', but also can be for a group.

Variables are user-defined fields. One useful variant are shared variables to pass data from a subreport back to the main report. You can also use variables to show page totals. For normal counting I find running totals or summary totals much easier.

Directly Calculated Totals within a Formula Field can be coded directly, with commands like Sum ({ADV01.Advance}, {ADV01.AccType}). The same result can be achieved by picking up an existing Variable, and will keep the code even if the Variable itself is later deleted. Formula fields can also include Running Totals and other Formula Fields, with some limits depending on when the values are calculated.

It is also possible to get get totals using a Formula Field, which can contain a Variable or a Directly Calculated Total.

To get yourself familiar with the idea, try doing a test report with a summary total and a running total for the same field, placed on the detail line. You'll find that the running total increases as each line is printed, whereas the summary total has the final value all along.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10