Group-specific subreport that only hits the database once

Thursday 12 January 2017 @ 10:44 pm

Group-specific subreports are always a last resort for me, because they hit the database multiple times and usually that slows things down. Sometimes, though, they are necessary.

I recently had to create a report with about 100 groups, and each group needed a subreport that ran a separate query returning a very large dataset. I was concerned about the time it would take to hit the database 100 extra times. I was trying to find a way to read the data just once for all the groups and yet still have each subreport provide group-specific data.

I knew that when you move an un-linked subreport from the report header to the group header it only refreshes in the first group and is simply repeated for every other group. If I could fool Crystal into thinking that my subreport was the same query for all groups, then it should only run once. So I changed the SQL in the subreport to return the data for all groups, and then added a parameter to select one group value at a time. I found that when I placed the parameter in the Record Selection Formula Crystal would refresh the subreport with every group. But if I put the same parameter in the “Saved Data” selection formula, which forces it to be evaluated locally, the subreport would not refresh for each group. I still get group-specific data for each group, but only one hit to the database.

Note – the “Saved-Data” record selection formula was introduced in Crystal Reports 2008 (v12).  If you are using Crystal Reports XI (v11) or an older version you will have to use an alternative method that is a bit more complicated.  For instance you could pass the group parameter to a conditional formula and use that to ensure that your totals are group specific.  Then use group selection to eliminate all the zero groups.  Not nearly as elegant but workable.  Call to schedule a session if you would like more details on this option.

Leave a Reply

Recrystallize Pro