Limit Group Selection to % Total 1

[shelby55]

Hi

I am using CR9 on Windows XP/SP2 computer accessing an Access XP database for hospital data.

I have a report where I'm looking at top 10 and bottom 10 in terms of length of stay over target. I want to limit to those physicians who represent 1% or higher of total cases.

My %TtlCases formula is:
Sum({ttlCases, Doctor})*100/Sum({TtlCases})

The formula works fine but when selecting as Group Selection i.e. %TtlCases >=1 I get the message "this formula can't be used becuase it must be evaluated later". This makes sense of course because the grand total part of the calculation won't be known until report is complete.

So, is it possible to do this? Any and all assistance greatly appreciated!

Shelby

 

[Madawc]

Sections can be suppressed using a formula. But you'r probably have just the same problem with 'evaluated later'.

The only way I can see is to get the grand total in advance. Put a subreport in the report header to find it from the same records. Pass it back using a Shared Variable.

Make a formula field in the subreport footer like
whileprintingrecords;
shared numvar TotCases;
TotCases:=Sum({TtlCases})

To access it in the main report, create another formula field with
whileprintingrecords;
shared numvar TotCases;
TotCases
WasSaved

This should then work for calculations.

Madawc Williams (East Anglia, UK)

 

[lbass]

You should be able to use your formula in the group selection area--unless "ttlcases" is already some kind of summary. Please provide the contents of "ttlcases".

-LB

 

[shelby55]

Hey lbass

You are wonderful and keep coming to my rescue!! Your comments made me realize that I was trying to summarize a formula that is a summary. So I changed the group selection critieria using the report fields themselves:

Count ({Data.ChartNo}, {@DataElement})*100/Count({Data.ChartNo}) >= 1

and it worked!! A big star for you - thanks!

Shelby

 

[shelby55]

Hi again

Sorry Madawc, I should have acknowledged your assistance while applauding lbass. Unfortunately, I failed to mention in my original post that the data was already in a subreport so your method wouldn't have worked (and also because ttlcases was a summary formula which was really the problem in the first place).

Thanks for trying to help, though, and I hope my unintended slight doesn't discourage you from replying to my queries in future!

Shelby