Selecting Groups based on Percentages Generates an Error.

[jaybar48]

I have a Crystal report that looks at patients w/o insurance data indicated. For each type of treatment project within a facility, the report tabulates the number of patients where there is no insurance information indicated, the Total N and the percentage of patients w/o insurance information for a given program within the facility. For the number of Patients w/o insurance information I am using the running total field expert and choosing the disting count summary function for the Client ID. Similarly, I am using the same exper to derive the N (COUNT). I am using a formula field to computer the percentage. So far so good.

Now, I want to include in the report only those facilities and treatment programs within a facility where the % is > 15%. No matter what I do in the group selection formtla I get the following error message :

This formula cannot be used. It must be evaluated later.

Is there a work-around? Can anybody help.


Thanks,


Jay

 

[kenhamady]

Is there a reason why you are using running totals instead of regular totals. The running totals make your formula ineligible for use in group selection, and you can probably do a these summaries using Insert-Summary. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[jaybar48]

Thanks Ken:

I was using running totals, because the dialog box expert allowed you to specify a formula condition under which the recored was evaluated. I want to only evaluate the record when isnull(x) is true and reset the count for each change in group. How would I do that using regular totals and not running totals?


Thanks,


Jay

 

[kenhamady]

Post the exact "evaluate" condition formula that you are currently using and I will show you. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[jaybar48]

Ken:

The evaluation condition is:
isnull({instat.effect_dt})=true

Counter to be reset for each change in group 2 field


Thanks, Jay

 

[dgillz]

Create a formula field:

If Isnull({instat.effect_dt}) then {Amount} else 0

Then sum this formula for your groups, and it should be available for further manipulation in other formulas as you desire. Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com

 

[kenhamady]

To get N (all client count) you can just select the ClientID and use Insert Summary to create a distinct count for group #2.

For the distinct count you have to create a formula that is:
if isnull({instat.effect_dt})
then {ClientID}
else {@nullformula}

Then use Insert Summary again to do a distinct count of this formula for Group #2. You have to do an ELSE that is a third special formula that returns a null value. Creating this formula is described in the following link:

http://www.kenhamady.com/form14.html

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[jaybar48]

Thanks Ken:

Method you suggest won't work! HELP!

If I use Insert->Summary as per your suggestion. then there is no way to create a percentage, because the summary fields are not visable in the field list. By percentage I mean the distinct count/N.

If I compute the distinct count and N using formula fields instead of Insert->summary, I can create a formula field for the %, but then I cant selct groups based on that percent


HELP



Jay

 

[kenhamady]

The summary fields should be visible in the formula editor below the running totals. They have a greek Sigma ( funny looking E ) next to them. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[jaybar48]

Thanks Ken:

We are getting there, but still have one major hurdle!

I see the summary fields and am able to locate them. However, we need one more step in order for this report to work.

I created a summary field and a formula field to get the distinct count of the clientID and that part works fine. I created a summary field to get th total N as you suggested in your precious post and THAT works fine. However, I can't see any way to create a summary field for the percentage I need. Basically I need a way of computing a percentage which is the distinct count/N. If I use a formula field, I get the same error message that I listen in my first post on this. Is ther any way around this?

Thanks,

Jay

 

[kenhamady]

You shouldn't. You can divide one sigma by another.

What formula are you trying that gives you the error? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[jaybar48]

Thanks Ken:

That worked. Great.


BTW, do you know of any Training in NYC that teaches SQL and how to integrate it into Crystal Reports.


Thanks Again for everything.


Jay

 

[kenhamady]

Not aware of any formal classes like that - but now that v9 is out and allows full access to SQL, you may find more Crystal classes covering SQL. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com