Archive for November, 2020



Crystal Reports formula function libraries (2020)

Saturday 28 November 2020 @ 11:15 pm

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.




Using a custom group name

Monday 23 November 2020 @ 11:12 pm

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.




Running totals vs regular summary fields

Monday 16 November 2020 @ 11:48 pm

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.




One more note about Group Selection

Tuesday 10 November 2020 @ 11:16 pm

My last post talked about Group Selection and showed off 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.





Recrystallize Pro