Counting outside of a parameter range

[rier]

I want to count those customers who do not fall within a parameter range.

I have set up a date parameter range and from that, obtain a count of customers who's details were entered during that period.

My problem is that I want to be able to:
1) count the customers entered upto the Min(?DateRange), and
2) count the customers entered after the Max(?DateRange)

Can anyone help?

Thanks

Richard

 

[Naith]

There's a few ways you could go about this, Richard. You could use running total functions, or create your own formulas. The following example is based on you doing the latter.

If you've set up a single ranged date parameter; then knock up a formula like:

whileprintingrecords;
numbervar beforerange;
numbervar inrange;
numbervar afterrange;

if {Customers.Detail Date} < {?Date1} then beforerange := beforerange + 1;
if {Customers.Detail Date} in {?Date1} then inrange := inrange + 1;
if {Customers.Detail Date} > {?Date1} then afterrange := afterrange + 1;

You'll need 3 independent formulas to display each total. e.g.:

whileprintingrecords;
numbervar inrange;

Remember to create one final formula which will reset your counters.

whileprintingrecords;
numbervar beforerange := 0;
numbervar inrange := 0;
numbervar afterrange := 0;

and place this as the last field in the group footer where the subtotals are displayed. Make sure the field is suppressed too.

 

[rier]

Naith

Thank you very much for your quick response but It doesn't appear to have resolved my problem.

Based upon what you have said, the 'InRange' formula works fine, the problem is that the 'BeforeRange' and 'AfterRange' remain at zero.

It seems that the date-range parameter has already eliminated the customer records outside that range. When the formula gets to evaluate the remaining records, there are none that are either before or after the parameter range.

I am sure I must be doing it wrong, but can you see where?

Thanks

Richard

 

[Naith]

Richard,

Sounds to me like you've included your date range in the Selection Criteria of the report.

If you have, then there's no way that the Before or After range counters are ever going to work because if the report had brought that data back in the first place, it would have disobeyed your selection criteria.

If you want to count dates before and after a date range, you can not have the range in the selection criteria .

If this means that you're going to be left with a report which takes ages to run because it's now looking at a shedload of data, perhaps consider taking a representative sample of data -/+ 4weeks or so on either side of the date range, if you have to have dates specified in the selection rules.

 

[rier]

Naith,

Unfortunately, a 'sample' representative isn't good enough in this instance, but thank you very much for the
support you have given me

Richard

 

[rosemaryl]

Dear Richard,

Can you write a Stored Procedure to collect the information?

If so you could pull the rows you need for the report but also add the count of customers before as a column and a count for customers after as a column. My experience is that this happens very quickly in the stored procedure and you would be only returning the rows you need to the report.

Also, have you considered subreports?

Hope this was helpful,

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.