The Crystal Reports Underground News - Volume 2019.11
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for November 2019:
** Crystal Reports formula function libraries (2019)
** Creating a "Distinct Sum" when the duplicates not grouped together
** My library of Crystal Reports materials
** No column headings on the last page (v2.0)
** Correction to "elapsed time string" formula
** Let me create your Crystal Reports
** Table filters in the database expert
** Converting an Excel column from Characters to Numbers
** Individual Training with an expert.
** Literal values that include quotes
** Crystal crashes with no error message
Gems from the Archives
Cross-Tabs with multiple initial columns (2009.9)
Crystal Reports and the color orange (2009.10)
Crystal Reports formula function libraries (2019)
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 can now convert numbers to Arabic text and provides more robust encoding for Barcode 128, along with some other enhancements to existing capabilities.
If you need help deploying one of these functions in a project let me know.
Creating a "Distinct Sum" when the duplicates not grouped together
Crystal has a distinct count function but not a distinct sum. A distinct sum would be a sum that skips values in one field whenever there is a duplicate in a separate "key" field. It would work something like this: "only add each customer's balance into the total once – even if a customer shows up in several different places in the report. The customer ID would be the "key" field while the balance would be the field you are totaling. It should only count the value on the first instance of the key field.
If you can group the duplicates together the solution is simpler. You can use a running total and set it to "evaluate on change of group" where the group is the key field. I teach this method in my advanced material. But a long ago student showed me a clever way to identify duplicates, even when they were scattered. I had never seen that method and couldn't find it described anywhere else. I published it in my newsletter in 2004. When I needed to use it last week I decided to post it here.
Last week my challenge was a payroll report that showed employees grouped by department, showing pay and withholding. Pay was split by Dept but the withholding was combined. So when an employee worked in two different departments, his withholding would show up twice in the totals. I needed the totals to count the withholding once per employee, even if the employee showed up in several departments (on different pages).
To use this method you first create a running total field that is a distinct count of the "key" field. Then you create another running total, this time using variables, to total the numeric field. This formula has logic to only add the value when the first running total has changed from the record before. Any time there is a change in the first running total it must mean there is a new value for the "key" field. When the first running total doesn't change it means that the "key" field value has appeared before. The formula I used looks like this:
WhilePrintingRecords;
NumberVar Prior;
NumberVar LYS;
//test if first record for this customer
if {#CustCount} = Prior + 1
then LYS := LYS + {Customer.Last Year's Sales};
//store current count to use for next record
Prior := {#CustCount};
LYS
If you need a Distinct Sum as a Subtotal you would reset the running total and both of the variables with each group.
I have seen other developers solve this problem by creating an array of all of the "key" values and checking each "key" value against the array before adding the numeric. This method lets Crystal handle that duplicate check so there is no reason to maintain an array.
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.
No column headings on the last page (v2.0)
Last month I wrote an article about suppressing the page header on the last page when there are no details. This is handy if your last page is a subreport, a chart or a cross-tab. After my newsletter went out one of my readers shared her approach to the same problem. She uses the group header of a dummy group, and sets it to "repeat" on each page.
Any time you have a Group Header you can set it to repeat on each page. One feature of a repeating GH is that it won't appear on the last page of a group, unless that page has at least one detail record. I wrote about that behavior in another article long ago. So the only trick is to create a primary group that includes every record in the report. Then you set this group header to repeat on each page and it behaves just like a page header, with the exception of not printing on the last page.
So how do you create a group that includes all the records in the report? You group on a value that doesn't change. If you have a DB field like "company" that doesn't change you can use it. But you can always create a formula that isn't tied to any data fields. My favorite dummy group formula is:
WhileReadingRecords;
"All"
The word "All" can be any value. Just keep in mind it will appear as the overall node of the group tree so you might want it to make some sense. The WhileReadingRecords function allows the report to see this static value as a recurring value, which makes it eligible for grouping.
Once you create the formula you use it as Group 1 in the report and then go into Group Options and check "Repeat Group Header on each new page". If you put your column headings in this Group Header they will appear on every page, but won't appear on the last page (unless there are details printing on that page).
And thanks to Tina Nordyke, the DBA for Advocates for Basic Legal Equality, Inc for suggesting this method.
Correction to "elapsed time string" formula
My web site has a page for commonly used formulas. Many have been there for years. Formula page 9 has two versions of a formula that will convert a number of seconds into an elapsed time string. The long version has days, hours, minutes and seconds. The shorter version is just hours and minutes.
I was using the short version in a customer's report and we noticed that the minutes value was sometimes off by one. After some testing we found that if the remaining seconds were between 30 and 60 the formula would always round down because the formula used a Truncate() function. The Truncate() works correctly in the long version of the formula, because you truncate down to the whole minutes and then display any remaining seconds. But since the shorter version doesn't display seconds it is more accurate to Round () the seconds to the nearest minute. So I have updated the short version formula on my site to use the Round () function for minutes.
So those of you who have used that formula should update your reports by changing the last Truncate to a Round, or just taking the updated formula from page 9.
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.
Table filters in the database expert
This week I worked with two different customers, helping them locate data buried in a large system with hundreds of tables. In both cases I had to use the table filter method to help me find the correct tables. For instance, when we needed to find the vendors table we looked first in the V section of the table list. However, in the system we were using all of the tables names had a 2 character prefixes for a dozen or more different modules. It wasn't clear which module might contain this table. So I added a filter to the list of table names shown in the Database Expert. This way only table names that contain VEND somewhere in the name would be displayed. It made it easy to spot the correct table.
To add a filter to the table list you need to go into File > Options > (Database Tab).
In the middle section you will see two boxes on the right, and the top one is for table filters. It is labeled [Table Name LIKE:]
In this box you enter the characters you are looking for with a percent sign before the characters and/or after the characters. This percent sign is a wild card symbol. So since we only wanted to see tables that contained VEND anywhere in the name we put in a filter like this:
%VEND%
If I wanted all tables that have TMP on the end I could enter a filter with only one wildcard, like this:
%TMP
The only downside I have seen when using these filters, is that it is easy to forget about the filters. It then takes a bit to figure out why the table list is missing or incomplete. So don't forget to take the filter out when you are done.
Converting an Excel column from Characters to Numbers
I have written before about the challenges of reading data from an Excel, mainly because Excel doesn't define data types for columns like you do in a data table. That article included a formula to convert a column of numbers into a column of equivalent strings.
Last week I had to do the opposite. A customer wanted to link a data table to a spreadsheet, but the linking field in the spreadsheet had numeric characters, while the data table had a true numeric column. There is nothing you can do in the Crystal linking window to get a numeric column to link to a character column, so we had to convert the Excel column from characters to numbers. Just like the previous article, you can't fix this by formatting the column. The data type assigned to an Excel column isn't affected by the format of the column, but only by the actual values stored in that column.
After a few experiments I discovered a solution. If you write a formula like this in Excel:
A1 + 0
The result will be a number even if the cell A1 is a character string with numeric characters. Even better, if there are any true numbers in the column, the formula will work the same for them. So we copied that formula for the entire column and that new column became our linking field.
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.
Literal values that include quotes
I was recently working on a formula that had the crystal syntax for "is one of". It looked something like this:
{LastName} in [ "Smith","Jones","Thompson","Rutledge","Harris" ]
Each value goes in quotes (single or double). You separate the values with a comma and you put square brackets around the list. So I was surprised today when I saw that the formula was actually like this:
{LastName} in [ "Smith""Jones","Thompson","Rutledge","Harris" ]
Notice the comma missing between the first two values. The report had been running for months without error messages which I didn't understand, so I started testing. My experiments pointed me to a syntax rule that I had read about but never used. It is for when your formula includes a literal string that you surround with double quotes, and when the literal string itself contains double quotes. Crystal would assume the first quote is the 'open' and the second one is the close, even when you want the second one to be part of the visible output. One solution is to use two consecutive double quotes within the literal. Whenever Crystal finds two consecutive double quotes within a literal surrounded by a pair of double quotes, Crystal will interpret the consecutive quotes as one literal quote and not as a closing quote. This is much easier to explain with an example. So if I wanted a formula to output this string:
The syntax for "is one of" uses brackets
I could write it like this inside the Crystal formula:
"The syntax for ""is one of"" uses square brackets"
The formula engine would only display one of the two consecutive double quotes in each pair. That explains why my formula with the missing comma doesn't generate an error. Crystal treated the consecutive double quotes as a single literal and then combined the first two elements in the list as being the single value:
Smith"Jones
There was no error message, but the results would not have been correct.
BTW, the same principle applies to single quotes. If you put two consecutive single quotes in a string surrounded by single quotes, Crystal will ignore the first and treat the second as a literal quote.
The reason I didn't think of this right away is that I have never used consecutive quotes in a formula. If I have a string that needs to contain single quotes I surround it with double quotes. And if the string needs to contain double quotes I surround it with single quotes. In the rare case that a string needs both I would split it into separate pieces and combine them. I find that using consecutive quote pairs makes the formula harder to follow.
Crystal crashes with no error message
MDB is the original Microsoft Access database format. It was replaced by the ACCDB format in 2007, but there still applications that use MDB files. For instance I have a handful of customers using Raiser's Edge software for donor tracking, and this application still creates export files in MDB format.
One Raiser's Edge customer recently contacted me about a report that kept crashing. There was no error message – Crystal would just shut down. The report had 32 tables and 31 joins. If he deleted one of the tables the report ran fine. As soon as he added the last table the report would crash.
In my testing I found that it didn't matter which table was dropped. So it became apparent that the issue was a limit, somewhere on the number of tables or joins. But it was hard to determine where the problem lay without any error messages.
First I checked the SQL generated by Crystal Reports and that looked normal. I copied the SQL into a new SQL command but that had the same exact limit.
Next I tried the same report using different connection methods. Both OLEDB and ODBC failed in the same way as DAO.
Then I decided to see if the problem was in the MS Access engine so I copied the SQL From the report and pasted it into a new MS Access query. (I am one of those people who still use MS Access 2002.) The query would run fine in MS Access as long as I dropped one of the tables. If I added that last table the MS Access query would generate an error that said:
"Query is too complex"
I couldn't find the official limits for MDB files but I did find a page that showed the limits for ACCDB files and it says that the number of tables in a query is limited to 32, which in some cases can be reduced even lower. So apparently we hit the limit. And worse, when you exceed the limits for MS Access, the report can't survive to give you an error message. It just dies.
Gems from the Archives
Cross-Tabs with multiple initial columns (2009.9)
Crystal Reports and the color orange (2009.10)
Removal instructions:
I have sent you my newsletter because you or your company are one of my consulting or training customers. If you don't wish to receive the newsletter you can use the link below.
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 2019 by Ken Hamady
All rights reserved - Republishing this material requires written permission