My last post talked about Group Selection and showed off some quirks. For instance adding a Group Selection criteria might cut your report in half – but the record count stays the same. So I was curios to find out how the functions Previous() and Next() are affected by Group Selection.

First, what do these functions do? Normally, when you refer to a field in a formula the values for that field will come from the current record. These two functions allow you to refer to the record before or after the current record. So, if I wanted to know the number of days between one order and the next I could write the following formula:

{Orders.Order Date} - Previous ({Orders.Order Date})

This would tell me the number of days between the two dates. But what if you are using Group Selection. Do these functions use all the records from Record Selection, or just the ones that meet the Group Selection criteria?

Say I wanted to limit a report to the records that were above average. I could calculate the average order amount for the entire report then use that total in the group selection formula like this:

{Orders.Order Amount} > Average ({Orders.Order Amount})

Adding this rule would cause about half of the orders to be eliminated from the report. But as we mentioned above those records are still in memory. They need to be because they are used to determine the overall average. So what happens if you write a formula like the one above that refers to the previous order date? Will it see all records, like the record count does, or only ones that meet the Group Selection criteria?

My gut told me that this formula would include records that didn’t meet the Group Selection criteria, but I was surprised. The Previous() and Next() functions are apparently evaluated AFTER Group Selection. So the formula above will always pull values from the records that meet the Group Selection Criteria.