Select records based on a "whileprintingrecords" formula

[S3066]

Hi Group,

This problem has plagued me for some time: in a Crystal report, I have a "whileprintingrecords" formula that references summary information.

I'd like to display groups in the report based on whether this "whileprintingrecords" formula is at least a certain value.

Due to the inherent two-pass processing of Crystal, I cannot use record selection or group selection to specify my "whileprintingrecords" threshold.

My best guess is to use "whileprintingrecords" value criterion in the group's "Suppress" property, but that isn't ideal since it leaves a lot of white space.

Any ideas? Thanks for your time!

 

[synapsevampire]

Perhaps a Top N will satisfy this criteria.

If you supply sample data and expected output it will be easier to determine what you need and how to resolve.

-k

 

[paulosinuk]

I've hit this very issue myslef and had to opt for a suppression based exclusion as I couldn't find any method of getting this to evaluate during report generation.

I'm now doing the processing outside of the report using triggers in a stored procedure, not easy but far more efficient.

Paulos

 

[S3066]

Thanks to both people who replied!

An example is, say I have a &quot;whileprintingrecords&quot; formula that calcs the difference of something. I only want to display those records in the report where difference > <some constant threshold value>.

paulo - thanks for letting me know I'm not alone in this predicament. By the way, what exactly are &quot;triggers,&quot; if you don't mind my asking?

Synaps - I was thinking of using &quot;Top N&quot; also; however, I noticed that Top N processes during Crystal's &quot;pre-Pass 2,&quot; whereas whileprintingrecords processes during &quot;Pass 2.&quot; And, since I want to take certain values off of whileprintingrecords, I guess I can't use Top N.

 

[MoJoP]

Not sure if this would work but Ken Hamady pointed me in this direction when I was experiencing difficulties.

http://support.crystaldecisions.com/kbase/c2005121.asp

is the knowledgebase paper from Crystal.

MoJoP

 

[lbass]

Just a comment. I'm not sure this would work in your case, because you haven't shown the formula, but I've found that sometimes I can use a group select by reverting to writing out the content of the formula instead of using the formula itself. This also is sometimes useful when referencing formulas within formulas to avoid a message like &quot;must be evaluated later.&quot; For example, instead of using:

@maximum > 5 //where @maximum = Maximum({field.number},{group.field})

Use:
Maximum({field.number},{group.field}) > 5

-LB

 

[kenhamady]

Suppressing sections shouldn't leave white space - that will happen if you suppress the fields, not the section.

Could you post the actual formula that you are using?

Also, explain again why the Group Selection formula won't work. The GSF is designed to allow criteria that includes summary info. 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

 

[chelseatech]

There are a couple of potential solutions here, but it depends on your report design

Lbass almost has the correct solution, although there is one catch.. WhilePrintingRecords is calculated after Group Selection.

If your formula is just a collection of summaries, paste the entire formula into the Group Selection formula. That might be all you need. Just remember that ALL the summaries are calculated at the same time, so if you want an accurate Grand Total you need to use a running total, rather than a summary.

Otherwise section suppress is what you need. Use the Section Expert to suppress the sections if the formula is below a specific value.

The only catch with section suppress is what if you've got a subreport calculating your formula and using a Shared variable. CR9 has an option to suppress empty subreport, and that combined with Suppress empty section will remove some white space from your report. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[Ngolem]

I think your real problem....(unstated as yet)..... is that you only want the group detail information displayed.... IF the results of a calculation difference is in the correct range.

Otherwise you don't want to see any of the information.

This is a tricky problem but not too hard if the amount of Detail information (or header information for that matter) is reasonable.

My solution to this problem was to make the detail and header sections 100% suppressed. The difference would be calculated in the footer of course and if the result was outside the acceptable range, the footer would be conditionally suppressed.

The header/detail information is stored in variable arrays during the processing of the group. The footer (when the calculation is acceptable) would display the array information in a single or multiple group footer sections (depending on the amount of data and how it is to be displayed)

Jim Broadbent

 

[S3066]

Thanks for your input, everyone!

To respond:

MoJoP - sorry, the Crystal paper you specified didn't seem applicable to this issue. But I appreciate the info!

lbass - your idea to write out the summary formula <-- unfortunately, my summary formula contains too many &quot;sub-summaries,&quot; so writing the whole formula out would be very cumbersome. Your post, however, will help me in other reports, so thanks!

kenhamady - I wish I could post the selection formula I'm trying to use, but it references other formulas, which reference others, so I don't want to burden people with confusing info.

chelseatech & Ngolem - Yes, I've used the &quot;Section Expert&quot; to suppress the group footer conditionally based on a desired value range of a formula. Thanks for corroborating this solution.

I finally found, the reason for my extraneous white space was, I needed to suppress 100% the group header & detail. These sections were minimized but not 100% suppressed.

Thanks to everyone who responded!!