In the ideal world, customers write perfect specs up front, and requirements never change. In my work reports tend to evolve through several iterations. Some customers don’t realize what would work best until they start to see drafts.

Last week a customer presented me with what is usually a fairly simple request: print only the first 40 widgets. So I sort the records into the correct order (in this case date and item number) and then suppress all rows with a record number greater than 40.

Then the customer asks if the chosen 40 records can be sorted by location. Well they can, but changing the sort to location would change the chosen 40 records. So we go to plan B.

I created a formula field called {@Rank} that combines the Date and the Item Number into one string. The combined value needs to sort by date and then by Item Number so I used this:

Totext ({Date} , 'yyyyMMdd' ) & '-' & Totext ({ItemNumber} , '00000')

This converts the date and item number into a value that still sorts in the correct order. But I didn’t actually sort on this formula. Instead, I calculated the 40th value using the NthSmallest() function. It is like the Minimum function but allows you to specify not just the very smallest value (minimum) but any number, like the second smallest value in the column. You use the function like this to get the 40th value:

NthSmallest (40, {@Rank})

There is also an NthLargest() which is like Maximum(), and NthMostFrequent() which is like Mode(). If this were a formula field sitting on the report it would display the 40th value on the report, and it can do this without requiring an actual sort.  Just like the minimum can find the lowest value without requiring a sort.

So with this calculation I could sort the records by location and still limit the report to 40 records.  The limit is done with a group selection formula that only includes values less than or equal to that 40th value. The group selection formula would look like this:

{@Rank} <= NthSmallest (40, {@Rank})

The report works correctly.

But then another wrinkle appears. The number 40 needs to be a calculation instead of a literal number. Here is where I learned something new. In just about every place that Crystal requires a numeric argument (like 40) Crystal allows you to use a calculation instead. But the NthLargest, NthSmallest, and NthMostFrequent functions are exceptions to that rule. You can’t put a calculation, a formula field, a database field or a parameter as an argument in that function. You can’t even put in (1+1). It has to be a literal number.

So to meet the new requirement I had to completely scrap the NthSmallest() function and go to Plan C, using a subreport. I inserted the report I had created back into itself as a subreport. In the main report I sorted the records based on {@Rank}, and then wrote a formula that said:

WhilePrintingRecords;
StringVar RankN;
if RecordNumber = {@Adjusted 40 target}
then RankN := {@rank};
RankN

The formula called “adjusted 40 target” is the calculation that replaces the original fixed target number 40.  So the formula above ignores all records until it gets to that target number record and then it stores the value of {@rank} from that record into the variable RankN.  That will be our cutoff value and will get passed as a parameter into the subreport. The subreport then selects all {@Rank} values that are less than or equal to the parameter. So the subreport only returns the records I need. But the subreport can be sorted by location without affecting the chosen records.

Of course subreports are a last resort, but the customer gets exactly what they want (at least for now).