The Crystal Reports Underground News - Volume 2017.09
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for September 2017:
** Updated comparison of Crystal Reports viewers (2017)
** Crystal Reports Language for Notepad++
** My library of Crystal Reports materials
** Using NotePad++ for formulas or SQL Commands
** Doing a Maximum "horizontally" as opposed to "vertically"
** Let me create your Crystal Reports
** 25% off Find it EZ products
** Free software for the Houston area
** Open enrollment Crystal classes in Frederick, MD
** Set Location in a Progress database
** Additional parameter values are needed ….
Gems from the Archives:
ToNumber() vs Val() (2007)
Using table inflation to generate multiple labels (2007)
Updated comparison of Crystal Reports viewers (2017)
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 "viewer" that is put out by SAP because that tool won't refresh reports. Every viewer in my list allows you to refresh reports.
Every September I compare the features of these viewers and post the results. The comparison page provides a brief introduction to each product including what sets it apart. There is also a detailed feature matrix (PDF) 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 9 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 repeated requests for information.
The active vendors are:
Crystal Corral by Groff Automation
rptView by Pursuit Technology
cView by Chelsea Technologies
ViewerFX by Origin Software
Crystal Kiwi Viewer by Crystal Kiwi
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.
Crystal Reports Language for Notepad++
Below is my article about using Notepad++ for working with SQL Commands and formulas. I loved the numbered lines and the way it finds matching parens. But I missed the green comments and blue functions I see in Crystal. Notepad++ has about 80 languages that it can format correctly but Crystal formula syntax wasn't one of them. So I decided to see if I could create a custom language for Notepad++ that would format Crystal formulas the way they appear in the formula editor.
After several hours of experiments I have something that I like. It will format a Crystal formula with functions in blue and comments in green. I even added the literals in red like you see in SQL language format. And you still get numbered lines and the ability to find matching parens and brackets. I even added the ability to fold (temporarily hide) sections between a pair of parens.
So, if you are using Notepad++ and would like to try my Crystal language you can right-click on the link below and save the xml file onto your hard drive. If you just click the link normally your browser will likely open the XML in a new window.
CrystalReports Language UDL for Notepadd++
Then to deploy the file in Notepad++:
1) From the Notepad++ menu go to [Language > Define your language ]
2) At the top of the window click "Import" and open the CrystalReports.xml file you downloaded.
3) Restart NotePad++
When you click "Language" from the Notepad++ menu you should now see Crystal Reports at the bottom of the list. When you select Crystal Reports, the current window should display Crystal formulas in the correct format. If you get any strange behavior, please let me know.
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.
Using NotePad++ for formulas or SQL Commands
If you have to write SQL commands or complicated Crystal formulas, it helps to have a good text editor. My favorite is NotePad++. Not only is it free, but it can do many helpful things beyond what Notepad can do:
Number each line.
Highlight the corresponding paren/bracket whenever you select a paren/bracket
Highlight all the instances of any word that you select
Record keystroke macros to automatically repeat a series of commands
Select a vertical strip of characters from within of a larger block of text
(called "column mode", e.g. highlight the 5th and 6th characters in all rows at once)
Find all of the differences between two text blocks or two lists, using the "compare" plugin.
"Fold" (collapse and expand) sections between parentheses or keywords
It can even format the code for 80 different programming languages, including SQL, showing reserved words and comments in different colors. As mentioned above, I have created a UDL (User Defined Language) for Crystal Reports formula syntax. That allows NotePadd++ to format a Crystal formula to look the same as it would in Crystal, with the comments in green and the functions in blue.
If you want to try out NotePad++ the best place to get it is through Ninite.com. I have written about Ninite before. The advantage of Ninite is that it lets you select multiple items from a list of 80+ freeware apps. Then it gives you a tiny exe that installs all the applications you chose (and nothing else) with one click. No questions, no toolbars no 'bonus' apps. It is one of the few places left where you can get freeware without junk.
Doing a Maximum "horizontally" as opposed to "vertically"
Most of the time when you see the Maximum () function in a formula, it is to calculate the highest value in a column. It could look like this:
Maximum ( {Svc.LabDate} )
which would calculate the latest lab date in the entire column. Or it could look like this:
Maximum ( {Svc.LabDate} , {Patient.ID) )
which would calulate the latest lab date for each patient's group of records.
In both cases the Maximum is looking up and down a single column. But there is also a way to use the Maximum() function (and other summary functions) looking across several values in the same row. For example, say that your table had 4 different date fields and you wanted to find the latest date of each row. You could write a formula like this:
Maximum([{Svc.LabDate},{Svc.PTDate},{Svc.OTDate},{Svc.SurgeryDate}])
This would return the date value that was the greatest (latest) out of the 4 fields. Notice that you need both square brackets around the list of dates (the 'array') and then you need parentheses around the square brackets for the function to work. This syntax also works for Minimum, Count, Average, Sum, and several other summary functions. To see the complete list of functions that can be used this way, go into the formula editor and open the function list node labeled ARRAY.
One other note. When you write a formula that use a vertical summary function you can no longer total that field with a vertical summary operation. But if your formula is doing a horizontal summary like the last example above, that formula can still be used in a summary operation.
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.
25% off Find it EZ products
SAP recently selected Find it EZ products for their first cloud platform partner spotlight. To celebrate, Find it EZ is taking 25% off the price of any new license purchase for Code Search Pro or Dev Surge 365 Enterprise. The offer is currently till October 30.
Here are links to the two product links, but they only work if you are signed in to an SAP account:
Code Search Pro (Desktop or Server editions)
Dev Surge 365 Enterprise
Also note these products may be sold as purchases or subscriptions. For a regular sale the discount is off the purchase price. For subscriptions the discount is on the first year's license.
Free software for the Houston area
From the R-Tag web site:
"R-Tag is providing free licenses to companies in the Houston area. The offer is valid until the end of September 2017.
The eligible products are: R-Tag Documentation and Search and R-Tag Crystal Reports Data Source updater.
We already have a free Crystal reports viewer and scheduler (R-Tag Community edition), which might be useful too.
R-Tag Documentation and Search and R-Tag Crystal Reports Data Source updater are useful with Crystal reports migration, backup processing and report development and we hope they might be helpful to companies in the Houston area with on-site servers."
If you are in the Houston area and want to take advantage of this offer, please see the R-Tag contact page.
Open enrollment Crystal classes in Frederick, MD
Stop struggling with Crystal Reports and learn how to use it fully. Come join me in one of my public classes and learn how to make Crystal work for you. My Intro class makes sure you know all of the basics. We even include material on cross-tabs, charts and formulas. The Advanced class shows you how to solve reporting problems with running totals, subreports, parameters and commands. The material is good for any version. See my web site for course outlines.
So what makes my classes different? I have written my own course materials and have used them to teach over 2,500 satisfied students. And, I give you a toll-free number so you can call me after class with questions at no charge.
Or, if you want to schedule a class at your office, using your data, that is my specialty. I have found some stellar instructors in the the US and Canada that deliver my class for a very competitive price. They are based near several major cities and are willing to travel. Call for details.
Set Location in a Progress database
I have written before about database connections that require the table alias to be different than the table name. Often this is accomplished by adding a 1 to the original table name. This came up again recently with a customer using a Progress database.
We were doing a "Set Location" for a connection that covered a handful of tables. Most of the tables were moved to the new connection but one table didn't move. We got the warning "some tables could not be found" in the new connection. When I have seen this before it usually means setting the location of that one table directly to the corresponding table in the new connection. But for some reason, even though we could see the table in the new connection the update would fail as if the table did not exist. We could even add the table to the report from the new connection, but not set the location to it.
However, I noticed that when I added that table to the report the alias had a 1 added on the end. So as a long shot I changed the alias of the problem table, adding the 1, and then tried the update. This time the update went through. I couldn't see any reason why this one table would behave differently but apparently this table needed the 1 added before the set location would work. One of the quirks of Progress.
Additional parameter values are needed ….
If you refresh certain reports and then try to save the report with data you may get the following error:
"Additional parameter values are needed before this
report can be saved or viewed with data…."
The error seems to occur only in certain versions of Crystal. If you see this message and want to save the report with data Crystal will make you choose parameter values again, which means running the report an additional time. With some reports this is a significant waste of time.
I saw this recently with a customer who said it was worth a bit of research if we could prevent the error. The answer I found did solve the problem for that specific report so I thought it was worth sharing. It was caused by a date parameter set to "allow range values". When we replaced the single "range" parameter with two single-value date parameters ("Begin Date" and "End Date") the problem went a way. To confirm this I changed the parameter back to a range and the problem reappeared.
This might not work for every situation where this error appears, but it is certainly worth a try if you see this message.
Gems from the Archives:
ToNumber() vs Val() (2007)
Using table inflation to generate multiple labels (2007)
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 2017 by Ken Hamady
All rights reserved - Republishing this material requires written permission