Running Total - sum nominated value fitting other criteria 1

[IceRuby]

Crystal version 10, ODBC connection to SQL database

I am trying report all REG and REGADJ transactions BUT these only limited to those that also have RBFER2 and RBFER3 transactions. Having issues in achieving correct results.

Reporting from a transaction table that interchanges the “Amount” field to fit the Tran Code or Allow/Deduc code. Individual transactions are listed per employee and if Tran Code relevant only list once per employee per pay period.

e.g
Emp no Allow/Ded Tran Code Amount
000034 RBFER2 - $ 40.00
000034 RBFER3 - $ 140.00
000034 REG $2500.00
000034 REGADJ $ -190.00

I have tried a configuring via a running total off "amount field" with the following formula:-

if {allow_dedn_code}="RBFER2" or {allow_dedn_code}= "RBFER3"
then {time_code} = "REG"

reset on every employee. No amounts are returned.

Example Report required:-
Emp no Allow/Ded Tran Code Amount RunningTotal
000034 RBFER2 - $ 40.00
000034 RBFER3 - $140.00
000034 - REG $2500.00 $2500.00
000034 - REGADJ $-190.00 $-190.00
000045 SGC $ 700.00
000045 - REG $ 300.00
000099 - REG $1700.00

Appreciate our help

 

[dgillz]

What field are you summing in your running total? And what is {time_code}? A database field? What is the purpose of that formula?

If you are trying to evaluate based on a formula, the formula should be:

{allow_dedn_code}="RBFER2" or {allow_dedn_code}= "RBFER3"

If either of the above evaluates to true, then the running total should work, provided that you have the proper field picked to sum inyour running total field.


Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[IceRuby]

The table breaks transactions in two types
1. Allow/Deduction (Code ID for any allowance or deduction)
2. Time Code (Code ID for standard type of payment)(yes a database field)

What I need the formula to evaluate is for any Allow/Ded code = RBFER2 or RBFER3 return not their values but the Time Code (or Tran code as I listed above) value when it equal to REG or REGADJ.

I do not need the actual RBFER values.

I cannot just list all Time Code = REG or REGADJ as I need to restrict these to Employee's with Allow/Ded codes = RBFER2 and RBFER3.

e.g

Emp no Allow/Ded Time Code Amount RunningTotal
000034 RBFER2 - $ 40.00
000034 RBFER3 - $140.00
000034 - REG $2500.00 $2500.00
000034 - REGADJ $-190.00 $-190.00
000045 SGC $ 700.00
000045 - REG $ 300.00

Sorry hope this explains issue more clearly

 

[lbass]

You could insert a group on {table.employeeno} and then create two formulas:

//{@allowded}:
if {table.allow_dedn_code} in ["RBFER2","RBFER3"] then 1

//{@timecode}:
if {table.time_code} in ["REG","REGADJ"] then 1

Then go to report->selection formula->GROUP and enter:
sum({@allowded},{table.employeeno}) > 0 and
sum({@timecode},{table.employeeno}) > 0

This will only those employees that meet your criteria. Then you will need to use a running total (sum of {table.amt}, evaluate using a formula:

{table.time_code} in ["REG","REGADJ"]

Reset never (or on change of employee group if you are evaluating at the employee level.

-LB