The Crystal Reports Underground News Volume 2014.03
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for March 2014:
** Comparison of desktop-based schedulers (2014 update)
** R-tag Report Manager for FREE
** My library of Crystal Reports materials
** A single prompt for selecting a specific month
** New auto-refresh feature in DataLink Viewer
** Let me create your Crystal Reports
** Applying the same format to many fields
** Dashes and strange sorting
** Open enrollment Crystal classes in Frederick, MD
** Changing page numbers to letters
** CR versions over time 2014
Gems from the Archives
** Displaying Range Parameters with "No Lower/Upper Bound" (Volume 2002.11)
** Currency Symbol Codes (Volume 2002.12)
Comparison of desktop-based
schedulers (2014 update)
How would you like your reports to be automatically run, exported to a PDF and
delivered to your Email InBox every Monday morning at 6am? The Crystal Reports
designer doesn’t provide a way to do this (unless you upgrade to CR Server or BO
Enterprise). But if you look at third party products like those on my
LINKS
page you will find several reasonably priced or free tools that do this. Some do
even more. So every March I go through the list and publish a
feature comparison on my blog.
There are 10 products again this year. The page linked above provides a brief description of each product and lists the features that set it apart. Then there is a detailed feature matrix that shows the key specifics for comparison, including prices and the install base. To clarify the matrix terms I have written a feature glossary to explain what each feature means. Finally there are links to the vendor websites so that you can get more information on each product.
In May I will be updating a separate article that compares server based scheduling tools. If you think one person can manage all of your scheduling you are probably fine with one of the desktop tools, regardless of the number of people receiving the scheduled output. But if you plan to have multiple people scheduling reports then you may want to consider a server based tool.
R-tag Report Manager for FREE
R-Tag has just released a
"Community Edition" of their Report Manager
… and made it FREE. This free edition has all the same features as the previous
paid edition except for one, the ability to run SSRS and SQL Reports. But
it can certainly run and schedule all of your Crystal Reports. It uses the
latest runtime engine and it can serve as both a viewer and a scheduler.
Of course the free product doesn't come with the support that you would get if
you purchased the Pro Edition ($749). And, if you look at the comparison
on the Community Edition page you will see that there are a few special features
that you only get with the Pro Edition. But, the Community Edition still
represents a powerful product with an impressive set of features – more than
enough for most users. Just look at the R-Tag column in
my feature grid (PDF) and you
will see that their feature list is at the high end. And it is certainly hard to
argue with free.
If you try it and want to share your feedback, please drop me a note.
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 single prompt for selecting a
specific month
A user wanted some help improving a report parameter. They want the user to
select a specific month using a single prompt. Currently they have to maintain a
list of values that looks like this:
2013 Dec
2014 Jan
2014 Feb
2014 Mar
etc.
This list not only has to be maintained each year, but it has to be converted
from a string into a date range to be used as criteria. I suggested that instead
they use a regular DATE parameter. I would then tell the users to pick the last
date of the target month. They could actually pick ANY date in the month and the
method works the same.
The method uses two formulas that calculate the first and last day of the month
in the date prompt. Then these two formulas go into your selection formula. So,
it is only one parameter, and one that is fairly intuitive. And because the
calendar is automatically populated you don't have anything to maintain.
Here are the two formulas (note that the second refers to the first):
//First Day
{?DateParam} – Day ({?DateParam} ) +1
//Last Day
Date (DateAdd ('m', 1, {@First Day} )) -1
Then your selection formula could be:
{Your.DateField} in {@First Day} to {@Last Day}
New auto-refresh feature in
DataLink Viewer
There are several viewers out there that allow you to keep a report open on the
screen and have the report automatically refreshed every few minutes. Some users
will even display these reports on a large screen to allow everyone to monitor
key metrics in real time.
DataLink Viewer from Millet Software is one viewer that supports auto-refresh.
Ido Millet has recently added a feature that speeds up the process for one of my
clients. The client wanted to auto-refresh the same report in several different
locations. But the report takes a long time to run and taxes the server. We
didn't want every location to run the same report continuously. So now DataLink
Viewer has an option to automatically reload a report that has saved data,
instead of refreshing it. My customer uses a scheduling tool to run the report
once every 15 minutes and export it to RPT format. This type of export saves the
report with data. Then each instance of DataLink Viewer can reload the exported
RPT and see the most recent export. This allows any number of viewers to display
timely data while only the scheduled instance hits the database.
For more information on viewers, auto-refresh or DataLink Viewer, please see the
LINKS page on my site or my
review of Crystal Reports Viewers:
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.
Applying the same format to many
fields
A user recently asked about setting the format of a large number of fields. He
wanted to have all the negative numbers show in red and positive numbers show in
black. The formula wasn't a problem, but he was looking for a way to avoid
writing a unique formula for each field.
I suggested that he use this as the font formula for one of the fields:
if
CurrentFieldValue < 0
then CRRed
else CrBlack
Then all he would have to do is use the format painter (paintbrush icon) to paint this format to all the other formulas that need to be changed. Since the formulas refer to the CurrentFieldValue the formula is exactly the same for every field, including formulas and subtotals. And to speed it up even more you can double click on the paintbrush to make it 'sticky'. Then you can single click every field that needs that same format.
Dashes and strange sorting
I noticed something odd the other day when sorting/grouping a character column.
The column was all date values stored as strings in the format Year-Month-Day.
When I looked at the data I noticed that some values had dashes and some did
not, like this:
2013-10-30
20131031
20131101
2013-11-02
20131103
2013-11-04
20131105
20131106
What surprised me was that they were still sorting in chronological order, as if
the dashes didn't exist. I had never seen that before. Since I like my sorts to
be consistent and predictable I did some quick testing to help me understand
what was happening.
I discovered that if you sort or group on a database field – and even on some
simpler formulas – the database driver will add an "ORDER BY" to the SQL query.
This means the database will do the sorting on the server, and many databases
ignore dashes when they sort columns. This has something to do with Unicode
"word sort" rules, but the main point is that it ignores dashes.
When you sort or group on most formulas fields the database driver does NOT add
an "ORDER BY". In those cases CR does the sort locally. So I wrote a formula
like this and used it as a sort:
Left ({Table.Field} , 12)
I have found that formulas that use the LEFT() or RIGHT() function never pass
any logic to the SQL. The value looks identical to the original field, but my
sort order was now like this with dashes sorting ahead of numbers:
2013-10-30
2013-11-02
2013-11-04
20131031
20131101
20131103
20131105
20131106
No, this isn't chronological but it is what I would expect when the data is not
formatted consistently. But there was still one last surprise. When the dashes
were between alpha characters even the local sort in CR would ignore the dashes.
So a local sort recognizes dashes between numbers but not between alpha
characters. My formula using the LEFT() function wouldn't help with alpha
characters. I got a sort like this:
2013-10-30
2013-11-02
2013-11-04
20131031
20131101
20131103
20131105
20131106
ABA
AB-B
ABCD
AB-Q
So if you ever need a sort that always recognizes the dashes you will have to
use the REPLACE() function to replace dashes with another character that
has a similar ASCII value. For example, replacing all dashes with plus signs [+]
makes it sort consistently. The formula would look like this:
Replace ( {Table1.Field1} ,'-','+')
You would still display the original
field but use the formula as the sort field. The sort would end up in this
order:
2013-10-30
2013-11-02
2013-11-04
20131031
20131101
20131103
20131105
20131106
AB-BB
AB-QB
ABABA
ABCDE
And, if anyone has any more information about sort anomalies related to Unicode,
drop me a line.
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 May 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.
Changing page numbers to letters
I was in a forum and someone asked if there were a way to change numbers into
letters (1=A, 2=B, etc). I had recently done something like this for a
customer and was surprised that I had not posted the formula here. You can
apply this formula to any number series that doesn't go over 26. This
first version checks and prints a blank for numbers above 26.
if pagenumber in 1 to 26
then CHR (pagenumber + 96)
else "
The number 96 in the second line gives
you lower case letters. Use 64 instead if you want upper case letters.
One special thing my customer needed was to have the the letters start over
after 26, so that 27 was another 'A' and 28 was another 'B', etc. To do
this we used a remainder:
Local NumberVar x:= remainder (pagenumber- 1 , 26) + 1 ;
CHR (x + 96)
You might wonder what the +1/-1 is for in this formula. This is the simplest way I found to get the exact multiples of 26 to show as 26 instead of zero.
CR versions over time 2014
I have posted my annual chart showing the versions
of CR
that my newsletter subscribers were using when they signed up. It gives you a
sense for which versions are being used and how quickly the new versions are
catching on. I have included numbers for 2014 even though the sample size
is relatively small.
The slight jump in the use of version 10 is probably a fluke that will diminish
as more data for the year accumulates. You will see a similar change if
you compare the bar for 2013 in this chart to
last year's chart.
Gems from the Archives
**
Displaying Range Parameters with "No Lower/Upper Bound"
(Volume 2002.11)
**
Currency Symbol Codes (Volume
2002.12)
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