Summing an If then number formula, getting too large values???

[lwanalyst]

Can somebody give me a clue???
My crystal report is oversumming for no apparent reason. Kenhamady told me this neat trick about creating a formula called Null containing a Variable, but then deleting the variable so it contains nothing. Well,this works for my conditional If then statement using distinctcount on txt values, but not for an if then statement that I sum number values. Why??? Here are my formulas...

Step 1:{A>=7cert}
If {@LOS}>6 then {@LOS} else {@NumNull}

Formula 2{@Days}
sum({@>=7cert},{STDEXT.prov_ext_id})

Note:{@LOS}= {std.eff_end_dt}- {std.eff_start_dt}

 

[dgillz]

Change your formula to:

If {@LOS}>6 then {@LOS} else 0

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

http://www.trianglepartners.com

 

[lwanalyst]

Thanks, but I initially had that, but it didn't work, which is why I went to using the Null field.

 

[dgillz]

When you say it "didn't work" what happened? Did it return an error? Did it return unexpected results? Please clarify.



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

http://www.trianglepartners.com

 

[kenhamady]

The NULL formual technique is only for count, distinct count and average. In these operations a null is treated differently than a zero. This is not true of summing, since a summing the null or a zero will do the exact same thing. My guess is that you have a one to many relationships and are getting 'table inflation' duplicates that are getting into your totals.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[lwanalyst]

Yes, that is exactly what is happening to my report, table inflation due to my master table being joined to a detail table that has a field that has multiple values per record, so I will join the tables in SAS to produce 1 table to use in crystal, instead of doing the joining 2 in crystal.

Thanks for your help.

 

[kenhamady]

I would expect this to happen no matter where you make the link. It is the nature of linking one-to-many.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[lwanalyst]

But if I create a single table that does not include the field that has the multiple values in it then it seems to be working(at least it is in MS Access)

 

[kenhamady]

I must not be following you. Even in CR, if you don't use that table, the inflation won't happen. I don't think MS Access will link any differently than CR in this regard.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[lwanalyst]

I'm sorry, I referred to the linking when its the table that I meant. You're right, in crystal if I use a single table I won't get the inflation. That is the plan.

Thank you once again.