Cloaking Groups

Tuesday 24 July 2018 @ 7:49 pm

There are times when you want a group level to be optional. Maybe you want to give the user a choice between having 1 group level or 2 group levels in a report. While it is easy to suppress Group Headers and Group Footers for group 2, this doesn’t make the group level go away. Group 2 will still put the records in order, and will take priority over any sorting you have specified. A group can’t be removed based on a parameter, but you can have the same effect by ‘cloaking’ the group. Even though it is still there, it has no effect on the report.

To cloak a group you first create a parameter with 2 choices. For example, our user has a parameter called {?Group choice} that allows them to select the number of group levels:

Group by State only
Group by State and City

You set Group 1 to use the field “state”. Group 2 is set to use a formula that says:

If {?Group choice} = “Group by State only”
then “All”
else {table.City}

So, if the user picks “Group by State and City”, then the second group will be the field “city”. But, if the user picks “Group by State only”, then Group 2 will be one big group. Since all the records in Group 2 have the same value, the details will sort as if there were only one group.

So what do you do with the Group 2 Header and Footer? You suppress these sections with a suppress condition that says:

{?Group choice} = “Group by State only”

Now the Group 2 has no effect on the sort, and it is invisible.

A more complex example is when the user wants to see the Top 10 customers but wants to choose if the Top 10 is based on the Average or the Sum of the amount. There is no option for this in the Group Sort Expert. The workaround is to create a parameter so the user can choose either “Sum” or “Average”. Then create two cloaking formulas like the one above, using the Customer field and opposite IF conditions. Group on both fields and set the Group Sort for one group to the average and the other to the sum. Add suppression logic to the group headers and footers to completely cloak one group or the other.

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






Comments are closed.

Jeff-Net
Recrystallize Pro

Crystal Reports Server