The Crystal Reports Underground News Volume 2013.03
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for March 2013:
** Comparison of desktop-based schedulers (2013 update)
** Discount on purchases of Find it EZ
** My library of Crystal Reports materials
** Using metric rulers in design or preview
** Font sizes inside charts
** Let me create your Crystal Reports
** CR versions over time 2013
** Using auto-complete in Crystal Reports formulas
** Open enrollment Crystal classes in Frederick, MD
** SQL expression bug in v9, v10, v11(XI)
** Using SQL commands in ABRA HR
Comparison of desktop-based
schedulers (2013 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.
I discovered and added one new tool this year, bringing the pool of reviewed
tools up to ten. The article provides a brief introduction to each product and
describes what sets that product 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.
Discount on purchases of Find it
EZ
If you have been considering purchasing Find it EZ then you might want to do
before the end of March. The developers of Find it EZ are offering my readers a
25% discount on purchases through 3/31/2013. Use
this link
to get the discount. And they are planning a price increase in mid-March
so if you use the link now there is an extra savings.
If you aren't familiar with Find it EZ, it is a source code scanning tool that I
have written about as part of my
comparison of RPT management utilities. It can search your entire
application, including your RPT files, for the use of specific tables, fields
and text.
The promotion this month is to highlight some recent updates. Find it EZ is now:
certified to run on Windows 8 and Windows Server 2012
certified for Citrix Server with floating license concurrent
distributed user support
certified for SQL Server 2012 and latest version of SSRS
able to search your database by owner or schema
able to scan within MS Query files
If you have tried it and have any feedback, please let me know.
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.
Using metric rulers in design or
preview
I was working with one of my customers on reports they needed to deploy in
Japan. The customer asked me how to convert the rulers in design and preview to
use metric units. I had never done this before, but I knew there was a setting
that mentioned this in the Page Setup window (File>Page Setup) so we went there
first. That changed the page sizes to metric units but didn't have any effect on
the the design or preview rulers. Next we went to the "View" menu and tried
changing the Product Locale to Germany, since that country uses metric units.
This changed the ruler to metric, but it also put the entire menu and all the
GUI labels into German. Right below this menu option is a similar menu item
called "Preferred Viewing Locale". It wasn't really clear how this was different
but we tried that next. That changed the rulers to metric without changing the
language of the product.
On my own installation of CR I only have one option in Product Locale (English),
but I have dozens of options in the "Preferred Viewing Locale". It appears
that these are installed automatically, while the full Product Locales are only
installed if you select them at the time of install. My customer had
the full list in both places.
Font sizes inside charts
If you have worked with charts you know that the font sizes can be a bit tricky.
With most text objects and fields on a report, the font size is independent of
the object size. You can make an object bigger but the font size doesn't change.
But with charts, all font sizes are relative. Make a chart bigger and you
automatically get bigger fonts for all the text in the chart.
This poses a challenge for getting labels or legend entries in your chart to be
the same size as the text outside the chart. You can't simply set them to the
same number because the chart labels are relative. Setting it to size 10 may not
result in size 10 font, so you have to play around a bit to get the sizes right.
In some cases I have had to type the my own labels independent of the chart and
line them up manually.
There is one other challenge that usually comes into play when you want very
small chart fonts. With most text objects you can override the font size
selector by typing in a smaller number that doesn't exist in the drop down list.
However in most places for charts you are limited to the drop down values. So
for instance if you right click on a label below a bar and select "format axis
label", you will have to select a number from the size selector drop-down.
You can't type a number in here.
But, if you need to override that list and type in an unlisted font size you can
use an alternative method:
- Go into the Chart Expert
- Go to the TEXT tab
- Select "Group Labels" from the list at
the bottom
- Click the "Font" button
Now you get a different font size
interface that allows you to enter an unlisted font size (like 2). Thanks to
Adam Butt of APB Reports in Trondheim, Norway for this last tip. Also note that
if you are trying to change the size of the legend labels, the item for this in
the TEXT tab is called "legend title" which might throw some people off. And, it
only appears if you have activated a legend.
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.
CR versions over time 2013
Last year about this time I posted a chart that showed my newsletter subscribers
and what versions of CR they were using when they signed up. I have decided to
make this an annual post so you can get a feeling for which versions are being
used and how quickly the new versions are catching on.
This years post even includes numbers for 2013. With only 2 months of
data for this year the 2013 ratios might be a bit skewed.
What I notice is that CR 2011 (v14) is starting to take over while CR XI
(v11) is now starting to recede. I think the slight jump in the oldest
versions in 2003 is a statistical fluke that will diminish as more data for the
year accumulates.
Using auto-complete in Crystal
Reports formulas
When you do something one way for a long time, it can be hard to get out of the
rut. So even though the Crystal formula editor has a pretty good 'auto-complete'
feature, I still don't take full advantage of it. I still tend to scroll through
the list of fields. But recently I have started to take advantage of
auto-complete to speed things up.
The auto-complete in CR has two components. One is for fields which was
introduced in CR v12(2008). The other is for reserved words like functions and
was introduced in CR v10.
Fields:
If you want to enter a field you simply type a curly bracket into the formula
editor. A list of fields will pop up. At that point it will include a list of
all the field objects that you have created (ie formulas, parameters, running
totals, etc) and it will include a list of the data tables used in the report.
If you type one or more characters after the bracket the list will get shorter,
including only the fields and tables that start with those characters.
Once the list is short enough you can scroll down and highlight the field or
table you need. You select the field or table by hitting either enter or tab. If
you are on a field when you hit enter then that field will be inserted into the
formula. If you are on a table then that table name will be inserted into the
formula followed by a period. Another drop down will now appear so that you can
repeat the process to select a field. You can type a few characters to shorten
the list or select a field by highlighting it and hitting enter or tab.
Reserved Words:
If you want to enter a reserved word like WhilePrintingRecords or
DrillDownGroupLevel you can type the first character or two of that word and
then click Ctrl-Spacebar. This will pull up a list of all the reserved words
that match those starting characters. You can then type characters until you see
the word and select with a TAB or ENTER. If there is only one word that matches
what you have typed it will be entered automatically.
But there is one small annoyance I have found. It happens when I am deleting
part of a formula and accidentally delete the initial bracket of a field name.
Typing that bracket automatically invokes the auto-complete which also adds the
right bracket, and opens the drop down list of fields. I have to delete the
extra bracket and I can't use the up/down arrows till I leave that field.
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.
SQL expression bug in v9, v10,
v11(XI)
So here is an odd bug I found in Crystal versions 9, 10 and 11(XI). It affects
SQL Expressions that contain a correlated sub-query. This example expression
works in both CRv8.5 and CRv12 (2008) but none of the versions in between:
(SELECT
Min (CAL.ONDATE)
FROM GM6.dbo.CAL CAL
WHERE CAL.ACCOUNTNO = Contact1.AccountNo)
The expression is designed to show the first appointment in the Cal table for each contact in the report. Using a SQL Expression avoids duplication I would normally get by adding the Cal table to the report directly. Run this expression in CR versions 9, 10 or 11 and you get this error:
"Error in compiling SQL Expression.
The multi-part identifier Contact1.AccountNo
could not be bound."
At first I thought this error was coming
from SQL. As a test I took a v12 report that had this expression and opened it
in v10. The report ran fine. But if I then try to edit the SQL expression or
even check it, I get the error above. At that point the report will no longer
run in v10 no matter what I do. My only option is to close it without saving
changes and reopen it – then it works fine again. This tells me that the SQL is
fine because it isn't the DB that rejects it. The error only comes from the CR
SQL syntax validation. The validation must not happen at refresh because if it
did the report would not run when first opened. But once the validation has
failed the report can't be refreshed, so the SQL expression must be marked as
bad until it can pass the validation.
A few more experiments showed that this error is somehow related to using
aliases. If I remove all alias references the problem goes away. This expression
works in all CR versions:
( SELECT
Min ( ONDATE )
FROM GM6.dbo.CAL
WHERE ACCOUNTNO = Contact1.AccountNo )
It is curious that the alias of the CAL
table causes the field from the Contact1 table to error out.
But here is the really odd part. Another way to fix it is to put a SQL comment
line at the beginning of the expression. If the comment includes the name of the
field mentioned in the error message, then that somehow makes it work, even with
aliases. So the following expression also works in all versions:
(
/* some words accountno and some more words */
SELECT Min ( CAL.ONDATE )
FROM GM6.dbo.CAL CAL
WHERE CAL.ACCOUNTNO = Contact1.AccountNo )
The only word necessary in the comment is
the word 'accountno'. If I delete that word or misspell it, I get the original
error. Clearly the comment doesn't make a difference to the database, but it
must have some effect on the CR syntax validation. This last behavior was
discovered by Bob Antaki, a developer with the Bay Area News Group in
California.
So if you have struggled with adding SQL expressions like these to your reports,
hopefully this will help. If you want to learn more about using SQL in Crystal
Reports you can read my
Expert's Guide to SQL Expressions, Options and Commands, or you can
schedule a session with me to go over some of these concepts.
Using SQL commands in ABRA HR
I don't know how many of you out there using Crystal with the ABRA HR/Payroll
software, but I just had a real challenging episode with it and ended up
learning quite a bit (the hard way).
My task was to speed up a report that took 3 hours to run. The problem was
obvious – a recurring subreport that ran 900 times on a typical payroll. A
subreport was used because linking the table would have required
adding a filter to an outer join, something that the linking
window can't do. When I found out commands didn't work in the ABRA OLEDB
connection it started me on a long process for finding a solution. Plan
B turned to plan C and then plans D through F before we found our solution.
Those interested can
follow the full saga.
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 2013 by Ken Hamady
All rights reserved - Republishing this material requires written permission