 |
|||
 |
|||
 |
|||
|
|||||||||
|
Crystal Reports Tools: Improve Performance While Saving Time and Money |
|
Crystal Reports: Hierarchical SummariesHierarchical Groups were one of the less understood features in Crystal Reports 8.0, but it's gotten better and more useful over time.This feature enabled you to process data that had a hierarchical relationship in a database table. Potential use of this feature included organization charts, product bill of materials, and general ledger chart of accounts. But you still had to have your application calculate the totals for the hierarchy. Well, Crystal Reports 8.5 enables you to specify that a total is a Hierarchical summary. When you create the summary, an option is available on the form ("Sum across Hierarchy"). Select that, where you would like to use this feature. This will also appear as a new option when you select "Change Summary Operation" on an existing summary field. The release of Crystal Reports Xi included some excellent new features for Hierarchical Groups. The groups were now nested, and with dynamic horizontal position, and hierarchical summaries we had an excellent solution to financial and hierarchical costing reports. But there was one new limitation as well. Hierarchical summaries could no longer be used in a formula. In a way, this was a fix to the problem that if you used the hierarchical summaries in a Crystal Reports 8.5, 9, or 10 report, the value that you got was the regular summary. It was not the total for that level of the hierarchy. Ido Millet tells us at every conference that a report becomes useful when you include a ratio in it. You might want to take a report with one hierarchical summary and calculate a value from it. If you had two or more summaries, you often want to subtract or divide one from the other. Our solution to this problem uses an array for each field you want to summarize. Each element of the array accumulates values for each level. You need to reset the total for that level in the header section, and you can display the summary for that level in the group footer. A formula in the report header creates the array and sets all the elements to zero. You must have enough elements to support the maximum number of levels. You need an array for each field you are summarizing. |
|
The final line of this formula is needed because a formula can’t return an array. A zero is all you need.
We need to reset each array element in the group header. Place this formula in the Group Header section
In the details section you need to accumulate the field into every element in the array.
A Similar formula retrieves the value from the array in the Group Footer.
This final formula can then be used in other formulas to calculate those variances and ratios in the group footer. A problem with drilling down on the hierarchy was solved by setting the Report Option, “Show All Headers on Drill Down.” With this technique, those hierarchical data structures like your general ledger or a bill of materials become a lot simpler to design. |
|
Hierarchical Summaries in Formulas using DisplayStringWe’ve been a great fan of Hierarchical Groups and Summaries since they first appeared in Crystal Reports 8.0 and 8.5. Back in issue 54 (March 2006) we discussed hierarchical summaries and why it would be useful to use them in formulas. And unhappily why they didn’t provide the correct value when you did use them in a formula. The Crystal Reports XI and 2008 solution to this problem is to bar you from using the summary in a formula. But as we said back in March 2006, there are a lot of good reasons why you would want to use that hierarchical total in a calculation on your page. |
|
| You can use a method using a series of formulas to store the summaries inside an array variable. That works well, but is a little bit complicated. Sandro Bozzoli from Italy has found a simpler and better way to do this using the DisplayString conditional format. You need a section above the section you are using for your fields. |
Click to enlarge |
|
In this example, we have Group Header 1a with the hierarchical summary in that section. Format that summary suppressed, but with a display string of:
Then anywhere in the subsequent section (Group Header 1b) use the mySummary variable to pick up the hierarchical summary value from the previous section. This formula will double the summary:
The Group Header 1a section is formatted to "Suppress Blank Section" so it doesn’t take up any space on the page. We tried suppress or even display the string on the summary in the same section, but that approach didn’t work properly. One great benefit of this solution is that if you have several fields each with a hierarchical summary, you can just use a different variable in each to save the CurrentFieldValue, and you can then use them in formulas in the next section. Thanks, Sandro, for sending us an interesting hint.
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. |
|
