Using Hierarchical Grouping in Cross-tabs and Charts
If you have ever created a Crystal Report using the Hierarchical Grouping Options then you may have also wanted to create a cross-tab or chart using the same grouping order and hierarchy.
In later versions of Crystal, a HierarchyLevel function was added so that you could find the node of a particular group when using the Hierarchical Grouping Options. This function could be used for formatting objects (indenting, highlighting, etc.) but formulae referencing this function could not be used in charts or cross-tabs or group summaries as it was a print time function.
Recently one of our community members was trying to apply the same group order from his hierarchical data into a cross-tab and also apply some formatting to the cross-tab based on the group nodes.
Although there is no way to do this directly in a cross-tab or chart, there is a workaround. If a report uses a hierarchy, all of the information from the hierarchy (group levels, hierarchy nodes, parent groups) can be rolled up in a string running total and passed to a subreport. The subreport would be based on the same data as the main report and formulae can parse through this data and then be used in subreport objects such as cross-tabs, charts, group summaries, etc.
This may seem like a lot of work but my hope is that the attached sample report will help you out considerably. The sample report contains a bunch of formulae that do most of the work for you. The real work that you have to do is some copying and pasting and also editing some fields so that they use your data instead of the sample report’s data. There is one main formula in the sample that takes your group and parent values and then rolls up all of the hierarchical information and this is passed to the subreport via a one-way subreport link. In the subreport, several more formulae look after parsing this data out to make it usable in your subreport objects. A lot of the formulae that are in the sample report do not have to be edited, so that makes things even easier.
There are instructions on the sample report’s Report Header that will guide you through reusing the formulae and this technique on your report. To use the report, download the HiearchyFormula file attached to this blog and change the file extension from .txt to .rpt. If you get an error opening the file it is most likely because you are using an earlier version of Crystal Reports that does not support the HiearchyLevel function.
I hope that you find this tecnhique helpful. If you are looking for any other solutions or workarounds for Crystal Reports, please see my blog here in particular the content section.