The Crystal Reports Underground News Volume 2014.05
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for May 2014:
** Server-based scheduler comparison (2014)
** A calendar for multi-day events
** My library of Crystal Reports materials
** A Daily Appointment Calendar
** Options for displaying zero values
** Let me create your Crystal Reports
** "Saved Data" Select Expert/Formula
** R-tag introducing a lower priced scheduler edition
** Open enrollment Crystal classes in Frederick, MD
** Searching for SAP support articles
** Sage/PeachTree drops CR, adds a new BI tool.
Gems from the Archives
** Preventing Unwanted Drill-Down (Volume 2003.04)
** Section Background Color that fades gradually (Volume 2003.04)
Server-based
scheduler comparison (2014)
I have just updated my
comparison of server-based scheduling tools
for 2014. These tools are similar to the desktop-based scheduling tools I write
about every March, but these are designed to be run on server. This allows
multiple people to schedule reports for automated delivery by Email, FTP or
network folder.
There are 8 products on the list this year with one new release and another
being discontinued. There are also a few feature updates and price changes
since last year. The blog page provides a brief overview of each product. It
also has a link to the feature matrix that compares roughly 70 features of these
tools. There is even a feature glossary that defines all the terms. So if you
need a short course in automating Crystal Reports delivery, this is a pretty
good place to start.
A calendar for
multi-day events
Creating a calendar in Crystal poses some special challenges. There may not be
an event for every day or every week, so a method is needed for filling in the
gaps. And there may be a long list of events on any given day which means
finding a way to stretch the boxes. But a recent request form a customer looked
simply impossible in CR. They showed me a calendar for multi-day events that
they were creating in a scheduling application. It looked like the
(intentionally blurry) image below.
My first reaction was that this couldn't be done in CR. There were 6
key challenges:
1) The colored box for each event stretches from the event start date to the
event end date.
2) Events that cross weeks need a box for each week with the same text repeated
in each box.
3) The box must grow vertically to print all the text, which means short events
will have taller boxes.
4) Events in the same week should not overlap, although they should share the
same row when they both fit.
5) The box color is defined by the type of event and can be specified by the
user.
6) Part of the text in the box needs to be in bold.
But the customer was willing to invest some time to see how close I could come.
After a few hours, a couple of arrays, some looping logic, a quick review of
twips, several fun date formulas and a cross-join …. I had solved all 6
problems. I have posted a
sample page of my output (PDF) to show how it looks.
Once I had it working I
decided to invest some extra hours of my own time to polish the code and comment
the formulas. I converted the direct field references into
feeder formulas
which makes it relatively simple to deploy the report in another environment. I
have even written a few pages of documentation to describe the flow of the
formulas and make the report educational.
My plan is to give a copy of the report and documentation to those willing to
pay for one of the hours that I spent doing the extra development. So if
you are interested in deploying this, or just having a copy to see how it is
done, let me know. See the article below for an adapted version that
provides a more traditional calendar, using the same approach.
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.
A Daily Appointment
Calendar
One of my customers had adapted a report template that they found online to
generate a calendar report. But they weren't happy with the reports
performance. When I looked at it I found that the template ran a subreport
for every day so there wasn't much I could do to speed it up. They allowed me to
try a different approach to creating the calendar, the approach I used for the
calendar descibed above. I wanted to see if I could adapt that report to
create the more traditional daily appointment style calendar. That would
eliminate the subreports. It would also eliminate the need for a table
containing all dates, and the needed time to maintain that table.
I was able to eliminate the "Dates" table by using a 15-record inflation table
with records numbered 1 to 15. This allows the report to project each
record into the next 15 weeks. As long as you have at last one record
every 15 weeks you get a complete calendar. The number 15 is arbitrary and
can be adjusted up or down depending on how large of a gap you expect to
encounter in your data. If you are sure to have at least one event every week
you don't need the inflation table at all.
And I did find a way to eliminate the subreports by using variables. My
sample output is from the Xtreme database. To see a full month as a PDF
you can open the
sample output file.
So if you think calendar reports like these would be useful in your environment,
let me know.
Options for
displaying zero values
Hidden in the advanced formatting options for numbers are several ways of
displaying zero values on your reports. To find these options, right click
on a numeric field and select "Format Field". Then click on the "Number" tab and
finally click on the "Customize" button at the bottom of that screen.
One option is to suppress the zeros completely so they show up as blanks.
To do this there is a checkmark in the upper left.
Another option is to display something specific instead of zeros and this is
found in the lower right of the same screen. You will see a drop-down
under the heading "Show zero values as". Here you can keep the default
behavior or use another value, like a dash or a zero. I wondered what the
difference was between Default and the zero and it took some testing to figure
it out. The default might show decimals, like 0.00 or 0.0000 but if you set the
default to zero the decimals won't show.
But the real secret of this last setting is that it is one of the only drop-down
lists in CR where you can type your own value. In other words you can type
whatever you want to show as the zero value, like "n/a", or "Pending".
This is similar to using a "Display String" (on the common tab) but without the
need to write a formula.
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.
"Saved Data" Select
Expert/Formula
In the current versions of CR, you have 3 choices for the select expert and the
selection formula. The main one is Record Selection which is the one you
use for database fields and most formula fields. Then there is Group
Selection which is used for subtotals and other summary fields. But the
third an most recent addition is the one for "Saved Data". I have ignored
this feature since it was introduced, mainly because the explanations I read
didn't make any sense to me. The idea is that you can put rules here when you
only want the rule to apply to saved data. But if you have saved data you can
put the same rule in the regular record selection formula and just tell the
report to use saved data, so it seemed to simply confuse things. It might make
sense for those using the SAP viewer where you are limited to saved data. This
would allow you to deploy parameters that don't ask the user about refreshing
the data. But since my customers typically use viewers that CAN refresh
data this doesn't come up.
But after a customer asked me about this feature I gave it some more thought.
I realized that a better way of saying this is that it is a rule that is applied
locally, meaning that it doesn't get added to the WHERE clause of the SQL query.
No one has said it that way but I just tested it and that is exactly what
happens. This opens up several useful avenues.
1) I have seen cases where the selection criteria creates a WHERE clause that is
misinterpreted by the database. It happens when the fields in the selection
formula do not match the indexing scheme in the database. (This only happens in
a few less commonly used databases). In those cases I would normally have to
write a formula to use for selection, which can prevent the report from passing
that rule to the SQL. Now I see that I could move those rules to the "Saved
Data" formula and accomplish the same thing.
2) Another potential use is when the report I create is run from within an
application. Some applications generate their own selection formula based on
choices that the user makes in the application screens. I sometimes find that
the rules I add to the selection formula are overwritten by the application's
selection formula. This provides another workaround for that problem.
The only downside is that because the "Saved Data" selection formula is rarely
used, any rules put there might be forgotten, causing some extra
troubleshooting. So that means one more place to check when a report isn't
returning the records expected.
R-tag introducing a lower priced scheduler edition
R-tag is introducing a new price point for their desktop scheduler, the Crystal Scheduler Edition at $299. They are still offering their free "community edition" without any support, but those that need support will now have less of a price hurdle when upgrading to the supported product. The $299 edition will only support Crystal Reports. If you want support for SSRS, ad-hoc queries and dashboards you will need to upgrade to the Pro Edition at $749. See my updated comparison for more information on desktop schedulers.
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 August 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.
Searching for SAP
support articles
My blog automatically checks for links that no longer work. It recently found a
batch of broken links for old Crystal support articles. These were hosted on a
Kanisa support site which is gone. Now they have (mostly) been incorporated into
the SAP support engine. If you ever need to search SAP for support content and
KB articles you should now use the following link:
http://search.sap.com/ui/
Sage/PeachTree
drops CR, adds a new BI tool
PeachTree Accounting, now known as Sage Accounting, has a long history of using
CR as a reporting engine for custom reports. Sage currently comes with
some standard Crystal Reports already written for you, and makes them available
directly from the Sage menu. But according to an Email forwarded to me by
one of my customers, this is about to change. Sage 50 2015 will no longer
come with a version of Crystal reports. Instead Sage is providing an Excel based
reporting tool called
Sage 50 Business Intelligence. You will still be able to use
Crystal if you purchase it yourself, or if you already have a copy from previous
version of Sage. You can read the details in this
CBA Blog post.
One concern was that Sage would stop supporting their custom functions.
These are necessary for things like Quantity on Hand and Cost of Goods. I have
written about
some of these in the past. But Dave Smith at Phase One Computing Services
was able to confirm that Sage is going to continue to provide an option to
install these functions for users who still want to use Crystal. See the
screenshot in my blog from the 2015 version of Sage.
Gems from the
Archives:
Preventing
Unwanted Drill-Down (Volume 2003.04)
Section
Background Color that fades gradually (Volume 2003.04)
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 2014 by Ken Hamady
All rights reserved - Republishing this material requires written permission