The Crystal Reports Underground News

The Crystal Reports Underground News - Volume 2021.07

an independent source for Crystal Reports Information
by Ken Hamady, MS

Contents for July 2021:

** RPT management utilities for 2021
** Download my advanced course materials
** My library of Crystal Reports materials
** Anyone want to recompile this free dll to be 64-bit?
** U2lwin32 recompiled for 64-bit, available to test
** Let me create your Crystal Reports
** Transitioning from 32-bit Crystal Reports to Crystal Reports 2020
** Using HTML interpretation
** Individual Training with an expert.
** Crystal Reports 2016 date formatting issue
** Undocumented limit on the ExtractString() function

Gems from the Archives
Using Approximate Joins (2011.01)
Does IsNull() let you filter a Left Outer Join? (2011.01)

Comparison of RPT management utilities (2021)

I have just updated my comparison of RPT management utilities for 2021. These are tools that allow you to scan, document, compare and in some cases batch update RPT files. The list includes 9 tools:

Report Runner Documentor by Jeff-Net
R-Tag Documentation and Search by R-Tag
CR Data Source Updater by R-Tag
Visual CUT and DataLink Viewer by Millet Software
Report Miner by the Retsel Group
Code Search Professional by Find it EZ Software Corp.
Dev Surge 365 by Find it EZ Software Corp.
.rpt Inspector 3 Professional Suite by Software Forces, LLC
.rpt Inspector Online by Software Forces, LLC

Download my advanced course materials

In April I made my Intro course materials available for free. I mentioned this in my blog and newsletter and so far several hundred users have downloaded the material. This month I am going to make my Advanced course materials available in the same way.

Just like the Intro material, you are welcome to download the materials and use them. Share them with your friends. Please do not modify them or try to sell them.

Note that as a consultant, people often pay me to help them use or learn Crystal. You can, too. So if you have questions about Crystal Reports I am happy to schedule a short consult for you. This is explained further on these links:

https://kenhamady.com/faq.shtml
https://kenhamady.com/support/default.html

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.

Anyone want to recompile this free dll to be 64-bit?

In a recent blog post I mentioned the U2lwin.DLL. It is a Crystal Reports user function library (UFL) that is currently available in 32-bit but not yet in 64-bit. This means that users who have this dll and who upgrade to CR 2020 will lose some of their formula functions.

This week I checked in with the developer, Bjarke Viksoe. He has always posted the source code for this dll on his web site. He said it is fine if someone else takes the source code and creates a 64-bit version of the dll. He doesn’t even mind hosting the upgraded file if that helps.

Of course, most of my readers are not application programmers, and neither am I. But I know that a few of you write serious code. If anyone wants to take a crack at this you can download the source code and give it a shot. The current source code uses Microsoft Visual C++ 6.0.

In the meantime, if you upgrade to CR 2020, there is another DLL that you can purchase called Cut Light by Millet Software. It is one the UFLs in my annual comparison of UFLs. Cut light has at least twice as many functions as U2lwin with many that are the same. So it might have the functions you need, and it comes in both 32-bit and 64-bit. It isn’t free but it also isn’t very expensive.

U2lwin32 recompiled for 64-bit, available to test

In the post above I invited developers to recompile the source code for this free 32-bit UFL into a 64-bit version of the same DLL. Right after that post someone sent me a DLL they had compiled and I have posted it on my site. I consider it a Beta version since he only tested the function he needed (FileAppendLine). But there are 37 other functions included in the dll. The developer also admitted that “the code still assumes a 32-bit word length”, which might cause problems on some of the functions.

I can’t test it yet since I haven’t upgraded to CR 2020. But if you have CR2020 and want to try some of these functions, you can download and test the DLL. If you do any testing, let me know which functions you try and if you run into any problems. And if someone feels like doing a more complete conversion, you can download the source code for the original 32-bit dll.

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.

Transitioning from 32-bit Crystal Reports to Crystal Reports 2020

Some of my customers are transitioning from 32-bit versions of Crystal Reports to Crystal Reports 2020, which is 64-bit. This creates some issues since ODBC/OLEDB drivers are either 32-bit or 64-bit. Switching to CR 2020 requires different drivers and (if using ODBC) different ODBC Data Source Name (DSN) entries.

