I had a customer this week who updated a report to read Oracle instead of SQL Server. The report was based on a command and the SQL was virtually identical. But for some reason the Oracle version would take a full 5 minutes to refresh while the SQL Server version only took a few seconds.
Once the report was run in Oracle the first time it could be refreshed using different parameters and it would only take a few seconds. I had the customer check for indexes but those had been set up to match SQL Server. I wondered if it was a data cache, but that didn’t explain why setting new parameters would be fast.
The only other thing that I could think of that was unique to the first refresh was a setting in Crystal that does a “Verify Database” on the first refresh. This usually takes a few seconds and isn’t noticeable, but as a test I turned that feature off. The first refresh was now as fast as SQL Server. Apparently, something in their environment caused the “Verify” process to take 5 minutes to complete.
If you run into something similar, go into File > Report Options and look for the check mark that says “Verify on First Refresh”. Unless your environment is changing frequently, this doesn’t need to be checked, although in most environments you won’t notice a difference.
If you aren’t familiar with the “Verify” feature you can invoke it at any time by using the option in the database menu. This forces Crystal to poll the database for a complete list of fields for every table used by the report, including the data types. Crystal stores this info in the report, which gives you a full field list even when you aren’t connected to the database. Whenever fields are added, removed or their data type is changed this list needs to be updated . That is what it means to “Verify Database”.
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 new functions and is now available in a 64-bit version for users of CR 2020.
If you need help deploying one of these functions in a project let me know.
Everyone has been talking about the new Java security vulnerability called Log4j. I have been talking to colleagues to determine if this affects Crystal Reports, Crystal Enterprise or anything else in the Crystal ecosystem. SAP put out a note that states that this vulnerability does not affect SAP Business Intelligence 4.2 or 4.3. It doesn’t mention earlier BI versions but these are no longer supported.
There was a support note about this topic, but nothing in the support note mentions Crystal Reports or the Crystal runtime engine used by third party applications. One of my colleagues said that since this is a Java vulnerability, it should not affect stand alone Crystal Reports, and I tend to agree. I also believe that if Crystal Reports were affected it would be mentioned in that support note. The same goes for the Crystal runtime files, but it would be nice if SAP responded specifically to these questions in the discussion above.
Last, thanks to Andrew Baines of Pursuit Technology and Danny Shahrabani of rePORTAL Software for helping me track down and make sense of the SAP support note. If anyone else has info to share on this topic, please let me know.
I received this question several times, recently:
We have a legacy app that uses the Crystal Reports runtime. We want to use a later version of Crystal Reports, but still need the reports to work in our application. Is this possible?
The short answer is “probably not”. The longer answer involves a bit of history.
Crystal Reports, right from the beginning, provided a runtime engine that let you invoke reports from applications. First there was the Print Engine API (1991) which used files like crpe.h, crpe32m.lib and direct calls like PEOpenEngine(),PEOpenPrintJob(). Then in 1995 they released an Active X component using the file Crystal32.OCX (this was included for free with Visual Basic). In 1997 they released something called the Automation server using CPEAUT32.dll. These three different integration approaches worked side by side until 2001 when Crystal introduced the Report Design Component (RDC) which used the file CrAxDrt.dll.
The next year, when CRv9 was released, the RDC became the only supported integration method and the files for the other three methods were no longer provided. Since CRv9 rpt files were not backward compatible, users had to choose between using their legacy code or upgrading Crystal Reports. If they wanted to use the latest version of Crystal Reports they would have to rewrite their application. Many chose to freeze their Crystal version, and some are still using CRv8x and one of the original three runtime engines.
Then in 2003 Crystal released a runtime engine for .NET. For the next few versions of CR you could choose either the RDC or the .NET runtime engine. But in 2008, when CRv12 came out, Crystal no longer supported the RDC. Again users had to choose. If you wanted your application to support the new features in CR 2008 you had to rewrite your application in .NET. But, there was one difference. A crystal report created in CR 2008 (or even CR 2016) is backward compatible all the way back to CRv9. So these later reports can still be run from RDC based applications. However, if the reports use any features that are new after CR 2008 (optional parameters, vertical alignment, etc) these won’t work unless you are using the .NET runtime.
And this is where we are today. The only runtime engine that supports all the features in the current versions of Crystal is the .NET engine. There isn’t anything available in any recent version of Crystal that will work with legacy code from the other 4 integration methods. If you have an application that uses one of these methods your choices are:
1) Stay with your legacy code and use an old version of Crystal to maintain the reports.
2) Rewrite your code using the .NET runtime engine.
Years ago I put out a guide for using the .NET engine in applications. Although it was written in 2003, the basic object model hasn’t changed significantly since then. It is free and you can download a copy – if you think it will help.
I recently helped a new user who wanted to purchase Crystal Reports, but wasn’t sure what product(s) to buy. He had read about a server component, a ‘developer’ component and a viewer, but wasn’t sure what the names were or which he needed. The SAP website didn’t clear things up for him.
“I saw an architecture diagram that broke it into tiers, but that did not help.”
This isn’t the first time I have had this type of question, and I thought my response might help other users.
There are three levels in the Crystal product line (four if you count the Business Objects Enterprise level) but most users only need one, SAP Crystal Reports. That is all I use. Here is the main product page. This is often called the Report Designer or the Developer, but the official name is SAP Crystal Reports. It is the only product that lets you create a Crystal Report.
You can currently buy 2 different editions:
If you get a free trial from the product page I think you will get 2020, but if you buy it you can choose either edition. I currently use 2016 because the 64-bit product is relatively new. I also use lots of third party tools that aren’t ready to jump to 64-bit and I don’t want to leave them behind.
The other products are Crystal Server and SAP Crystal Reports Viewer. Crystal server is a web portal that lets people run the reports from their browser. But, you still have to create them in Crystal Reports and then publish them to the portal. This product can get quite expensive.
The Crystal Reports Viewer is free but is pretty much useless in my opinion. It lets you open reports, but only those that are saved with data. You can’t refresh reports unless you are logged into CR Server. There are plenty of inexpensive third-party viewers out there so not many of my customers bother with the SAP Viewer. Here is a list of the ones I review each year.
If you have any questions about the Crystal Reports product line, let me know and I will try to help.
I create reports for several customers who use Raiser’s Edge donor management software. One of my customers shared this note from BlackBaud that went out today:
“On December 15, 2021, we will no longer support the ability to create or edit custom reports using Crystal Reports directly within any Blackbaud solution. Users will still be able to view historical reports and export file formats that work with Crystal Reports.”
Before the 15th it wasn’t clear if reports would still run in RE, but now it appears that they still do. The only noticeable change is that users don’t have a built-in copy of the Crystal Reports designer. To modify reports they will need to purchase their own license.
If you decide to buy a copy of Crystal Reports to work with your RE export (mdb) files, I recommend that you buy Crystal Reports 2016 instead of Crystal Reports 2020. That will give you something that works very much like what you have now. Crystal Reports 2020 can be made to work but it is 64-bit so it will use different drivers. You could modify reports but you won’t be able to run that same report both outside and within RE.
See this article for a link to the Crystal Reports product page and for clarity on what you do and don’t need to buy.
For the past few weeks several customers have need help with registry changes. So I have spent lots of time referring to a document that SAP has posted on their web site. It shows all the registry keys for Crystal Reports. I wrote about it in my blog. When I need the link I search my blog to find the article that has the link.
Today I went to check that same document and I found that it was just a ‘preview’ of the article. The full article was part of the SAP (password protected) support portal. This change must have happened in the past few days. Fortunately I have an old (free) User ID and password. I logged in and I could read (and download) the full registry key document. It was a minor hassle.
Then I tried to pull up a related article on editing the registry. Again I was shown just a ‘preview’ of the article. This time my credentials didn’t get me to the full article. I was given a second login screen and my credentials were rejected. SAP may be starting to restrict access to certain support materials.
I tried a variation of the second URL and got this message:
You are signed in with a P-user ID.
Visitors with an S-user ID will benefit from more tools and enhanced functionality.
According to this SAP Article, S-user IDs are provided to those who have an active support contract. P-User IDs are for the public and for partners.
If you anyone notices any other content that moves from free access to paid-only access, please let me know.
Most Crystal parameters are static, meaning they use fixed list of values is stored in the report. But Crystal also allows you to create dynamic parameters. Dynamic parameters generate a list of values from the database each time the report is refreshed. This is useful for lists that change frequently.
Normally you can pull a dynamic parameter directly from a table or view used by the report. But if your parameter is part of a stored procedure you won’t be able to pull the parameter’s list of values from that stored procedure. That’s because the stored procedure needs a parameter value before it can return any data. And once it returns data it the only it will have is the one value you choose in the parameter. So trying to pull all the possible parameter values from the stored procedure won’t work.
However, you can still provide a dynamic list of values for a stored procedure parameter. But the list has to come from a separate source. You can choose another table or view that has the list you want to show. Even better, you can write a SQL command that allows you to filter the list to show the exact values you need. This other object (table/view/command) is added to the report along with the Store Procedure. You use it for the dynamic parameter but make sure you don’t use any fields from that object for anything else in the report. That force a separate SQL query, which is a sure way to slow the report down.
Once the object is added you can use it in the dynamic parameter to provide the values and the descriptions. If needed you can add a second level to the parameter to make the dynamic parameter into a ‘cascading’ parameter. A cascading parameter has 2 or more levels. You select values for the first level of the cascade and this determines which values appear in the next level.
For more information on using parameters you can download my Advanced course book and the free “Expert’s Guide to Subreports, Parameters and Alerts“. And if you need more help you can always call to schedule a consult.
After releasing my course materials and my “Expert” series I realized that I had forgotten one item: My set of calendars. This three report set, with instructions, allows you to generate 3 different styles of calendar from your data (continuous, monthly and daily). The first two allow you to show multi-day events as boxes that stretch from the beginning to the end of the event.
Several of my customers read Excel spreadsheets, Microsoft Access MDB files and even text files using DAO connections. This are simple connections made by pointing directly to the data file. Unfortunately, these direct connections are no longer supported in Crystal Reports 2020. I was hoping that might be a temporary situation, but this link on the SAP website provides confirmation:
Crystal Reports 2020 is a 64bit application, and therefore it is no longer possible to connect to Excel or Access using DAO, since there is no 64bit version of this Microsoft Technology. This is why the option “Access/Excel (DAO)” is not available in Crystal Reports 2020.
You can still connect to these data sources using ODBC or OLEDB, but you will need the new 64-bit Microsoft driver. OLEDB actually works in a similar way to DAO, but you will need to do a “Set Datasource Location” in each report to convert it from DAO to OLEDB. I posted instructions in February for using OLEDB to connect to the Xtreme Sample Database (mdb) and the steps would be similar for other types of DAO connections.
If you run both 32-bit and 64-bit versions of CR you might want to switch to ODBC connections (DSNs). With ODBC you can create a 64-bit DSN on one PC and a 32-bit DSN on another PC using the same DSN name. That way you can maintain one rpt file and it can run in both the 32-bit and 64-bit Crystal environments.