The Crystal Reports Underground News - Volume 2018.09
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for September 2018:
** Updated comparison of Crystal Reports viewers (2018)
** Service Pack 3 for Crystal Reports 2016
** My library of Crystal Reports materials
** Crystal Reports versions over time (2018)
** Crystal Reports FAQ on the SAP website
** Let me create your Crystal Reports
** Selection based on two different records in the same group
** A temporary formula change that expires automatically
** Open enrollment Crystal classes in Frederick, MD
** Why is the group tree ODD sometimes?
** Numbers that touch operator words
Gems from the Archives:
Using the Report Explorer (Sept 2008)
The effect of dashes on the VAL() function (Sept 2008)
Updated comparison of Crystal Reports viewers (2018)
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 nine 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 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 Software
If you have feedback to provide on any of these products, I would love to know what you think.
Service Pack 3 for Crystal Reports 2016
I don't usually rush to apply service packs (i.e. 'if it ain't broke…'). Usually the fixed items don't apply to what I do. So when SAP notified me about SP3 for CR 2016 I was ready to ignore it for a while. But I read through the list of fixes anyway, just in case, and the second to last item was something useful. It corrects a scrolling bug in the formula editor so we can now use the mouse wheel to scroll up and down in long formulas. I decided to do the update right away since I write lots of long formulas.
But the update was a hassle. It starts with 5 minutes of extracting files and then immediately pops up a "Fatal" error:
"Fatal Error: At least one port in the range 4520-4539 must be opened for the installation to proceed. …"
So why would a service pack for a locally installed app require access to ports? After a few searches I found that this is a red herring error message. It also occurs on some full Crystal Reports installs. The keys to getting past this error on a full install are to:
1) Run the setup from the Root folder
2) Use "Run as an administrator".
But the service pack is a self extracting compressed exe. Placing the exe in C:\ and running it as administrator didn't solve the problem for me. What worked for me in both Win 10 and Win 8.1 was to:
1) Use an Unzip utility (e.g. 7zip) to extract the compressed files/folders into a new folder.
2) Put this new folder in C:\.
3) Run the Setup.exe in this folder as an administrator.
Even then there was another snag. The install couldn't finish because of a "suppressed reboot":
"A reboot request was suppressed on the local machine.
Reboot the machine in order to proceed with the installation"
But it wasn't clear how to do this. My only options were "retry" and "cancel". So I cancelled, did a manual reboot and then ran the setup.exe a second time. That allowed the SP to install and now I can scroll through my formulas. It was just much more work than I expected.
My complete 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.
Crystal Reports versions over time (2018)
I do this chart each year based on my newsletter subscribers. It shows the version of Crystal Reports that they were using when they signed up for the newsletter. Over time, it shows which versions have staying power and how quickly new versions take hold. I have included numbers for 2018, even though the year is only about two thirds over. Those numbers will shift a bit in next year's chart when 2018 is complete.
The chart shows that over 25% of my 2018 subscribers are still using Crystal Reports XI (circa 2005) which is version 11.x. Last year I was concerned that some of these users might really be on CR 2011 (version 14.o) since those do get confused. But when I Emailed some of those people they all said that they were using the old XI version. For some of them, upgrading past Crystal Reports XI would be a major undertaking, because all the newer versions are limited to the .NET runtime. Customers that have built an application around the older runtime engine would have to rebuild the Crystal components of that app in order to upgrade to a later version of CR.
Crystal Reports FAQ on the SAP website
I just stumbled across a FAQ on the SAP website that has some useful information. It was written in 2016 but the information still seems to apply. Many of the answers are links to other pages, like the link to the trial versions or the links to the service packs. I already had most of this information, but learned at least one new trick from the FAQ page.
Retrieving your License Key from the registry:
With older versions of Crystal you could go into Help > About and grab your complete license key. This was helpful if you were changing hardware or installing on a second PC. With more recent versions you have to go into the license manager and you can only see a portion of the key. I recently had to track down a license key and wished I knew how to extract it from the PC. Today I saw that 0ne of the tricks in the FAQ is how to find a full license key by searching the registry, using the portion of the key you can see in the license manager. I'll be ready next time.
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.
Selection based on two different records in the same group
I was visiting Tek-Tips.com today and found an interesting question to answer. A user was having trouble counting sales orders that had items with a certain comment. He wanted to group and count the occurrences by the item ordered. It sounded simple, but the item number was in an "item" record while the comment was on a separate "comment" record. When he filtered by the comment there would be no item numbers and if he grouped by item number the comments would be in a different group. He needed to get the two records into one record.
To solve a problem like this you need to add the sales order table to the report two separate times to create what is called a "self-referencing join". The second time you add the table, Crystal will give the table a slightly different name (an alias) usually by adding _1 to the end of the table name. Then you treat the two instances as if they were two separate tables. In this case you could think of the first table as the "item" instance and the second as the "comment" instance. You link them based on the Sales Order and then add two filters, something like this:
{SalesOrder.LineType} = "Item" and
{SalesOrder_1.NComment} = "Your specific comment"
Note that the two rules above reference one field from each of the two table instances, bringing the two separate records together as one. From there you can group by item number (from the first table) and count the number of records within each group. Here is a link to the tek-tips question which has a screen shot of the data.
A temporary formula change that expires automatically
Sometimes I need to make a temporary change to a report. For instance I want to might want to skip invoicing one or two customers for a week or two. So I will put a rule in the selection formula that eliminates them from the report. However, I have a tendency to lose track of these changes. Several months later I will realize that these customers have not been getting invoices.
So I have started putting in changes that are time limited. That way, I don't need to remember to reverse the changes. For example, if I want to hold off on invoicing two customers for the next few days I could add something like this to the selection formula:
. . . and (if DataDate < Date (2018, 8, 31)
then not ( {Cust.ID} in ['ABCD' , 'EFGH'] ) else True )
In English this says, 'if this report is refreshed before 8/31 then don't include these two customers, otherwise ignore this rule'.
Eventually I will notice the rule and take it out but I don't have to worry about when, because the rule turns itself off automatically. You can put a similar time limit on any change that can be driven by a formula.
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.
Why is the group tree ODD sometimes?
When you preview a report in Crystal the left side of the screen should show you the "group tree". This lists all of the groups in the report. It also allows you to go directly to the first page of any group, just by clicking on that value in the tree.
But a few times a year I work with a report where the group tree is in "Only Drill-Down" mode (ODD). In this mode, every entry in the group tree is accompanied by the drill-down indicator (a magnifying glass). Clicking on an entry no longer takes you to the first page of that group but instead it takes you to a drill-down tab for that group. To get to the correct page for a group I have to do a search.
It is a minor irritation so I have let it go for years. It just never seemed worth the time to figure out why some reports do this. But I figured it had something to do with the Hide/Suppress properties of the Group Header (GH) and Group Footer (GF). This week I got an ODD report from a customer, and so I decided to test all the combinations and see which ones were ODD.
I found four rules that control this behavior:
If either the GH or the GF is visible you get the normal group tree.
If both of those sections are suppressed you get the normal group tree.
If both of those sections are hidden you get the ODD behavior.
If one of those two sections is hidden and the other is suppressed you get the ODD behavior.
I can't explain the reasoning behind this pattern (or even the purpose for the ODD behavior) but at least now I know how to change it when I see it.
Numbers that touch operator words
I was shown an unusual formula today. It looked something like this:
if {Customer.Customer ID} = 14then 30else 0
Note the space missing between the numbers and the words THEN and ELSE. These two words didn't turn blue so I figured they weren't being recognized and would generate an error. Surprisingly, the formula saved without error and the number 30 showed up in the correct places. The reserved words were still being recognized correctly, even without the spaces.
I did some testing and found that any formula where a number is usually followed by a space and then an operator word will work the same without the space. I tested this in some older versions of Crystal and it worked the same at least as far back as Crystal v8.5 (2001).
The only reserved words I can think of that can follow a number are these operators: AND, OR, IN, TO, THEN, ELSE, MOD and DO. These all behave the same way. I can't think of any functions that can directly follow a number. A space is still required if the number follows these words.
So if you ever find a formula like this and wonder how Crystal is handling the error, now you know.
Gems from the Archives:
Using the Report Explorer (Sept 2008)
The effect of dashes on the VAL() function (Sept 2008)
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 2018 by Ken Hamady
All rights reserved - Republishing this material requires written permission