The same applies to UFL function DLL files. For instance the free UFL named u2lwin32.dll only comes in a 32-bit version, but there is a 64-bit beta mentioned earlier in this newsletter. Some commercial ones come in both 32-bit and 64-bit editions. Switching to CR 2020 requires that you use 64-bit UFL libraries.

The good news is that RPT files in CR 2020 are backward compatible with earlier versions, probably back to Crystal Reports v9. So you can modify and run a single RPT file in both CR2020 and earlier versions. But you do need different connectivity. What my customers are doing is naming the connections the same in both the 32 and 64 bit environment. That way users in both environments can run the same report without modification.

If you run into any strange behaviors in using CR 2020 or in the transition, please let me know and I will share with others.

Using HTML interpretation

Sometimes database fields store their data with HTML formatting tags. When you put that field on the report it will display the raw HTML tags. However, if you format the field to use “HTML interpretation” Crystal will try to use the HTML tags to format the text. Crystal doesn’t support ALL HTML tags and properties, but there is a list of the ones that are supported on this page.

Another way to use HTML in Crystal is to include HTML tags in your formulas. For instance I could use a few HTML tags to highlight only a portion of a formula field’s output. Below is the formula for a 3-line address with the City name in bold blue text:

{Customer.Customer Name} & '<br>' & {Customer.Address1} & "<br>" & "<b><font color='#00559c'>" & {Customer.City} & '</font></b>, ' & {Customer.Region} & ' ' & {Customer.Postal Code}

The formula uses 4 different tags:

The tag after the first 2 lines creates a new line.
The tags mark the beginning and end of the bold text.
The tags mark the beginning and end of the blue text.

You can look up other color code using a color picker site.

When you first put the formula on the report it looks like the left example below. You then use the menu options “Format > Field” and select the “Paragraph” tab to see the “Text Interpretation” option at the bottom. Select “HTML Text” and the formula will look like the right example below.

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.

Crystal Reports 2016 date formatting issue

When you set up Windows you get to select a Regional Format for things like dates and times. For instance, English(US) format will show today’s date as 7/21/2021 while English (UK) will show today’s date as 21/7/2021. In Crystal, certain date objects can be assigned to use these formats, so that they automatically change from one format to another based on the regional format setting of the PC running the report. This is handy if you have users in different countries. To use this feature, right-click on the date field, choose “Format Field” and on the “Date” tab select the “System Default Long/Short Format”.

However, I just had a user complain to me that he couldn’t get CR 2016 date fields to respond to the regional setting. So, I did a quick test in my own environment and found that changing my regional Format from English (United States) to English (United Kingdom) had no effect on date format in CR 2016. I closed Crystal and reopened it, created a brand new simple report and put the print date in the Page Header – and it still showed in US Format. To make sure I was using the right setting I tried the same test in CRv10 and the print date came up in UK format. I repeated the test in CRv8.5 and it also came up in UK format – but not in CR 2016.

I did an online search and found an SAP thread where someone else had the same issue when they upgraded to CR 2016. SAP’s response focused on the database client (?), but the issue didn’t appear to have been resolved.

I wonder if Crystal is somehow pulling the regional format correctly when it is installed, but then isn’t updating the region if it is changed after the install. If anyone is seeing different behavior or has insight to share, please let me know.

Undocumented limit on the ExtractString() function

A few years ago I wrote about a limit on the number of tables you can use when connecting to MS Access. The limit is 31. If you add table #32 to a report it will crash with no error message. Even using 32 tables in an MS Access query will generate an error message. I see this occasionally when dealing with reports for Raiser’s Edge.

Today we needed table #32 and I found a way to get it. I made a duplicate copy of the MDB and connected to the first instance to get 30 tables. Then I connected to the duplicate MDB to get the other two tables. I selected those two because their joins made up a separate branch from the other 30.

This worked because Crystal does two separate queries, one for each MDB, and then merges the two result sets locally. Crossing connections a last resort, and is never very efficient. The report was a bit slower but at least it would run.

Gems from the Archives
Using Approximate Joins (2011.01)
Does IsNull() let you filter a Left Outer Join? (2011.01)

Ken Hamady, MS
525K East Market St.
PMB 299
Leesburg, VA 20176
(540) 338-0194
ken@kenhamady.com
http://www.kenhamady.com

Copyright 2021 by Ken Hamady
All rights reserved - Republishing this material requires written permission