Quirks of Group Selection

Saturday 31 October 2020 @ 7:48 pm

When you add a filter to most reports you create a rule that applies to each record and determines if that record qualifies or not. Examples would be:

{table.state} = 'PA'  and  {table.Amount} > 50

This is called record selection and is stored in the Record Selection Formula. You might enter the rules using the Select Expert, but Crystal will convert those rules into the Record Selection Formula.

But sometimes you need to add criteria that applies to an entire group based on a group subtotal. For instance you might want to limit the report to states where the total amount for the state is over $5,000. This is called group selection and these rules are stored in the Group Selection Formula. The rule I just mentioned would look like this:

Sum ( {table.Amount} , {table.state}) > 5000

When the subtotal already exists on the report you can create the rule using the the select expert. You would choose the “Group” option instead of the “Record” option of the select expert. Crystal will add the rule to the Group Selection Formula.  Any type of summary operation can be used for Group Selection (Sum, Count, Average, Minimum, Maximum, etc).

Just keep in mind that using Group Selection comes adds a few quirks to your report.

1) Adding a group selection rule will shorten the report but the record count (shown in the status bar) won’t change. That is because the record count is determined by Record Selection before Crystal applies the Group Selection Formula.

2) Groups that don’t meet the Group Selection rules are eliminated from the report but will still show up in the group tree. The group tree is created before the Group Selection Formula is applied. So clicking some values in the group tree will not take you to that group.

3) Grand total summary fields will not be reduced by Group Selection. The Grand totals are created before Group Selection. This issue can be solved by re-creating your grand totals as Running Total Fields rather than as regular summary fields. Running Total Fields are evaluated AFTER Group Selection so they will be lower than the original grand totals.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)






One Response to 'Quirks of Group Selection'

  1. MHurwood - November 4th, 2020 at 2:16 am

    The record count *can* change using Group Selection *if* you have the “perform grouping on server” box ticked and if you’re hiding the details through a drill-down or similar. There’s even a bit of Heisenberg’s Uncertainty Principle going on in that in a test report of many thousands of records I had the Crystal Reports Studio say it had only processed 75 records to show the entire report, but if I added the “Record Number” special field to the footer, suddenly it was back to saying 5000+ records.

    Just further proof that Crystal Reports can be a bit weird sometimes, especially when it comes to relying on all the multiple-passes it does to produce the final output!

    I’ll also give my tip for getting rid of all the entries in the Group Tree, etc. You need to somehow exclude all the records in the groups you don’t want from the initial query. You can do this by using SQL Expression Fields. Create an expression like:

    (SELECT SUM(“T2″.”Amount”) FROM “table” T2 WHERE “T2″.”state”=”table”.”state”)

    (This is summing the amounts of all records matching the state of the current record, through the use of an aliased second reference to “table”, called T2.)

    You now how a field for every record that gives the total for its group, so in the Record Selection formula you can have:

    {%StateTotal} > 5000

    and this will cause all the records you don’t want to be dropped at the very earliest opportunity, so won’t appear in the Group Tree, or any Grand Totals, or any Record Counts.


Leave a Reply

Jeff-Net
Recrystallize Pro

Crystal Reports Server