When you add a filter to most reports you create a rule that applies to each record and determines if that record qualifies or not. Examples would be:

{table.state} = 'PA'  and  {table.Amount} > 50

This is called record selection and is stored in the Record Selection Formula. You might enter the rules using the Select Expert, but Crystal will convert those rules into the Record Selection Formula.

But sometimes you need to add criteria that applies to an entire group based on a group subtotal. For instance you might want to limit the report to states where the total amount for the state is over $5,000. This is called group selection and these rules are stored in the Group Selection Formula. The rule I just mentioned would look like this:

Sum ( {table.Amount} , {table.state}) > 5000

When the subtotal already exists on the report you can create the rule using the the select expert. You would choose the “Group” option instead of the “Record” option of the select expert. Crystal will add the rule to the Group Selection Formula.  Any type of summary operation can be used for Group Selection (Sum, Count, Average, Minimum, Maximum, etc).

Just keep in mind that using Group Selection comes adds a few quirks to your report.

1) Adding a group selection rule will shorten the report but the record count (shown in the status bar) won’t change. That is because the record count is determined by Record Selection before Crystal applies the Group Selection Formula.

2) Groups that don’t meet the Group Selection rules are eliminated from the report but will still show up in the group tree. The group tree is created before the Group Selection Formula is applied. So clicking some values in the group tree will not take you to that group.

3) Grand total summary fields will not be reduced by Group Selection. The Grand totals are created before Group Selection. This issue can be solved by re-creating your grand totals as Running Total Fields rather than as regular summary fields. Running Total Fields are evaluated AFTER Group Selection so they will be lower than the original grand totals.

I took another dip into the registry today. I had to fix the formula editor toolbars for a customer (see below).

Two sections would not dock and the field tree on the left could not be made visible. We tried the field tree icon and right-clicks to allow docking. Nothing worked. I tried using the Toolbar Reset check mark in View > Toolbars. That didn’t work either. He even tried uninstalling and reinstalling Crystal, which also didn’t work.

But I remembered from my last post that the toolbars have lots of registry keys which store their settings and positions and I figured these were corrupted. And since re-installing didn’t solve things that meant the bad registry keys weren’t being replaced by the install. So we did another uninstall and then we went into the registry. Most of the Crystal registry entries had survived the uninstall. I was tempted to delete just the node for the formula editor, but we decided to play it safe and delete the entire node named “Crystal Reports”. It is found in this path:

Computer\HKEY_CURRENT_USER\Software\SAP BusinessObjects\Suite XI 4.0\

We exported this node to a .reg file just in case we needed to restore it and then deleted it.  When we reinstalled Crystal Reports the formula editor went back to normal.

Today I did some more testing because I wasn’t sure if we needed to do the full uninstall.   I closed Crystal, then I went into the registry and exported the “Formula Editor” node. This is found under the “Crystal Reports” node mentioned above.  Once it was exported I deleted the “Formula Editor” node.  I then went into Crystal Reports and worked in the formula editor.  When I checked the registry the “Formula Editor” node had been recreated.  It wasn’t fully populated but it was there.  I went in and did a SQL expression and a custom function and this created more of the keys for that node.  In other words, it may not require a complete re-install to fix this part of the registry.  Crystal appears to create these keys, as needed, when they don’t exist. So if you have windows or toolbars that won’t behave, you can try this approach first.

In my last post I wrote about overriding the limit on List of Values for a dynamic parameter. That requires changing a registry key. In doing the research for that post I found an SAP web page that lists 140+ registry keys for Crystal Reports. Most of the keys have a short comment about their purpose and a link to a knowledge-base article.  Unfortunately, many of the articles no longer exist on the SAP web site.

Some of the keys are esoteric, and won’t be useful to most users. But I found a few that I thought were interesting.

For instance, I have written about something called the ‘batch interface’ for parameters. This is the little control panel that appears whenever you have more than 200 values in your parameters list. This control shows you the values in batches of 200. Apparently the number 200 is a registry value that you can change using the node and key:

…\Crystal Reports\Reportview\ – PromptingLOVBatchSize (200)

Then there are several nodes that remember where toolbars and formula editor panels were located the last time they were used. They should open in the same place the next time. But sometimes when you change screen resolution or switch from two monitors to one, these locations might be off the screen.  The following registry settings are sometimes helpful in getting them back.

…\Crystal Reports\Formula Workshop

Editor Position (10,10,10,10)

…\Crystal Reports\Formula Workshop\Formula\

Field Tree: Toolbar-Bar2
Function Tree: Toolbar-Bar3
Operator Tree: Toolbar-Bar4 – Docking Style (f000)

You can check out the complete list on the page above.  And if you do decide to experiment with your registry – make sure you create a backup of it first.

I have written before about dynamic parameters and the fact that they are limited by default to 1,000 values. This is more noticeable when the dynamic parameter is a cascade of several columns. The cascade is pulled from a query that assembles all the valid combinations of the fields in the cascade. It is the total query that is limited to 1,000 records by default, not each field in the query. If you want to raise that limit you have to go into the registry and add a key.

There are two branches in the registry where you can make this change. One branch (Current user) makes changes that only apply to a specific user. Another branch in the registry (Local Machine) makes the change apply to ALL users.

I was making this change recently for a customer and had some trouble with the Local Machine branch. The Current User branch worked fine. What I eventually realized is that the Local Machine branch node I was using was for 32-bit computers. I am so used to thinking of Crystal as a 32-bit product that I used the 32-bit location out of habit. But you have to use a different node when you are running Crystal Reports on a 64-bit computer.  In this registry change it doesn’t matter if  Crystal is 32-bit or 64-bit, if you are on a 64-bit computer.

I updated my original article to give the correct locations for both.