Formula in Group Footer Only Evaluating Last Row

[lesleycampbell]

I am using CR11 and working on an invoice report. One employee will have multiple pay codes listed on the invoice.

In order to format according to client's request, I am working on aligning all pay codes in one horizontal line versus each pay code listed as an individual line item.

Example: John Smith HOUR 40 @ $10.00
John Smith OVER 12 @ $15.00

In the above example, "HOUR" and "OVER" are the pay codes.

The client would like it to read as follows:
Example: John Smith HOUR 40 @ $10.00 OVER 12 @ $15.00

I am attempting to do this by suppressing the detail section and using the group footer. I am using running totals for the units and rates but I am having trouble with the formula I am using for my pay code. There are certain groups of pay codes where the employee will only have one of them. I want my field to display the pay code that is displayed for the employee.

Pay Codes: "MED-EC", "MED-EM","MED-ES","MED-FA"
Formula I'm currently using: IF {PAY.CODE} IN ["MED-EC", "MED-EM","MED-ES","MED-FA"] THEN {PAY.CODE} ELSE " "

Right now, it is only evaluating the last row in the details.

Any suggestions?

 

[hilfy]

You'll need to use a variable and a set of formulas for this. It might look something like this:

{@InitVar}
StringVar Array paycodes := ["","","",""];
""

Put this formula in the group header. It will display an empty string, so it won't actually show anything.

{@SetVar}
StringVar Array paycodes;
switch(
{Pay.Code} = 'MED-EC', paycodes[1] := 'MED-EC % @ $' + ToText({Pay.HourlyAmount}, 2) + ' ',
{Pay.Code} = 'MED-EM', paycodes[2] := 'MED-EM % @ $' + ToText({Pay.HourlyAmount}, 2) + ' ',
{Pay.Code} = 'MED-ES', paycodes[3] := 'MED-ES % @ $' + ToText({Pay.HourlyAmount}, 2) + ' ',
{Pay.Code} = 'MED-FA', paycodes[4] := 'MED-FA % @ $' + ToText({Pay.HourlyAmount}, 2) + ' '
);
""
Put this formula in the details section (even though it's suppressed!) If it doesn't calculate in the suppressed section, make the field as small as possible and the details section as narrow as possible so that it doesn't take up much space.

Now, assuming that you have a running total for each of the four paycodes (I'll name these based on the last two characters of the pay code), you'll do something like this:
{@ShowVar}
StringVar Array paycodes;
StringVar result := '';
If paycodes[1] <> '' then result := Replace(paycodes[1], '%', ToText({#EC Hours}, 0));
If paycodes[2] <> '' then result := result + Replace(paycodes[2], '%', ToText({#EM Hours}, 0));
If paycodes[3] <> '' then result := result + Replace(paycodes[3], '%', ToText({#ES Hours}, 0));
If paycodes[4] <> '' then result := result + Replace(paycodes[4], '%', ToText({#FA Hours}, 0));
result

Put this in the group footer to get the information you're looking for.

These are just examples - you'll have to tweak them based on your data and the actual names of the running totals.
-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com