an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for September 2014:
** Updated comparison of Crystal Reports
viewers (2014)
** Highlighting individual rows and columns in a cross-tab
** My library of Crystal Reports materials
** Reporting on 'multi-value' fields
** Code Search Pro 2014
** Let me create your Crystal Reports
** Eliminating trailing zeros in decimal numbers
** Select records after the most recent business day
** Open enrollment Crystal classes in Frederick, MD
** Finding fields in use outside of formulas
** Prior versions, service packs, runtime and merge modules
Gems from the Archives
When
"Select Distinct" doesn't work (Volume 2003.10)
"Convert Null to Default" is disabled? (Volume 2003.10)
Updated comparison of Crystal Reports viewers (2014)
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. I am amazed at how many Crystal Reports users
are still unaware of these, since they are offered by a dozen different vendors.
Some users get sidetracked by the "viewer" that is put out by SAP because it
won't refresh reports. Don't let that fool you because
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 and the install base. I have even included a glossary
of features in case you aren't familiar with the terminology.
This year there are 11 vendors in the review but one of them is MIA** – the
vendor for EasyView (EasyStreet Software). I have Emailed and called and have
not received a response for 2 years now. They are also not responding to
customers or even the
Better Business Bureau (at least as of September 2014), although the web
site is still running.
The current vendors are:
Crystal Corral by Groff Automation
cView by Chelsea Technologies
ViewerFX by Origin Software
Crystal Kiwi Viewer by Crystal Kiwi
Report Viewer Pro by Report Viewer Limited
RV by Climate 27
Logicity Pro by SaberLogic
Report Runner Viewer by Jeff-Net
Easy View by Easy Street Software (**see warning above)
RTag Report Viewer by RTag
DataLink Viewer by Millet SW
If you have already tried one of these products, or are currently using one, I
would love to know what you think.
Highlighting individual rows and columns in a cross-tab
I was reviewing a report for a customer that was based on a cross-tab. The
cross-tab had several rows that had a different color font and background. Yet
when I went into the formatting properties of the cells, I couldn't see where
the color logic was entered. I searched the formatting formulas and all the
places I could think of where you could change the colors and couldn't find it.
Then I started right-clicking different areas to see if there was something I
had forgotten and found the answer in the 'Highlighting Expert'.
Currently, I never use the Highlighting Expert. That is because when it was
first introduced it was only good for numeric fields, and you could only write
conditions based on the current field's value. So I continued using the
conditional formatting formulas, which are give you more control over the
condition logic.
But it seems that the Highlighting Expert has become much more sophisticated
now. This is especially true within cross-tabs where I think it is a better
choice than the normal conditional formatting formulas. The Highlighting Expert
can easily reference values in both the row headings and the column headings of
every cell, in addition to the value of the cell. So I can now change the font
and/or the background color of anything in the Fedex column, or anything in the
row for Canada. To reference these same values using conditional formatting you
would need to use the new grid functions available for cross-tabs. This can be
done but it is much more complex.
But there are two downsides to using the Highlighting Expert.
1) There is no way for the field search to find when fields are used here.
2) There is no way to copy the logic from one cell to another.
Both of these are easy when you use the conditional formatting formulas. So I
will probably continue using conditional formatting on field objects, but I may
now find myself using the Highlighting Expert on cross-tabs.
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.
Reporting on 'multi-value' fields
There were recently two different threads in LinkedIn that discussed reporting
on 'multi-select' or 'multi-value' fields. A multi-value field allows the user
to enter more than one value, or a list, in a single field. But these
discussions are usually a muddle because the term only describes how the data is
entered or how it appears on the screen. But, to plan a report using multi-value
fields you have to know how the data is stored which determines how it appears
in a report. I know of four different methods for storing multi-value fields, so
the first step is making sure you know how the data is stored.
A true multi-value field violates proper database design rules. When you need to
store a list of items related to the same row the correct approach is to use
another table that provides a one-to-many relationship. Some applications will
do this and make it appear on the screen as a single, multi-value list, but
others take different approaches. Below are the options that I am aware of.
As an example, think of assigning one, two or three categories to each order.
1) One field stored in multiple records, all in a separate "child" table.
This is the "correct" approach according to database theory. You can have
unlimited values this way but you get duplicate records when you bring in this
table. For our example you would have 3 records. Each would have the
order ID, and each record would have a different category in the category field.
2) Different fields stored in one record of the same table.
I have seen this presented on the screen as a multi-value field, even though
each slot has a separate field in the database record. This eliminates
inflation because there is only on record. But that also means that you won't be
able to group the records independently without a little trickery. And,
this approach will only allow a fixed number of slots for you to fill. For
our example there would be 3 extra fields in the order table called CategoryA,
CategoryB, CategoryC.
3) One field stored in one record.
This is a true multi-value field. Here the list is stored as items separated by
commas or semi-colons. This was how it was done in
Lotus Notes
years ago. This also eliminates inflation because there is only on record. But
again, you won't be able to group the records independently. Depending on
how this is done the list may only allow a limited number of values. For our
example there would be 1 extra field in the order table called Category and in
it you would see all three values separated by commas. Something like:
parts, service, support
4)
MS Access Hybrid. This displays as if it is option 3 but in the
background stores the data as option 1. I have never run into this one.
So before you can create reports on this data you have to know which storage
option they are using. To do that put the field or fields on the report
and preview. If there is only one field with delimiters than you have
option 3. If there are several different fields side-by-side then you have
option 2. If the data is all in one column on separate rows, then you have
option 1.
Code Search Pro 2014
Find It EZ
has just released version 14 of their code search software. It is now called
Code Search Pro Desktop 2014. The new features for Crystal users include the
ability to go through a batch of reports and:
Verify the database
Change the data source
Set the table alias names
Create a database cross reference report
Also, the tool now creates an index of scanned documents which makes searches
faster. And it is certified to support SQL Server 2014, Oracle 12c,
MySQL 5.7 and DB2 10.5. See the
Find It EZ website for a free 14-day trial. Or you can use the new
option of
Code Search Pro in a Saas model. This allows you to use it fully
for 90 days, with unlimited support, for $99.
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.
Eliminating trailing zeros in decimal numbers
A user in one of my LinkedIn groups had an unusual requirement. He needed
to eliminate trailing zeros from a series of decimal numbers. The numbers
could go out to 4 or more decimal places and he didn't want to show any trailing
zeros. He wanted a column to look like this:
1.2345
2.345
3.45
4.3
instead of this
1.2345
2.3450
3.4500
4.3000
There are other approaches but I think this is the simplest. Just put your field
in the first line of this formula:
Local NumberVar x := {YourField};
Local NumberVar y :=
Length (Totext (Truncate (Val (StrReverse (Totext (x,9)))),0,''));
Totext (x,y)
Note that this will work with numbers up to 9 decimals. You can increase the
number of decimals in the second line by replacing the number 9 with a larger
number.
The above example is a formula that returns a string. Another approach is to
take the original numeric field and go into:
[ Format >> Field >> Number tab >> Customize button ]
Then put the following line into the condition button of the decimal places
property. You don't even need the name of the field:
Length (Totext (Truncate (Val (StrReverse (Totext (CurrentFieldValue,9)))),0,''))
This is simpler because it doesn't involve writing a formula field and it
also doesn't change the data type to 'string'. It can also be applied to
multiple fields without modification. The only downside is that having the logic
hidden in a conditional property may mean that it is forgotten.
Select records after the most recent business day
I was helping a user implement my formula for
calculating the most recent business date. He wanted to use the most
recent business date before today as the starting point for his date filter. We
were able to calculate the correct date and use it in the selection formula
correctly, but I noticed that the date criteria wasn't passing to the SQL. This
was affecting the performance of the report.
I tried starting the formula with "BeforeReadingRecords" since it didn't need
any field values from the database. But then it couldn't see the holidays
formula which stores the list of holidays in a second separate formula. I
had trouble assigning both formulas to BeforeReadingRecords and still have one
formula reference the other. Then I realized that I could combine them into one
formula by putting the holiday array at the beginning of the business dates
formula. As soon as did that I could assign the combined formula to be
BeforeReadingRecords, and then the value was passed successfully to the SQL
WHERE clause.
So I had to think back about why I originally wrote the two formulas separately.
I think in general I thought it was more efficient to have the holiday array
done once in the report header. That way I could use it in several different
business date formulas and Crystal wouldn't have to recreate the holiday list on
every record as it did the business date calculation. But in this case I found
that it was more efficient to move the holidays than it was to have the database
return all dates.
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 November 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.
Finding fields in use outside of formulas
SAP created a suggestion box called the "Idea Place" in 2010. This month idea
D661 from October 2010 was accepted by the developers at SAP. It is scheduled
for release later this year. The idea is to make it easier to find where fields
are in use in a report. Currently you can search all formulas, but some fields
are used in non-formula properties. For instance fields are used as sorts, as
groups, or as links to a subreport. So SAP plans to add 3 new search options
that cover these situations. The bad news is that after 4 years it will only be
implemented in CR for Enterprise.
So the rest of us will have to continue to use the old way of finding where
fields are used outside of formulas. Just export the report to "report
definition" and open the resulting text file. Then search for that field name
within the text. You will find fields used as sorts or as groups. You will also
find a field when it is used by a summary field or a field that is just sitting
on a section of the report. These last two don't seem to be covered even after
the upcoming improvements. The only fields you will not find listed in a report
definition export are fields used as a link to a subreport. The subreport
definition is there within the main report definition, but any main report
fields used as links to the subreport parameters are not identified in the
definition. If a field is in use and doesn't show up in the "report
definition" then you will have to check the subreport links manually.
Links to prior versions, service packs, runtime and merge
modules
I had to track down some older versions of Crystal Reports along with service
packs and runtime modules. I found some useful links and want to put them all in
one place so that I don't have to hunt them down again. Others might find
the links useful so I have
posted them in my blog. The links are for:
Version downloads and service packs (SAP)
Version downloads and service packs (external)
CR XI downloads and service packs
CR 2008 service packs
CR 2008 SP 6 supported platforms
SAP Downloads search page
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