Hierarchical Groups

When a new version comes along, there is always an extensive list of improvements and enhancements. Some of these can be "must have" features, and become a major motivation for an upgrade. Others features can take a little longer to be appreciated.

Hierarchical grouping might seem to be one of those things with specialized application. The demo material shows examples of organization charts. We’ve recently found an application where hierarchical groups have been very useful.

In a General Ledger table, each record contains a GL code, description and balance. These accounts can be grouped into higher level summaries with a field that points to a parent account.

You might make similar use of this feature with bill of materials tables, where a product is made of a subassembly.

Hierarchical Groups and Levels

The Hierarchical group feature allows you to indent each group as you go down the hierarchy. We have seen requests for indented description fields, and the totals not to be indented. 

The only way of doing this is to keep track of the group levels using formulas. You also have to set the automatic indenting to zero, and control the indenting yourself.

In this example, you need formulas using variables and arrays. We are using a number array as the SupervisorID field in the hierarchical group is a number. A string field will require a string array.

Your array size depends on how deep you expect your hierarchy to go. Five works in this example, and the formula can be easily extended to 10 or 20 levels.

A reset formula in the Report Header section

WhilePrintingRecords; 
numbervar array levels:=[0,0,0,0,0]; 
levels[1]; 

A formula in the Group Header to calculate the group level we are in. 

WhilePrintingRecords; 
numbervar array levels; // array of hierarchy index
numbervar i; //counter for loop 
if isnull({Employee.Supervisor ID}) then 
levels[1] := {Employee.Employee ID}; 
if not isnull({Employee.Supervisor ID}) then 
for i:= 1 to count(levels)-1 do ( 
if {Employee.Supervisor ID} = levels[i] then levels[i + 1] := {Employee.Employee ID}); 
levels[1]; // don't need to do anything here - but helpful for testing 

You now have an array of where you are in the hierarchy, and you can use this for indenting selected columns, or conditionally suppress some levels of the hierarchy. If you use hierarchical groups then you will find this hint very useful. 

Defeating Flummoxing by Formulation

Do formulas make you feel flummoxed? No problem. Here's some good info that will help you defeat flummoxing by formulation.

The Formula Workshop makes formula development easier. In Crystal Reports 9.0, all formulas are managed in the Formula Workshop. This is where you create and edit your calculations--including Record and Group Selection, SQL expressions, and conditional formatting of sections and fields.

One major improvement in version 9 is the ability to process string fields that are longer than 254 characters. The new limit is 64,000 characters in length.

In a similar way, Memo fields can now be processed in formulas.

There are some new functions available in version 9.0 including ProperCase, and formatting numbers in Roman numerals. (So perhaps we should refer to Crystal Reports IX).

Another useful function is DrillDownGroup Level which we see adding a whole new level of improved presentation to Drill Down Reports. If you know what level of Drilldown is currently being displayed, then conditional formulas and formatting can be used to change the appearance of the report. 

Jason Dove recommends saving time with formula sharing.

Once a report library grows to a certain size there will always be common formulas that are needed again and again. Having ready access to them can save a vast amount of development time.

My book started life as a notepad document which was a list of 25-30 formulas I used a lot, or were so complicated I did not like the idea of rewriting them.

This is fine for a solo consultant traveling from company to company, but for large teams of permanent staff something more robust and expansive is required.

For those companies using a Business Objects reports server (Enterprise, Edge etc) formulas can be saved in the repository just like a report and be accessible by the whole team.

Even a solo developer can benefit from this approach as formulas can be added to a formula through this method far quicker than copy / pasting from a separate text file. There is also the added bonus to the business which gets to keep this knowledge once the report developer leaves.

I’ll end this article with a step by step instruction on how to setup and use this method of formula storage, but first I want to discuss some of the finer points of implementation.

One of the main strengths of this approach is the standardization of report calculations, but this increases the importance of the formulas being right! This may sound obvious, but a formula that is right for one report maybe not be for another.

For example: a formula that calculates working hours based on a five day working week and 08:00 to 16:00 hours is going to be wrong for reporting on a team which works a half day on a Saturday.

With this in mind, I strongly recommend that formulas are double checked, tested and checked again before they are saved to the repository. Ideally, other team members should have an active role in the testing.

In a team of mixed abilities, an extensive library of formulas can lower the required expertise for report writing and really boost productivity.

And, as the code is still visible, new report developers can use it as a study guide to expand their own knowledge.

Unfortunately, saving, or accessing a pre-saved formula requires the developer to log on to the Report Server and so uses up a license. For those companies with only five licenses, this could be a real problem, especially as the Crystal Reports software has to be shut down and reopened to free the license.

One final thing to be aware of, which struck me as odd, is that formulas saved as functions on the Report Server do not handle NULLS. This can make some things just impossible to save in this manner.

How to Setup Functions

Write and save a formula, preferably something useful!

Within the formula editor, select "Custom Function" from the New File dropdown.

Choose to "Use Extractor" and then select the previously written formula.

Crystal Reports will create a Function based on the formula and replace any database fields with variables to allow it’s re-use with other values.

Rename the computer created variable names to something meaningful. I advise using ctrl F and Find & Replace to ensure all references to the variable names are changed.

Upload to the Report Server, where the Function be saved in the Repository Custom Functions folder.

Note the consistent naming pattern to the Functions in the illustration above. This is important for later use and makes finding the correct Function a lot easier.

Using Existing Functions

To use an existing Function is simple:

Open a new formula and expand the Repository Custom Functions.

You may need to log on to the Report Server, then, highlight the Function you want.

Click the cog icon with the red arrow pointing to it.

Go back to the formula you just opened and notice that the Functions Panel now has an additional folder “Custom Functions” which contains the Function just created.

Just double click the Function you want.

Ok, that may sound a little convoluted when approached as an end to end process, but once a library of Functions builds up, accessing it as and when required becomes second nature and a genuine time saver.
 

Jason Dove is a top Business Intelligence consultant and author who has shared his expertise with some of the most respected businesses in the world.  Crystal Reports is his reporting software of choice and the subject of his book "Crystal Reports Formulas Explained" which is the most advanced book on the market.  Currently available with a free 70 page Crystal Reports XI tutorial: http://www.scry-business-intelligence.com/products/bookoffer.php

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.