The Crystal Reports Underground News - Volume 2020.11
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for November 2020:
** Crystal Reports formula function libraries (2020)
** Running totals vs regular summary fields
** My library of Crystal Reports materials
** 140+ registry keys for Crystal Reports
** 1000 value limit on dynamic parameters
** Let me create your Crystal Reports
** Quirks of Group Selection
** One more note about Group Selection
** Individual Training with an expert.
** Using a custom group name
** When a reinstall won’t fix the formula editor
Gems from the Archives
Placing a group of fields onto the report in a column (2010.05)
Setting a Crystal Report to Any Datasource (2010.05)
Crystal Reports formula function libraries (2020)
It is time for my annual comparison of formula function libraries. If you aren’t familiar with User Function Libraries (or UFLs) they are DLL files that add new formula functions to your Crystal Reports formula editor. With these functions your formulas can do some pretty amazing things like:
1) Carry values from today’s report to tomorrow’s report
2) Carry values from one report to another.
3) Append lines of text to an external text file.
4) Automatically copy a value to the clipboard.
5) Check the user name of the user running the report.
6) See if a file or folder exists (on your network or on the internet).
7) Rename/copy/delete a file on your hard drive or network drive.
8) Launch an application or run a batch file.
9) Execute a SQL statement (Select/Insert/Delete).
10) Send an Email using information in the report.
11) Create a table of contents or an index for your report.
12) Generate bar codes without having to install any fonts
If this sounds interesting you can read my complete comparison including a list of all the functions provided by each DLL. The five UFL providers are:
Bjarke Viksoe (U2lwin32)
Maginus Software (CRUFLMAG)
Millet Software (CUT Light)
Chelsea Tech (File Mgt, Text, Share and others)
CrystalKiwi (Export, Table of Contents)
The only product that has changed since last year is CUT Light, which added some enhancements to existing capabilities.
If you need help deploying one of these functions in a project let me know.
Running totals vs regular summary fields
There are several ways to create totals in Crystal. This week I solved problems for two different customers by changing the type of total they were using. I will give a short explanation here, but if you want to really understand this topic you should download the Expert’s Guide to Totals in Crystal Reports from my website ($12). It comes with example reports and exercises.
The primary method for creating totals in Crystal is to add summary fields. In the menu use Insert > Summary or find the Sigma symbol on the tool bar. Some users default to using running totals because they see them listed in the Field Explorer. But summary fields have several advantages over running totals. Summary fields can be:
1) placed in the Group Header as well as the Group Footer
2) used in the Group Selection formula as a group level filter
3) used in Group Sorting to rank the groups in order based on a subtotal value
4) Copied to other group/report sections to create additional summaries
You can’t do these with running totals, so my first choice for creating any total is to use a summary field.
But there are specific situations when a running total will solve a problem that you can’t solve with a regular summary field:
1) If you want to watch the value change, row by row, like the balance in a checkbook
2) If the column you are totaling has duplicates and you need to skip the duplicates
3) If you use Group Selection and then want a total of just the groups that meet the criteria
4) If you do a TopN without others and need a total that doesn’t include the others
Some users also use running totals because you can apply a condition directly to the total. This way the total only includes records that meet a specific criteria. But if you write an If-Then formula with your condition you can use a regular summary field and get the same result. This gives you the conditional total without giving up all the advantages of summary fields.
And, if you get really stuck on a total issue, you can call me for a short consult.
My online library of Crystal Reports materials
Do you want to really understand Crystal formulas? Do you know when to use the four different methods to add totals? Why not let me explain these Crystal Topics to you with one of my Expert's Guides. Each guide comes with clear explanations and sample files to illustrate the concepts.
Expert's Guide to Formulas ($18)
Expert's Guide to Subreports, Parameters and Alerts ($14)
Expert's Guide to SQL Expressions, Options and Commands ($13)
Expert's Guide to Totals ($12)
Expert's Guide to Cross-Tabs ($11)
Expert Techniques Vol. 1 - 4 ($10 each)
Quick Reference to Crystal Reports in Visual Basic ($8)
Quick Reference to Crystal Reports in .NET ($7)
You will find these on the "Library" page of my site.
140+ registry keys for Crystal Reports
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.
1000 value limit on dynamic parameters
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.
Let me create your Crystal Reports
There aren't many people who know Crystal Reports better than I do. It is what I do all day, every day. So if you need a tough report created why not leave it to an expert? Let me show you how I can mix and match techniques to create the reports you need - even the ones that "can't be done". And since I am also a teacher I am happy to explain to you how the techniques work together.
I can also review existing reports that break, or run slowly, or seem overly complex. Let me have a look at them and see if there is a more elegant solution.
Quirks of Group Selection
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.
One more note about Group Selection
The article above talked about Group Selection and showed some quirks. For instance adding a Group Selection criteria might cut your report in half – but the record count stays the same. So I was curios to find out how the functions Previous() and Next() are affected by Group Selection.
First, what do these functions do? Normally, when you refer to a field in a formula the values for that field will come from the current record. These two functions allow you to refer to the record before or after the current record. So, if I wanted to know the number of days between one order and the next I could write the following formula:
{Orders.Order Date} - Previous ({Orders.Order Date})
This would tell me the number of days between the two dates. But what if you are using Group Selection. Do these functions use all the records from Record Selection, or just the ones that meet the Group Selection criteria?
Say I wanted to limit a report to the records that were above average. I could calculate the average order amount for the entire report then use that total in the group selection formula like this:
{Orders.Order Amount} > Average ({Orders.Order Amount})
Adding this rule would cause about half of the orders to be eliminated from the report. But as we mentioned above those records are still in memory. They need to be because they are used to determine the overall average. So what happens if you write a formula like the one above that refers to the previous order date? Will it see all records, like the record count does, or only ones that meet the Group Selection criteria?
My gut told me that this formula would include records that didn’t meet the Group Selection criteria, but I was surprised. The Previous() and Next() functions are apparently evaluated AFTER Group Selection. So the formula above will always pull values from the records that meet the Group Selection Criteria.
Individual Training with an expert
Do you have gaps in your Crystal Reports knowledge that are slowing you down? The most effective way to fill in those gaps is to have individual training sessions with an expert. I have done Crystal Reports full-time for over 20 years.
This type of training is ideal for people who:
Need to cover only a few topics.
Want to use their own data and reports as examples.
Want to learn from someone who has taught over 2500 satisfied students.
This is one-on-one, hands-on training - not a webinar. Start with a purchase of only 2 hours and get my course material with exercises for free. Do as much of the work as you want on your own, then use your prepaid time to work with me by phone and remote connection when needed. We can review lessons, discuss questions or even troubleshoot existing reports. For more details see the "Individual Training" page on my web site.
Using a custom group name
Whenever you add a group to a Crystal Report, Crystal creates and inserts a field called the “Group Name” as a heading for each group. These values are also what you see in the group tree along the left of the screen. In most cases the group name simply shows the values of the group field, but there exceptions. For instance, when you group on a date field you can choose to group “for each month” or “for each year”. The group name would display only the month or year from the field. If you do a TopN or use Specified Order you may get a final group called “Others”.
Crystal also gives you the ability to customize the group name. You can either pick a different database field to display as the group name, or you can write a formula to be the group name. For instance you might group by a Customer ID, but choose the Customer Name field as the group name. Or you might group by Employee ID, but create a formula that combines First Name and Last Name to be the Group Name.
To make this happen you go into Group Options (Report > Group Expert, Options button then Options tab) and check “Customize Group Name Field”. You will be given the choice of selecting another database field or creating a formula expression using the X+2 condition button.
Here’s a tip – when I want to use a formula expression for a group name, I typically write the formula as a separate formula field. Once that is saved I will go into the group options and put my formula field into the condition formula. If the logic ever needs to be changed it is simpler to modify a formula field than it is to get into the expression editor within the Group Options.
Note that you can also customize group names in cross-tabs, using the “Group Options” button for either the row or column fields. The interface is the same.
When a reinstall won’t fix the formula editor
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. We 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.
Gems from the Archives
Placing a group of fields onto the report in a column (2010.05)
Setting a Crystal Report to Any Datasource (2010.05)
Contact Information
Ken Hamady, MS
525K East Market St.
PMB 299
Leesburg, VA 20176
(540) 338-0194
ken@kenhamady.com
http://www.kenhamady.com
Copyright 2020 by Ken Hamady
All rights reserved - Republishing this material requires written permission