Skip to Content

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.

Untitled.png

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.

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Former Member

    Hi Jamie,

    I just want to use your formula for the Hierarchy of my report but i cant understand it fully because i am new to the crystal report. Can you please help me all you know about cross tab reporting?

    Thanks.

    Bernard

    (0) 
    1. Ludek Uher

      Hi Bernard

      Please post your query as a Discussion. Include some basic information. E.g.; exact version of CR (include any service packs), database used and database connection type. Document what exactly you are having issues with. Perhaps link to this blog in your Discussion.

      – Ludek

      Senior Support Engineer AGS Product Support, Global Support Center Canada

      Follow us on Twitter

      (0) 

Leave a Reply