The Crystal Reports Underground News - Volume 2015.01
an
independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for January 2015:
** Web based deployment options compared (2015)
** Is Crystal Reports in decline?
** My library of Crystal Reports materials
** Printing single-character fractions
** Eliminate zero bars in a bar chart
** Let me create your Crystal Reports
** IE security setting breaks parameters
** You should activate the AutoSave feature
** Open enrollment Crystal classes in Frederick, MD
** The ) is missing ?!?
** Group tree takes you to the wrong page
Gems from the Archives:
** Doing a
SELECT in a SQL Expression field:(Volume 2003.12)
** Vertical
Height of a box gets locked (Volume 2004.03)
Web based deployment options compared (2015)
There are many ways to deploy Crystal Reports to users. I normally lean toward
the simpler and less expensive options, like locally installed viewers, or
scheduled delivery of PDF output. But there are environments where a web based
option is necessary. The "official" options from SAP are Crystal (Reports)
Server and BO Enterprise. But there are other, less expensive products out there
that many users never see. These are third party products that allow your users
to view reports from a browser. You can also centrally manage your report
deployment from a browser.
I have created a
page on my blog that lists and compares these products, and I update it
every January. This year the list includes 9 products, including CR Server,
itself:
Crystal Reports Server – a traditional Web portal
Report Runner Web Portal – a traditional Web portal
CSS Portal with CRD – a traditional Web portal
Visual Access Report Server – a traditional Web portal
Ripplestone– a traditional Web portal
RVweb – a traditional Web portal
rePORTAL – a traditional Web portal
RV for Windows Pro – a server-based viewer
Report Launch – a bridge between BO server products and server based
applications
The blog page mentioned above contains a brief rundown on what each product does
and provides links to all of the product web sites. I have also posted a
feature matrix (PDF) that shows some of the specifics for comparison,
including prices. If you have any feedback to share on these tools I would be
happy to hear form you.
Is Crystal Reports in decline?
Crystal Reports has been around a long time. I have worked with it for 20 years
and it was around before that. That is an incredible run for a piece of
software. Recently I have received questions that are variations on the same
theme: How strong is CR's presence in the market. Here are the main variations:
Is it still worthwhile to invest time in learning Crystal?
What are the job prospects for full-time CR developers?
Can I make money as a CR consultant?
I am working on a software project, should I consider an
alternative for reporting?
What does SAP have planned for Crystal Reports.
Crystal Reports is still widely used, and I am still busy, but the user base
seems to have stopped growing. I have seen the curve sloping down in three
areas:
1) Newsletter subscriptions
2) Sales of my e-books
3) On-site and public class requests
Based on my data, Crystal Reports hit a peak around 2008 and then started a slow
decline. This may be in part because of the SAP focus on Enterprise customers or
because that was the last version with significant new features. But despite
that, there are two things that will keep Crystal Reports going for years.
First is customer loyalty. When I started using Crystal Reports in 1995 I was
already an expert in a similar tool called R&R Report Writer. In 1996 Crystal
Reports v5 came out and my requests for Crystal sloped up while requests for R&R
sloped down. Within a few years Crystal had completely taken over my business.
But R&R did not go away. The customers were so loyal that another R&R consultant
bought the rights to the product and kept it going. R&R is still out there
today, nearly 20 years later, and still has a small but loyal following. Now if
a tool like R&R can stay alive for 20 years after it was surpassed, Crystal
should solid for the foreseeable future. The user base for CR is much bigger
than R&R ever was. And CR has yet to be surpassed, which brings me to number
two.
The second thing that will keep CR alive is its power. Crystal has the ability
to tap directly into all types of data, it has a rich formula language, and it
has incredibly flexible presentation options. Crystal has also grown a broad
ecosystem of independent products that can enhance its power. Crystal can't do
everything, but it is a rare day when I have to tell a customer that I can't
find a way to meet their output or delivery requirements. I keep looking at
newer tools and nothing out there is as flexible and comprehensive as Crystal
Reports.
So on the one hand, the current decline would make it difficult to launch a new
Crystal Reports business. On the other hand, I expect to be doing Crystal
Reports for another 20 years.
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 ($36)
Expert's Guide to Subreports, Parameters and Alerts ($28)
Expert's Guide to SQL Expressions, Options and Commands ($26)
Expert's Guide to Totals ($24)
Expert's Guide to Cross-Tabs ($22)
Expert Techniques Vol. 1 - 4 ($19 each)
Quick Reference to Crystal Reports in Visual Basic ($16)
Quick Reference to Crystal Reports in .NET ($14)
You will find these on the LIBRARY page of my site.
Printing single-character fractions
A few years a back I posted a formula for
converting a decimal value into a fraction. But the output used full size
characters combined with a slash. Recently a customer wanted me to shrink the
fraction so it would look like a true fraction.
At first I started looking for super script and subscript characters. Then I
found that several fonts have several common fractions as a single character.
For instance the Unicode value CHRW(8541) is the character for the fraction
symbol "5/8" in several fonts. I found single-character symbols for the 15 most
common fractions – those that have a denominator of 2,3,4,5,6 or 8. The formula
below will tap into these characters for the corresponding decimal values. I
then extended it to include the eight odd numerators over 16. These I created
with other special characters that seem to be designed for creating fractions.
But there are several things you have to keep in mind with this technique:
1) Rounding
You have to decide how precise the decimal match has to be. For instance the
formula will always print 5/8 if the decimal is .625 but what if it is close,
like .62485? I set the rounding in this example to 3 decimals but you can adjust
that by changing the 'D' variable in the first line. This number should match
the the number of decimals in the field you are converting. You may even need to
round the incoming value before using that in this formula. Including more
decimal places requires a more precise match.
2) Other fractions
What should the formula do if the decimal value doesn't match one of the 24
fractions in the formula? If that would be considered a mistake in the data then
you can print an error message after the final 'else'. If that would be rare but
legitimate you might want to include the original (full size) fraction formula
in the report. You can then refer to the full size formula in the final else,
making it the last resort.
3) Font
Don't forget that you have to format this field with one of the fonts that
support these symbols. The ones that I found to work the best in my environment
are Calibri, Cambria, Khmer UI, Segoe UI, MS Yahei and Meiryo. You can test
others and see how they look if you don't have these.
Local NumberVar D := 3; //Number of decimals
to round before comparing
if {@input} = 0 then '' else
if {@input} = round (1/4 , D) then CHRW(188)
else
if {@input} = round (1/2 , D) then CHRW(189)
else
if {@input} = round (3/4 , D) then CHRW(190)
else
if {@input} = round (1/8 , D) then
CHRW(8539) else
if {@input} = round (3/8 , D) then
CHRW(8540) else
if {@input} = round (5/8 , D) then
CHRW(8541) else
if {@input} = round (7/8 , D) then
CHRW(8542) else
if {@input} = round (1/5 , D) then
CHRW(8533) else
if {@input} = round (2/5 , D) then
CHRW(8534) else
if {@input} = round (3/5 , D) then
CHRW(8535) else
if {@input} = round (4/5 , D) then
CHRW(8536) else
if {@input} = round (1/3 , D) then
CHRW(8531) else
if {@input} = round (2/3 , D) then
CHRW(8532) else
if {@input} = round (1/6 , D) then
CHRW(8537) else
if {@input} = round (5/6 , D) then
CHRW(8538) else
if {@input} = round ( 1/16 , D) then
CHRW(185) & CHRW(8725)& CHRW(8321)&
CHRW(8326) else
if {@input} = round ( 3/16 , D) then
CHRW(179) & CHRW(8725)& CHRW(8321)&
CHRW(8326) else
if {@input} = round ( 5/16 , D) then
CHRW(8309)& CHRW(8725)& CHRW(8321)&
CHRW(8326) else
if {@input} = round ( 7/16 , D) then
CHRW(8311)& CHRW(8725)& CHRW(8321)&
CHRW(8326) else
if {@input} = round ( 9/16 , D) then
CHRW(8313)& CHRW(8725)& CHRW(8321)&
CHRW(8326) else
if {@input} = round (11/16 , D) then
CHRW(185) & CHRW(185) & CHRW(8725)&
CHRW(8321)& CHRW(8326) else
if {@input} = round (13/16 , D) then
CHRW(185) & CHRW(179) & CHRW(8725)&
CHRW(8321)& CHRW(8326) else
if {@input} = round (15/16 , D) then
CHRW(185) & CHRW(8309)& CHRW(8725)&
CHRW(8321)& CHRW(8326) else
//This refers to a separate formula for full
size fractions.
{@fraction full size}
// Other optional values:
//"Other Fraction"
//Totext ({@input},4)
Eliminate zero bars in a bar chart
What if you have a bar chart and some of the bars are at zero? Maybe that
information is useful but sometimes you may want those zero bars to be skipped.
One method is to exclude the zero records, but what if they are needed by other
parts of the report? I just found a relatively easy way to exclude zero bars
from a chart, without eliminating the zero records from the report.
For example, say you are doing an "advanced" chart (not a group chart). You want
one bar per customer so the Customer field is the "On change of" field.
The field {@FedEx Counter} is the "show values" field and it is set to sum.
You preview and you see several customers have no FedEx orders so their name
shows an empty slot where their bar should be. Here are the steps to eliminate
those:
1) Go into the Chart Expert and select the Data Tab.
2) Add the {@FedEx Counter} to the 'on change of' box as a second field.
3) Click the "order" button right below {@FedEx Counter}.
4) Change "in ascending order" to be "in Specified Order".
5) On the specified order tab click "New" and type 'zero' as the group name.
6) Below {@FedEx Counter} select "is greater than" 0.
7) Click on the "Others" tab and select "Discard all others".
8) Click OK > OK to get back out.
You should see all the zero bars disappear.
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.
IE
security setting breaks parameters
Are you having trouble with your parameter windows in Crystal Reports? Did your
calendar control stop working? Are the "OK" and "Cancel" buttons not responding?
I just got a note from Adam Butt of APB Reports in Trondheim, Norway.
Apparently he was experiencing these problems and worked for hours to try and
solve it. He went as far as uninstalling and reinstalling his entire CR product
suite, including several versions. Only when that didn't work did he realize
that he had recently changed his Internet Explorer Security setting to "High".
Changing it back to "Medium High" resolved the problem. I will have to do some
testing to see which of the underlying IE security settings breaks CR
parameters. But I am posting this now for others who might run into this
problem, or a similar problem, with parameters.
You should activate the AutoSave feature
One of my favorite sayings:
Good judgement comes from experience.
And experience comes from bad judgement.
I was
reminded of the value of AutoSave this week when one of my reports froze up on
me before I had a chance to save a large number of changes. I waited for it to
come back and even went to lunch. But an hour later the report was still frozen.
Normally I am pretty good about saving my work often, but I had been
concentrating on a problem and lost track of time.
The machine I was working on did not have the CR AutoSave feature activated. But
somehow the report that froze did not freeze the rest of Crystal. I was
able to use the menu and save my other reports, but I could not save the report
I had been working on.
Since the menu was still working I took a long shot and activated the AutoSave
feature. After a few minutes I noticed a new RPT file in the temp directory and
it had all of my most recent changes. So even though it would not let me save
the "frozen" report, CR was able to AutoSave it for me.
And as if that wasn't a strong enough message, I had three client workstations
crash on me this past week. None of them had AutoSave activated. So allow me to
make a public service announcement: Read my prior post on
how to use the AutoSave feature and then activate it in your environment.
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 January 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 and dates.
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
Spokane/Seattle, LA, Omaha, Detroit, Tampa, Philadelphia, New York City, Boston,
Vancouver and Montreal. Call for details.
Group tree takes you to the wrong page
I recently had a report grouped by Employee with one page per employee. Each
Employee showed up in the group tree on the left of the screen. But every time
we clicked on an employee in the group tree, we ended up on the page of the
employee just BEFORE the one we wanted. I thought it was a glitch to I changed
the group options, then closed the report and eventually closed CR completely.
The problem didn't go away so I realized it was something in the report.
After some experiments I realized what was going on. The report was printing one
page per employee and printing only the group footer. But there was no actual
"new page after" checked for the group footer. There was one employee per page
only because the group footer was so large that only one group footer could fit
on a page. So it mimicked a page break.
But the group tree doesn't take you to the group footer of the group, it takes
you to the Group Header. And in this report the Group Header for each employee
was suppressed. So as soon as each Group Footer finished on a page, the next
Group Header would start right below it. And because it was suppressed, it would
always fit on the prior page.
So the Group Tree took us to this (invisible) Group Header which was always on
the page before the corresponding group footer. All we had to do to solve the
problem was check "new page after" for the Group Footer and that moved the
(invisible) Group Header to the next page, right above the corresponding Group
Footer. And then the Group Tree worked as expected.
The ) is missing ?!?
If you make a mistake in a Crystal Reports formula, the error messages are
usually pretty helpful. But every once in a while the error can send you on a
wild goose chase. The message above is a good example. It will appear when you
have an opening paren without a closing paren. But it will also appear in the
following formula, even when the parens match:
WhilePrintingRecords;
NumberVar Save01;
NumberVar Save02;
NumberVar Save03;
if {@criteria}
then (Save1 := 0; Save02 := 0; Save03 := 0)
Now in
this formula it is obvious that the parens are not the problem. So why would the
error message mention a missing paren? This happens only when you have a
specific combination of factors – a bad variable name within a pair of parens.
If you misspell a variable name that is NOT within parens, you will get an error
message that says "the following text does not appear to be part of the
formula". If you misspell a field name (with or without parens) you
would get an error about the field name. Most syntax errors generate the same
syntax warning inside or outside of parens. But if you misspell a variable AND
it happens within a pair of parens, then CR warns you about a missing paren even
if it is NOT missing.
In a simple formulas like the one above it doesn't take long to realize that the
error message is a red herring. But with a more complex formula you might spend
quite a bit of time checking parens before you realize that the problem lies
elsewhere. I know from experience. This happened to me twice in one
week with two different customers, which is why noticed the behavior.
The good news is that when the error pops up there is a clue that tells you if
the problem is really a missing paren or a bad variable. When the problem is
really a bad variable name then the cursor will move to highlight the name of
the problem variable. When the problem is a missing paren then CR will not move
the cursor.
Gems from the Archives:
** Doing a
SELECT in a SQL Expression field:(Volume 2003.12)
** Vertical
Height of a box gets locked (Volume 2004.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 2015 by Ken Hamady
All rights reserved - Republishing this material requires written permission