The Crystal Reports Underground News - Volume 2019.09
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for September 2019:
** Updated comparison of Crystal Reports viewers (2019)
** Calculating the begin and end of Daylight Saving Time
** My library of Crystal Reports materials
** Suppress column headings when there are no details on the last page
** How to turn records/rows into columns
** Let me create your Crystal Reports
** When that zero isn't really a zero
** Real rounding vs fake rounding
** Individual Training with an expert.
** Column headings scrambled in Excel export
** Math tweet splits internet mathematicians
Gems from the Archives:
Date parameters in a Command with DateTime fields (2009.09)
On-Demand Subreports that use saved data (no refresh needed) (2009.05)
Updated comparison of Crystal Reports viewers (2019)
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 SW
If you have feedback to provide on any of these products, I would love to know what you think.
Calculating the begin and end of Daylight Saving Time
I am not a big fan of Daylight Saving Time (DST). I even hear some states and countries are talking about dropping it (yeah!). But in the meantime there are plenty of reports that need to adjust the time twice a year. This usually happens when the datetime values are stored in Greenwich Mean Time (GMT) and have to be converted to a local time. Then you need to know when DST begins and ends.
The first two formulas below calculate the beginning and ending dates of DST, based on the year of your transaction date. The third formula uses the first two formulas to make the one-hour adjustment. Substitute your GMT date fields into the first and third formula.
//DST Start
DateVar Start:= Date (Year ({@YourDateTimeGMT}) , 3, 15);
DateVar BOM:= Start - Day(Start)+7;
DateVar BOW:= BOM - DayOfWeek(BOM) + 8;
DateAdd('h', 2, BOW);
//DST End
{@DST Start} + 238 ;
//Adjusted DateTime
if {@YourDateTimeGMT} in {@DST Start} to {@DST End}
then DateAdd('h', 5, {@YourDateTimeGMT})
else DateAdd('h', 4, {@YourDateTimeGMT})
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.
Suppress column headings when there are no details on the last page
Sometimes there are details printing on the last page and sometimes the last page is just the Group or Report Footer. You may not want column headings on the last page if there are no details. I have seen and tried several methods to accomplish this but not all are reliable. For instance using "OnLastRecord" as a suppress condition will work most of the time, but will also suppress the headers when the last page still contains exactly one detail record. I have found the approach below to be the most reliable.
First you split the Details band into A and B subsections. You suppress Details B and use Details A for your visible fields. Then you create the following formula field and place it in Details B:
WhilePrintingRecords;
BooleanVar Ending := OnLastRecord
Last you use the following formula to suppress the Page Header, or the part of the Page Header with column headings:
WhilePrintingRecords;
BooleanVar Ending
A Boolean variable is False by default. The variable will only be True once the report has passed the last Detail A, which means that the last record has printed. If there is a page header after that point it will be suppressed, because the "Ending" variable is now True.
How to turn records/rows into columns
A customer recently requested an unusual report layout. They wanted the field labels in the first column on the page and each record to be a new column on that page. I remembered struggling with a similar layout request years ago and pulled up an old report from 2009. What I came up with then worked fine if you only needed one set of labels on each page, meaning that the columns were so tall that there wasn't room to fit a second set below the first one each page. In 2009 I had half-height columns which meant two sets of labels along the left side of the page. That got very complicated.
Fortunately my current project had full page columns, which meant my 2009 solution was a perfect fit. Here are the steps:
1) Create a static value formula that can be used as a group. I usually use something like this:
WhileReadingRecords; 1
2) Group on this formula, and in the group options check "repeat Group Header on each page"
3) Make the group header about 6 inches deep and type all the field labels along the left side.
4) Make your details section about 6 inches deep and put the corresponding fields in.
5) Go into the section expert for the details section and check "Format with multiple columns".
6) When the layout tab appears set the width of the column to an appropriate number.
7) Check the layout options: "Across then down" and "Format Groups with multiple columns".
Note – you can do something similar with a cross-tab, but the additional pages for cross-tabs are all to the right on page 1 when in preview. And the layout isn't as flexible with a cross-tab.
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.
When that zero isn't really a zero
Last week a customer was really befuddled. He had a formula that said:
if {@field} = 0 then ...
He could see lots of zero values but the formula didn't work as expected. He couldn't figure it out so he sent it to me. The first thing I did was add a few more decimals to see if it was a rounding issue. That didn't show anything, but the formula still insisted that the value was somehow NOT equal to zero.
So I went into the formula and multiplied the current value by one trillion. Then instead of zeros I started to see some small numbers. I am not an expert on floating point values or database precision but I have seen this before in reports. The solution is to round the value in the formula before comparing it to zero. In this case we rounded the value to two decimals like this:
if Round ({field}, 2) = 0 then ....
That made the formula behave as expected. The odd part is that I have seen the same problem with two other customers in the past week. It could be just a coincidence, but I figured I would mention this and see if this is happening to more people.
Real rounding vs fake rounding
Crystal makes it easy to reduce the number of decimals that you display for numeric values. There are two buttons on the toolbar (near the percent sign) and these allow you to either reduce or increase the decimals displayed. When you reduce decimals the remaining digits will either round up or down based on the digits no longer displayed. In other words, if you are showing 75.28 and you click the button to reduce the decimals displayed by one, the value will display as 75.3. This is because the hidden 8 causes the value to round up to the next 1/10th.
However, this is deceptive. The underlying value in the report is still 75.28. And if you were to total that column or use that value in a calculation the value used would still be 75.28. This can give unexpected results, because the visible total at the end of the report might not reflect the sum of the visible values that go into that total. For that reason, I call this 'fake' rounding. You will find the same type of issue in Excel spreadsheets.
Most of the time, this isn't an issue. And many people who read financial statements understand 'rounding errors'. But there are cases where you need 'real' rounding. Real Rounding requires writing a formula that uses the Round() function. For instance, if you have to apply a tax rate to a purchase and the calculated tax amount. You could use a formula like this:
{Sales.Charge} * {Sales.TaxRate}
But if the tax value has more than two decimals, those extra decimals aren't real. Most transactions have to be rounded to the nearest penny. In these cases you would need a formula like this:
Round ( {Sales.Charge} * {Sales.TaxRate} , 2 )
This rounds the tax to the nearest penny. If you used the first formula and created a total of the tax values, the total would include all the fictional fractions of a penny. If everything was displayed with two decimals the total tax would not match the individual tac values, because those would be using fake rounding to display only two decimals. But if you use the second formula and then create a total of that formula the total should reflect the visible values. The total calculation is using values that display the same number of digits as the underlying value.
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.
Column headings scrambled in Excel export
I had a customer recently complain about a report not exporting correctly to Excel (Data Only). The column headings worked correctly in preview but were re-arranged in the spreadsheet in a seemingly random fashion.
My first thought was to make sure that they were all the same width as the field below them and precisely aligned with the fields, but this didn't solve the problem. Then I aligned their bottom edges but again this didn't solve the problem.
Then I noticed that the headings that moved to the right were all text objects that were either two rows or three rows deep, making them taller than the other headings. The tallest headings moved further to the right, which meant that it had something to do with the position of the top edge. So I made all the heading objects the same height and then aligned their top edges. At that poing they all exported to Excel in the correct order.
Note that this only affects Excel exports that are "Data Only". Of course this is the option that I use 99% of the time.
Math tweet splits internet mathematicians
One of my colleagues sent me a New York Times article about a math tweet. The tweet was of a deceptively simple math equation and asked people to calculate the result. All of the internet mathematicians immediately divided into two camps based on their interpretation of the rules of precedence, which were intentionally ambiguous in the equation. The point of the article (and of my colleagues sharing) was the importance of using parentheses to clarify the order in which things should occur. I often add extra parens, even when the order of precedence wouldn't require them, just to give myself a visual clue of what is supposed to happen when.
The funny thing about the original article, and an interesting follow-up article, is that experts can still be found on both sides of the divide. That is somewhat surprising given the apparent simplicity of the problem.
And thanks to Zvi Flanders of Huron Consulting for sending me the link.
Gems from the Archives:
Date parameters in a Command with DateTime fields (2009.09)
On-Demand Subreports that use saved data (no refresh needed) (2009.05)
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 2019 by Ken Hamady
All rights reserved - Republishing this material requires written permission