IsNull Question 2

[Hashiba14]

I have been trying to debug this code and have finally give up. Please see if you can point me in the right direction.
I'm using Crystal 10.

I have a field in my report called emp_pre_cont and this field usually has a number most of the time, but there are occasions when there are no values whatsoever. If there is a number value in this field, I have a formula that will calculate a retirement bonus, but if it doesn't have a value, I would like it to say 0.00.

Here are my 3 formulas:
1. Value Check:
if isnull({BENEFIT.EMP_PRE_CONT}) then '0.00'
else ({@Retirement Pension})

2. Retirement Pension:
(({EMPLOYEE.PAY_RATE}*{EMPLOYEE.NBR_FTE}*{EMPLOYEE.ANNUAL_HOURS})*.0575)

3. 403b Sum Calculation:
whileprintingrecords;
shared numbervar CompPensionTotal:=sum ({@Retirement Pension });

My problem is that when there is a value, I can pull it alright, but when its 0.00, nothing appears.
Suggestions...

Thanks

 

[lbass]

Your first formula should fail, since the potential results are of two different datatypes. It should be:

if isnull({BENEFIT.EMP_PRE_CONT}) then 0
else {@Retirement Pension}

Make sure you don't have the field formatted to "suppress if zero" in format field->number->customize.

-LB

 

[Hashiba14]

Ok, now I'm getting 0 for the values that don't have any values and if there are values, they are also being shown. But if I want to take it a step further and total everything out, like in the formula:

3. 403b Sum Calculation:
whileprintingrecords;
shared numbervar CompPensionTotal:=sum ({@Retirement Pension });


Do I substitute Retirement Pension with Value Check? Because when I do do that, the 0's disappear again. Thanks for all the help again...

 

[lbass]

If you use {@value check} in the variable, the sum will exclude the zero amounts. Is that what you want?

-LB

 

[Hashiba14]

No, I would like the zero amounts to be included.

 

[lbass]

Please explain what you mean. Adding zero amounts results in the same sum. Maybe you should show some sample data and spend some time explaining what you are trying to do.

-LB

 

[dgillz]

Generally, you do not need to write a formula to do a simple sum on a field. Just right click the field {@valuecheck}, select insert, Grand Total.

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

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[Hashiba14]

This is a subreport that I'm using to populate a value back into the main report.

Ex. If Account# 33110 has a value of 5.75 in field ({BENEFIT.EMP_PRE_CONT}), then it knows to use the Retirement Pension:
(({EMPLOYEE.PAY_RATE}*{EMPLOYEE.NBR_FTE}*{EMPLOYEE.ANNUAL_HOURS})*.0575)

But if the value shows as null or 0,the account does not qualify for a retirement pension and I want the subreport to display 0.00, where the value would be. I want the 0 to show, because when this value is referenced back into the main report, instead of putting a 0 there, it just puts a blank line. This is the formula that is being used to reference it back to the main report:

whileprintingrecords;
shared numbervar CompPensionTotal:=sum ({@Retirement Pension });

Example of blank lines:

Ex. Account 12456 Account 31130

Benefit 85.00 Benefit 85.00
Retirement 2500.00
Total 2585.00 Life Insurance 210.00
Total 295.00

Although this formula does help me in populating the amounts
({BENEFIT.EMP_PRE_CONT}) then 0
else {@Retirement Pension}

I need a way to populate the fields that don't have a value with 0.00, so I don't have any holes in the main report.

Please help me. Thanks

 

[lbass]

You left very important information--that this is coming from a subreport! Please explain the location of the subreport--which section in the main report contains the sub? Are you displaying the sub results directly or are you referencing the shared variable in the main report in a separate formula? In the example that you showed, which report section are we looking at?

-LB

 

[Hashiba14]

Everything that I have been explaining has been in the subreport for the retirement pension. I also have 2 other subreports in this report as well.

The subreport lies in Group Footer 3b: Benefit.Employee.
In the subreport, the formula that passes the information back to the main report is:

Value = if isnull({BENEFIT.EMP_PRE_CONT}) then 0
else {@Retirement Pension }

Retirement Pension =
(({EMPLOYEE.PAY_RATE}*{EMPLOYEE.NBR_FTE}*{EMPLOYEE.ANNUAL_HOURS})*.0575)

403 B SUM CALC:
shared numbervar CompPensionTotal:=sum ({@Value});

The information from the subreports are being referenced by a shared variable. Here is the formula:

whileprintingrecords;
shared numbervar Comptotal;
shared numbervar CompPTOTotal;
shared numbervar CompPensionTotal;

Comptotal + CompPTOTotal + CompPensionTotal + Sum ({BENEFIT.COMP_CONT}, {BENEFIT.EMPLOYEE})+{@Annual Salary}

The example I showed in my last post was from the main report.

I'm sorry I left out that bit of information, hope this helps you to help me..

thanks again...

 

[lbass]

Try setting up a reset formula in the main report, to be placed in GF#3a:

whileprintingrecords;
shared numbervar Comptotal := 0;
shared numbervar CompPTOTotal := 0;
shared numbervar CompPensionTotal := 0;

See if that allows the zeros to appear in your later formula.

-LB

 

[Hashiba14]

What do you mean by reset formula?

 

[lbass]

Create this formula which sets the shared variables to zero if the subreport is null. Place it in the GF#3a section.

-LB

 

[Hashiba14]

Unfortunately the blank lines are still showing up..

Anything else I can try?

 

[lbass]

So the blank "line" is a blank GF#3 section?

Can either of the following be null:

Sum ({BENEFIT.COMP_CONT}, {BENEFIT.EMPLOYEE})
{@Annual Salary}

Also, please show the contents of {@Annual Salary}.

-LB

 

[Hashiba14]

Yes, the blank line only appears when an account does not have a value for the retirement pension field. Otherwise it populates with the actual amounts.

Benefit.Comp_Cont can also be null as well.
Employee and Annual Salary can not be null.

Annual Salary:
{EMPLOYEE.PAY_RATE}*{EMPLOYEE.NBR_FTE}*{EMPLOYEE.ANNUAL_HOURS}

 

[lbass]

Try changing {@value} to:

if isnull({BENEFIT.EMP_PRE_CONT}) or
isnull({@Retirement Pension} then 0
else {@Retirement Pension}

-LB

 

[Hashiba14]

Unfortunately, I'm still getting the blanks.
I also tried changing :

if isnull({BENEFIT.EMP_PRE_CONT}) or
isnull({@Retirement Pension} then 0
else {@Retirement Pension}

to:

if isnull({BENEFIT.COMP_CONT}) or
isnull({@Retirement Pension} then 0
else {@Retirement Pension}


Anything else we can try?

 

[lbass]

Can you explain how you got this display?

Ex. Account 12456 Account 31130

Benefit 85.00 Benefit 85.00
Retirement 2500.00
Total 2585.00 Life Insurance 210.00
Total 295.00

What are we seeing here? Is each account a separate instance of a subreport? If so, are benefit and life insurance appearing in separate group sections within the subreport? Or?

Or are we seeing individual shared variables for each row or what?

-LB

 

[Hashiba14]

Yes, each account has a separate instance on the subreport.
Each account could have the same value, but more than likely not.

No, the benefits are being pulled under the detail of the main report. Any additional info is being pulled by subreports.

GF3b = Pension (Subreport)sharednumbmer var CompPenionTotal
GF3c = Taxes (Subreport) sharednumber var CompTotal
GF3d = Paid Time off (Subreport) sharednumber var CompPTOTotal
GF3e = Annual Salary (Calculation)

Yes, each subreport has a sharednumber variable that passes it back to the main report.