Crystal Reports: Use a Summary on an Array

The summary functions in a formula are usually applied to calculate grand totals or a group subtotal. You can also use them on an array. 

Numbervar array mylist:=[4,5,6,3,2,5,6,7,8]; 
Sum(mylist); 

The array can be any variable type, but the summary must be valid for that type.

For example, you can Count or find the Maximum of a date array, but you can’t sum or average the array.

There is a comprehensive list of summary functions available. You can use Sum, Average, Count or any of the other summary functions to achieve your purpose. 

If you combine this feature with the “Split” function to break a long string up into smaller array elements, then you have a powerful analysis tool available. 

And you may need to summarize your summaries. Why?

Usually, summaries at higher level groups behave correctly and you get the numbers you expect. But some summary functions do not behave as you wish. 

DistinctCount is a major offender here, as it does a distinct count of the entire group--and not a sum of the lower level distinct counts. If the same value appears in different lower level totals, it is counted only once at the higher level.

What you really need to do is summarize the summary.

The solution to this problem is to use the Three Formula Technique, which uses a variable to accumulate the correct total. You use a reset formula in the higher level group header, a calculate formula in the lower level section, and a display formula in the group footer.

Another thing to consider when you're working with summaries is how you merge fields into a text object. The ability to do this has been around for many versions and can be used to design sophisticated mail merge reports. You can drag database fields, formulas, parameters and all the other objects from the field explorer into a text object.

Be careful if you drag a summary field from a different section. Summary fields calculate their summary at the group level at which they are placed. But if you drag the summary directly from one group section into a text object in a different section, then the level of the summary is retained from the original section.

This applies to Crystal Reports version 9, 10 and XI. Crystal Reports 8.5 and earlier will change the level of the summary when dragged from another section.

If you want the summary to be for the level you have the text object in, move it to that section first. Then within that section insert the summary inside the text object. There may be a reason why you deliberately want a summary in one section to appear in another section.

Normally you need a formula to do this. Using a text object could be a faster way to achieve the same result.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.