The Crystal Reports Underground News - Volume 2020.09
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for September 2020:
** Updated comparison of Crystal Reports viewers (2020)
** How to remove the SAP BW toolbar in Crystal Reports 2020
** My library of Crystal Reports materials
** Converting a character date to a true date
** Converting String and Numeric Dates to DateValues (part 2)
** Let me create your Crystal Reports
** Get distinct count subtotals to add up to the grand total
** Why would you “Perform grouping on server”?
** Individual Training with an expert.
** Fields that change format when moved to a different environment
** rePORTAL v6.5 released
Gems from the Archives
Reporting on the Database Schema (2010.03)
Superscript numbers in a Crystal Report (2010.03)
Updated comparison of Crystal Reports viewers (2020)
You use Crystal Reports to create, change and run reports. But what if you have users who just need to refresh/view/print/export? Do they need copies of Crystal Reports? Do you need to configure an expensive web server?
The most cost effective method for letting a user run reports is to install a third-party client-based viewer. They are offered by ten different vendors. Don’t get sidetracked by the official SAP “viewer” because that tool won’t refresh reports. Every viewer in my list allows you to refresh reports.
Every September I update the features of these viewers. The comparison page provides a brief introduction to each product including what sets it apart. There is also a detailed feature matrix (xls) that shows some of the specifics for comparison, like prices. I have even included a glossary of features in case you aren’t familiar with the terminology. There are a handful of new features in this year’s matrix which are marked in blue.
There are 10 active products in this year’s review and 4 “ghost” products that are mentioned as warnings. A ghost product has a web site but it hasn’t changed in years and no one responds to requests for information.
The active vendors are:
Crystal Corral by Groff Automation
rptView by Pursuit Technology
CR Dispatch by APB Reports
cView by Chelsea Technologies
ViewerFX by Origin Software
CrystalKiwi Viewer by CrystalKiwi
Logicity Pro by SaberLogic
Report Runner Viewer by Jeff-Net
RTag Report Viewer by RTag
DataLink Viewer by Millet SW
If you have feedback to provide on any of these products, I would love to know what you think.
How to remove the SAP BW toolbar in Crystal Reports 2020
If you have installed the new Crystal Reports 2020 you may have noticed a new menu option and toolbar for SAP BW.
Gordon Portanier of ReCrystalize decided to do some experimenting to see if he could get CR to run without those options appearing. He found that when he renamed these 4 dll files, both the menu option and the toolbar didn’t appear :
crdb_bwmdx.dll
crdb_bwmdx_res_en.dll
BWQueryReportWrapper.exe
BWQueryReportWrapper_res_en.dll
He found the dlls in this folder:
C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win64_x64
The program seemed to run normally without these dll files. So if you decide to upgrade and don’t have a use for the extra menu options, this is something you can try.
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.
Converting a character date to a true date
I run into many situations where dates are stored as numbers or strings. These need to be converted to true date values before they can be used in date calculations. I have posted formulas on my site that convert common numeric and string values into true dates.
One of the challenges when converting dates is dealing with invalid values. These will generate an error when you attempt to convert them into dates. One way to validate a string input is with the IsDate() function. This checks and see if a string value can be converted into a date before doing the conversion. It is usually partnered with the DateValue() function which converts any valid date string into a true date value. The formula would look like this:
If IsDate ({String.Field}) then DateValue({String.Field})I did some experimenting with this today and I was surprised at how many different patterns can be converted using these functions. The general rule is that the string needs 2 or 3 separate parts in one of these 5 patterns:
Month-Day-Year
Year-Month-Day
Day-Month-Year
Month-Day (assigns current year)
Month-Year (assigns day as 1)
The year can be 2 or 4 digits, except for Month-Year pattern which MUST have a 4 digit year.
(2 digit years >= 50 are assumed to be 19xx, while those < 50 are assumed to be 20xx)
The month can be a number, the full month name or the 3-character abbreviation.
The separators can be a slash, a dash, a space or a comma (or any combination).
This gives you over 400 different patterns that can be directly converted into a date. Here are just a few common string examples that will all return the date value 12/1/2020 once converted:
12/1/2020
December 1, 2020
2020-Dec-1
1-12-2020
Dec 2020
12/1
**Note - Thanks to Michael Hurwood for pointing out that this is tied to PC locale settings. For example, if you are outside the US you would get January 12th for the first example above.
Converting String and Numeric Dates to DateValues (part 2)
I used the method above to update some formulas on my formulas page. The advantage of this method over the original formulas is the extra validation provided by the IsDate() function.
But there are one or two extra steps needed to use DateValue() to replace the original logic in Formula #2 and Formula #3. The DateValue() function needs separators like dashes or slashes. When character strings have no separators you need to add them using the Picture() function. If the field is a numeric, you need to convert the field into a string first, and then use Picture() to add the separators.
If the field we are converting is a string with values like this: 20201231
Then the formula would look like this:
Local stringvar z := {Table.TextDate};
z := Picture (z, 'xxxx-xx-xx');
If IsDate (z)
then DateValue(z)
But if the value looks the same but is stored as an 8-digit numeric, then we need to add an extra step:
Local NumberVar y := {Table.NumericDate};
Local StringVar z := Totext (y,0,"");
z := Picture (z, 'xxxx-xx-xx');
If IsDate (z)
then DateValue(z)
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.
Get distinct count subtotals to add up to the grand total
I just had a customer ask me about their distinct count grand total and why it didn’t match the sum of the subtotals. This is common. When you add up distinct counts at the group level they often won’t add up to the grand total. This is because of what a distinct count is designed to do.
A distinct count summary will count how many different values there are in a column. So if I group patient visits by doctor and then want CR to tell me how many different patients each doctor saw in that period, I would use a distinct count at the doctor group level. And if I also wanted to know how many different patients were seen across ALL doctors I could create another distinct count of the patients and put it in the report footer. But it is very likely that this grand total is not going to match the sum of the doctor subtotals. This is because any patient who saw more than one doctor in the period will show up once in each doctor’s group, but then will only be counted only once in the grand total.
But there are special cases where the grand total needs to be the sum of the subtotals. If you find yourself with this requirement the solution is simple. You create a formula that concatenates the group value with the field used in the distinct count. (In the example above you would combine the doctor with the patient.) Then you do a distinct count of that formula. This will give you the same values at the group level, but it will prevent the grand total from combining values from different groups.
This technique is article #25 in my Expert Techniques volume 1.
Why would you “Perform grouping on server”?
I was asked about this feature today and noticed that I had never mentioned it in my blog. It is a strange feature because you can turn it on in most reports and yet in most reports it will do absolutely nothing. But when you get it to work it can greatly improve the performance of the report. You will find this option in the database menu.
So what does it do? It tells the database to do the grouping and subtotals. The database will only return one summary record for each group. Check the SQL statement and you will see a GROUP BY clause at the end. This is useful when you have huge amounts of data to process and when you don’t need to show any detail level values. But, this feature only works in reports that meets some very specific criteria.
Things you must do:
Group on a database field or a SQL expression (not a formula)
Hide or suppress the details
Make all of the visible fields either a group field or a summary field
Limit the summaries used to those supported by your Database
(Sum, count, minimum and maximum should be safe in all databases)
Things you must NOT do:
Use specified order grouping.
Add running totals using detail fields.
Add summaries using formulas fields.
If your report meets these criteria, Crystal should be able to add the GROUP BY to your SQL statement, get the server to group the records and calculate all of the summary fields. Use the Show SQL option to confirm the GROUP BY appears.
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.
Fields that change format when moved to a different environment
I recently had complaint from a customer that a numeric field was changing format when moved from one PC to another. The format was fine when run on the developers PC but on the user’s PCs the format would change. I have seen this happen with both numeric and date fields.
The source of the problem is the format option at the top of the list called “System Default”. If you leave a field formatted with this choice the report will use whatever format is set in the regional setting of the PC where the report is being run. Of course this means that a report might look different when run on another PC. If you don’t want the format to change from one PC to another then you should identify the specific format you want for numeric, currency, date and datetime fields.
Using a specific format should solve this 99% of the time. I have only seen a few instances where a report moved to another environment would have formatting changes despite the format being specified in the report. In those cases the final workaround is to write a formula to display the value. You can use Totext() to convert the field into text that is formatted the way you want. You use the text version for display purposes while using the original field for other functions like sorting and totaling.
Here are some examples:
Totext ({Date.Field}, yyyy-MM-dd) //converts a date into a string like 2020-09-21
Totext ({Number.field}, 0,"") // converts a number into a string with no decimals or commas
rePORTAL v6.5 released
rePORTAL is one of the products in my list of tools that run Crystal Reports on the web. The developers at rePORTAL recently released rePORTAL v6.5. Some of the new features include the ability to share output to Slack, Dropbox and Google Drive. It also now supports Multi Factor Authentication (MFA) using SMS, Email and Google Authenticator.
If you are looking to run Crystal Reports on the web, my annual review of tools in this class should help you understand your options.
Gems from the Archives
Reporting on the Database Schema (2010.03)
Superscript numbers in a Crystal Report (2010.03)
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