Array Summary Function or something more efficient? 1

[lhendrickson]

I am using Crystal 9.0 against Lawson tables.

I have 2 tables an employee table that has one row of detailed employee information, and one table with salary information by type (overtime,foreign assignment, underwater,etc)for each employee. In the salary table there are up to 20 rows per employee because of all of the different categories of salary (it is identified by a 3 letter code).

example:

Employee Table:
5452 Ed Harris 548-15-8875 12/1/1974
1011 James Davis 999-99-9999 6/6/1984

Salary Table:
5452 TIF $1,000
5452 OTH 200
5452 DIT 150
1011 TIF 9,900
1011 DDR 900

I want to create a report that has one row for each employee and has columns to the right that sum the salary amount (by summing specific salary codes) beside the appropriate employee.

Example: TIF+OTH DIT Total
5452 Ed Harris $1,200 $ 150 $1,350.00

In Excel I would use a Sum If array formula to pull the data needed, but I am not sure what to use in Crystal.

Any ideas will be appreciated!

lhendrickson

 

[synapsevampire]

Since you're not using the columns directly, rather soem customized sum of multiple columns, you'll need to do this manually.

Join the tables, group by employee, and then use separate Running Totals with the evaluate use a formula to specifically state which salary category to use, as in the following for the first example:

{salary,cat} in ["TIF", "OTH"]

And then:

{salary,cat} = "DIT"

for the next Running Total.

-k

 

[synapsevampire]

Forgot to state that you should display all of the results in the group footer, and just hide the details.

-k

 

[lhendrickson]

Thanks for your quick response, I will try this right away and let you know the results.

LHendrickson

 

[lhendrickson]

Thank You Synapsevampire -- it works beautifully!!!!

I appreciate your time and expertise!

Lhendrickson

 

[lhendrickson]

Through deeper investigation I found that the solution does not entirely work because of the table set up. I thought the table listed the employee #s in order and grouped each employee together, but I am finding that the employee numbers show up multiple times in random order (rather than grouped).

My Running total currently resets on change in employee number - This does not provide a true total for an employee, only a total for the first group of that employee's deductions.

example:

Salary Table:
5452 TIF $1,000
5452 OTH 200
1011 TIF 9,900
5452 DIT 150
1011 DDR 900

If I were summing up TIF, OTH, and DIT into one column in my running total - employee 5452's sum would equal $1,200 rather than $1,350. Short of having the table sort numerically before the running total evaluates the data (which isn't possible unless it can be done through Crystal), I am not sure what to try next.

Any ideas will be appreciated!

lhendrickson

 

[synapsevampire]

Select Report->Record Sort Expert and sort the rows.

-k

 

[lhendrickson]

synapsevampire,

I am already sorting by employee last name.

After reviewing the post I forgot to mention that the formula would not work as you originally proposed:
({salary,cat} in "TIF", "OTH&quot The system would only evaluate the first code and would error that "the remaining text does not appear to be part of the formula".

The formula that the system would accepts is more like:
{salary,cat} = "TIF" or
{salary,cat} = "OTH"

and so forth. In one of the columns there is actually 18 of these or statements due to a large number of codes. Could the problem be with this formula rather than the sorting of the records?

LHendrickson

 

[lbass]

You can sort by more than one field, so if you use the running total method, add {salary.cat} as your second sort field. SV's formula didn't appear correctly in his post because TGML processing was checked for the post--I think he meant:

{salary.cat} in ["TIF","OTH"] //for the running total evaluation formula

-LB

 

[lhendrickson]

lbass,

I added the second sort criterea and changed the "in" formula to the syntax in your suggestion. Although the in formula was accepted as valid, neither of my changes made any difference to the incomplete totals on the report.

Thanks for you input anyway - I will keep trying!

Lhendrickson

 

[Ngolem]

I don't really see the problem here...Also I think this is an easy 3 sum formula solution.

Group your data on Employee.ID for group 1 then Salary.code for group 2.

Suppress the Group 2 header & Footer as well as the details

There seems to be extra codes that you haven't mentioned

and so forth. In one of the columns there is actually 18 of these or statements due to a large number of codes.

Is DDR one of these codes??? How is this added into the mix? or is it reported separately? Can each code exist multiple times/employee no.?

I don't see the necessity for Running totals in such a simple sum.

I would do it this way.

//@Init (Suppressed in Group 1 header - Employee.ID)
WhilePrintingRecords;
if not inrepeatedGroupHeader then
(
NumberVar SalOther := 0;
NumberVar DIT := 0;
//if there are other codes to display separately add those as well here
NumberVar Total := 0;
);
"";

Now in the Detail section put the following formula

//@Calc
WhilePrintingRecords;
NumberVar SalOther ;
NumberVar DIT ;
b]//if there are other codes to display separately add those as well here [/b]
NumberVar Total ;

If {Salary.code} in [ "TIF","DIF" ] then
SalOther := SalOther + {Salary.Amount}
else if {Salary.code} = "DIT" then
DIT := DIT + {Salary.Amount}
//if other codes displayed then add these the same as for DIT and end the IF-Then block

Total := Total + {Salary.Amount};

In the Group 1 footer you simply create display formulas

eg.

//@DisplaySalaryOther
WhilePrintingRecords;
NumberVar SalOther ;
SalOther ;

//@DisplayTotal
WhilePrintingRecords;
NumberVar Total ;
Total ;

other display formulas are similar....don't really see the problem here??

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[lhendrickson]

Jim,

Thanks for your detailed response! I have never used this type of formula and can't wait to try it out on some of my other reports!

I have one related question:

One of my codes is a number 641. When I type
NumberVar 641:=0;
into my formula I get an error message "A variable name is expected here". I have tried putting the 641 in single and double quotes to force the system to view the 641 as text, but to no avail.

Any suggestions on how to get around this? And, by the way, how long have you been working with Crystal? (just curious)

Thanks,

Lhendrickson

 

[Ngolem]

the code istself is a number but this is a variable accumulating the code

so make the variable name C641

then Crystal will allow it as a variable name....you would still test for "641" though

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!