Is there a function in Crystal similar to the LAST function in Access? 1

[lwanalyst]

I have a crystal report that counts case numbers if the case was open longer than 7 days.
I do this in 2 simple formulas

The first is...
@admits
If LOS(length of stay)>=7 then 1 else o

Then...
@Admitcnt
Sum(@admits)

However my count returns a larger number than it should because I have a detail table linked, so for each case there may be several codes in the LOS_Code field of my detail table causing it to count the case as many times as there are codes in the LOS_Code field. In Access if I use the LAST function on the LOS_Code field it alleviates this problem. Is there such a function in Crystal 8? I can't use MAXIMUM because it applies to numbers and my LOS_Code field is text. Any ideas???

 

[shaunk]

I avoided running totals for a long time and found that once I got my head around them they deal with exactly the problem you described.

I am having trouble imagining how your rows are tumbling out but I am sure if you play around with the running total it will solve your problem

 

[krishna]

Hi

Try using DistinctCount instead of count.

Regards
Krishna Kumar

 

[lwanalyst]

Thanks. I tried distinct count, and it doesn't work either.

 

[synapsevampire]

Not sure why distinct didn't work, but you can resolve this by creating formulas and by having a group for the Case:

Report Header Formula:
whileprintingrecords;
Numbervar MyCount:=0

Group Header Formula:
whileprintingrecords;
numbervar MyCount;
If LOS(length of stay)>=7 then
MyCount := MyCount +1

Report Footer Formula:
whileprintingrecords;
numbervar MyCount;
MyCount

Supppress the display of the first 2 formulas.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[lwanalyst]

Will I still be able to group on the final formula?

 

[lwanalyst]

OKay, I'm trying it now.

 

[synapsevampire]

I just reread and saw no reference to a formula that was to be grouped on...

Please post example data and the required output.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[lwanalyst]

Sorry about that...

I have a crystal report that counts case numbers if the case was open longer than 7 days.
I do this in 2 simple formulas

The first is...
@admits
If LOS(length of stay)>=7 then 1 else o

Then...
@Admitcnt
Sum({@admits},{state}(group1 header))

I also sum again by product(group2 hdr) , and then again by hospital(group3 hdr)

I also have the same problem in another report where I am trying to count casenumbers where source code field = ER or EM.
I have the same formula concept as above...
@Er_admitcnt
If {source_code} in ["ER""EM"] then 1 else 0

@ER_Admits
sum({@Er_admitcnt},{state})
sum(

Is there a way to say...
Distinctcount({case_nums}) where source code in ["ER""EM"]

 

[kenhamady]

See faq149-243
formula #14 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

 

[synapsevampire]

The approach should still be fine, just group on the fields you normally would, and then you can create 3 formulas to resolve distinct counting/summing.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[lwanalyst]

Ken, your formula worked! You are a genius

Thanks for the help everyone.