First Occurence of a number 1

[pmk16]

I have a calculation (formula) in the details section, whose output looks like the following:
@count
--------------
0
0
1
2
2
1

What I would like to do is perform a calculation such that if there is a first occurence of '2' then perform a calculation, else if it is a second occurrence of '2' then perform another calculation.

How do I determine the first occurrence of a number which is displayed in the details?
I considered using grouping on this calculation, but Crystal isn't allowing me to perform grouping on a calculation (due to Multipass concept: since Grouping occurs in first pass and formula in second)

Thank you

 

[Madawc]

Try doing a running-total count for the value - assuming Running Total will allow it when Group does not. Then do a formula field that tests the running-total count for being 1 or something else.

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.

It helps to give your Crystal version.

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

 

[lbass]

What is the {@count} calculation? You could try:

whileprintingrecords;
numbervar cnt;

if {@count} = 2 then
cnt := cnt + 1;
if cnt = 1 then <first calculation> else
if cnt = 2 then <second calculation>

-LB

 

[pmk16]

Thank you LB.
It works

 

[pmk16]

What if I want to perform something on first occurence of each number:
@linenumber @calc @number @variable
-----------------------------------------
1 0 0 25
2 0 50
3 1 0 25
4 1 50
5 2 0 25
6 2 0
7 2 50
8 3 0 25
9 3 0

Could you please suggest a way to perform the following operation:

if first occurence of @number=0 then set a variable = 25
else set variable = <blank>
So, the o/p would be for line number 1,3,5,8; variable would hold 25

Thank you so much for your help.

 

[lbass]

Try:

whileprintingrecords;
numbervar x;
if {@calc} <> previous({@calc}) and
{@number} = 0 then
x := 25;

The default would be 0, but you could format the formula to "suppress if zero" in the customize section of the number formatting screen.

If this doesn't work, you need to provide the contents of your nested formulas.

-LB

 

[pmk16]

The problem all through out has been that {@calc} <> previous({@calc}) will give @calc does not have previous or next value.

 

[lbass]

As I said: "If this doesn't work, you need to provide the contents of your nested formulas."

-LB

 

[pmk16]

@number:
numberVar amntReached;
Select {table.deduction}
Case "$50" : if NextIsNull ({table.DED_AMT}) then
if {@Balance} = 50 then
amntReached:= 50
else amntReached:= 0
else if next({table.DED_AMT}) + {@Balance} >= 50 then
amntReached:= 50
else
amntReached:= 0
Case "$100" : if NextIsNull ({table.DED_AMT}) then
if {@Balance} = 100 then
amntReached:= 100
else amntReached:= 0
else if next({table.DED_AMT}) + {@Balance} >= 100 then
amntReached:= 100
else
amntReached:= 0
default: amntReached := 0;
amntReached;

where,
@Balance:
whileprintingrecords;
Select {table.deduction}
Case "$50" : {#YTD Amount} mod 50
Case "$100" : {#YTD Amount} mod 100
Default : 0;

@calc:
Select {table.deduction}
Case "$50" : {#YTD Amount} \ 50
Case "$100" : {#YTD Amount} \ 100
Default : 0;


So basically {#YTD Amount} calculated running total per employee

@calc generates numbers like 0,1,2,etc... (integer division) . if case = $50, then divide by 50, else divide by 100

@balance: displays the remainder when dividing by 50 or 100

and @number should display 25 if either the balance = 0
for $50 bond, or if current ded_amount+ previous balance >= 50.

Or, should display 50 if either the balance = 0 for $100 bond, or if current ded_amount+previous balance >= 100

 

[lbass]

Now I recognize this problem from another thread. Is this another approach to try to solve the same issue? Can you explain what it is you are trying to do in more descriptive terms? Maybe there is a better/different way to do this.

-LB

 

[pmk16]

LB, you are the best!

Well, there might be a different way to solve this issue. May be write some PLSQl or something, but I am not well versed with oracle.

Let me try explaining:A Company buys bonds for its employees.In order to do that, a deduction amount set by an employee(which has no defined logic, it can be $6 or $50) is taken out of every employees paycheck.

Now, an employee A chooses to give $10 out of every paycheck, then on the 5th paycheck,the employee would have given $50 to the company to purchase the bond. Since the actual price to buy a bond is $50, the company would buy the bond for the employee.

On the other hand, an employee B on the other hand chooses to give $35 of of every paycheck to buy a $50 bond. Which means, that in the second paycheck, company needs to purchase a $50 bond for the employee and the 'balance remaining' would be (abs($35+$35-$50) =$20 ) .

There are two possible bonds prices: $50 valued, $100 valued

So, the purpose of this report is during a paycheck, the company should buy a $50/$100 bond for which all employees.

What information I have in hand is:

Employee, Ded-amnt, payment-date,bond-type
--------------------------------------------

So, what I have tried out is determine year-to-date amount.
then, calculate '@balance remaining' as the mod of YTD/50 or 100, based on the type of bond.

And, I need to determine the 'amount reached'. The problem that I am experiencing with my current amount reached calculation is that the output will appear one row prior to the desired row and it makes sense why, if you see my formula:

if next({table.ded-amnt}) + {@Balance} >= 50 then
amntReached:= 50
else
amntReached:= 0

or, if there was some other logoc that I could use!

Thank you agaiN!

 

[lbass]

But I already helped you with dealing with this issue in your last post, and you said that you thought it worked. So if it didn't work the way you wanted, it would be better for you to continue with that post, noting what the remaining issue(s) are.

-LB