
The Crystal Reports Underground News Volume 2011.05
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for May 2011:
** New features in Crystal Reports 2011
** License changes in Crystal Reports 2011
** Update to my comparison of server-based schedulers
** My library of Crystal Reports materials
** Tools for troubleshooting
** Case study for UNION of daily files
** New Crystal Reports licensing overview
** Let me create your Crystal Reports
** Formatting chart dates (part 2)
** Mysterious blank pages
** Open enrollment Crystal classes in Frederick, MD
Other recent blog articles:
Crystal
Reports 2011 release notes
Evaluate
CR Server "in the cloud" (or maybe not)
Odd error using
ODBC to IBM U2
Classic
VB runtime and PDF exports
New features in Crystal Reports
2011
We have waited 3 years for a new version of Crystal Reports, so you might be a
bit disappointed at the list of new features. They are the ability to:
1) Export to XLSX format which gets you past the 65K limit on rows.
2) Export to RPTR format, a format that can't be opened in the Crystal Designer,
but can only be run in one of the report viewers. This lets your users run
your reports without giving them access to any proprietary information contained
in the RPT file.
For an explanation of why there are so few new features you can read this
SAP blog post.
Also note that this is officially version 14, not version 13. Version 13
is the developer engine in Visual Studio 2010..
Crystal Reports 2011 license
changes
Crystal product managers and I have a tradition that we follow with every new
version:
1) Whoever happens to own Crystal that week puts in excessive restrictions.
2) I point out that the restrictions are excessive.
3) They have a serious internal discussion.
4) Things (usually) change for the better.
This time the issue was the restrictions in section (7). If you strip it
down the long legal paragraph you get this sentence:
"Except as expressly permitted… you may not … use the Software to provide ….
third party training, … or consulting services, or any other commercial service
related to the Software …"
This same wording was included initially with CR 2008 and then removed after I
started to write about it. If these restrictions were ever enforced they
would choke off the entire ecosystem that has given Crystal its position as
market leader. As I reread the license I got some hope from section 4:
"PRODUCT SPECIFIC USE RIGHTS. Additional terms relating to your use of the
Software are found at www.sap.com/company/licenses/product-use-rights" …
But when I tried to follow that link to see what additional rights I had, things
got bizarre. You can read the details in my blog, but lets just say no
mortal would ever find the information. And once found the information didn't
address the new restrictions.
So I sent some feedback to the folks at SAP. They had some internal
discussions and now assure me that they intend to make some adjustments to the
restrictions section. The plan is to provide essentially the same user
rights that we have in CR 2008. And they are also going to make the link
to the "user rights" document go directly to the document and include a
meaningful table of contents. All of this is slated to be updated sometime
in July.
Update to my comparison of
server-based schedulers
In my March newsletter I updated my review of
desktop scheduling tools
for Crystal Reports. These tools allow you to automatically run a report,
export it to a file and deliver it to an Email InBox at a set time every day or
week. This months review is also for scheduling tools, but while the March
list was desktop-based schedulers, this list is
server-based schedulers.
Most people who need to schedule reports will be fine using a desktop scheduler,
even if they have to deliver reports to many recipients. But in some
environments there will be many people scheduling reports and in those cases it
might make sense to upgrade to a server-based scheduler. So my blog post
compares eight server-based tools, providing you with their core features and
prices so that you can narrow down your search. Two of the tools are new
this year:
The tools being compared are:
Navarre Report Scheduler by PCS Programming
cView SERVER by Chelsea Technologies
Ps Report Scheduler by Perryman Software
rePORTAL SC by rePORTAL Software
Report Runner Batch Enterprise by Jeff-Net
Liaison Messenger EDD by Liaison Software Corp
Universal Report Server by VersaReports
CRD Premium Edition by ChristianSteven Software
You can read a brief rundown of each product and download a feature comparison
matrix (PDF) that gives the details. And, with so many complex features I
have written a feature glossary to define the key features.
Finally, if you have tried one of these products and haven't given me your
opinion yet, please do.
My complete library of Crystal
Reports materials:
Do you struggle with subreports? Are you curious about cross-tabs? 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.
Tools for troubleshooting
Recently an unusual number of customers have brought me errors to troubleshoot.
Most of them involve errors outside of Crystal with things like missing DLL
files or installs that seem the same but that behave differently. So I was
pleasantly surprised to find a recent
blog post
on the SAP web site that provides a list of free tools for helping to
troubleshoot things like this. There are 5 tools listed but I think two
would apply most to the problems I faced this month:
1)
Modules
is a free utility from the SAP web site. It allows you to run a report on
two different systems (ie one that works and one that doesn't) and find all the
files that are different in those two environments. It makes a list of the
files used in each environment and then compares to see which are different. It
is an old tool but worked fine in my Win7 64 environment.
2) Depends.exe
(Dependency walker) is another free tool that takes an EXE or DLL file and lists
all of the other files that it relies on. This is perfect for when you
have a DLL that won't work or register correctly. Often a missing
dependent file is part of the problem.
And if you need to troubleshoot hangs and crashes, monitor HTTP / HTTPS traffic
or monitor local file system and registry activity, the other tools should help.
Case study for UNION of daily
files
I recently found a way to mix several concepts and solve a problem that others
might be facing. The challenge came from a company who stores several hundred
thousand log transactions each day. The way the system is set up, each day's
transactions are stored in a separate SQL table. The table name is the date of
the transaction. What they wanted to do was to run reports that summarize
these transactions for an entire month, with subtotals by category. This would
require combining a month's worth of daily tables on the fly, and then getting
subtotals by category. At first I recommended a stored procedure because I
didn't think we could calculate the table names on the fly in a SQL command.
But we aren't allowed to add SPs to the database. Then I realized that by
adding the report as a subreport we would have the chance to do the calculations
we needed within Crystal.
So, my first solution was a 2-step process. We created a summary
report for one day with subtotals by category. The report used a SQL
command and we put a parameter field into the FROM clause where the table name
would go. That allowed us to run any day we wanted by entering the date
into that parameter.
Then we created a container report with up to 31 details based on the days in
the chosen month. It had a formula that calculated the table names for all of
the dates in that month. We put the subreport in at the detail level and linked
the formula to the parameter so that the report ran one subreport for each day
and generated the category summary rows for each day. We exported this to a
spreadsheet, creating a handful of rows for each day in the month, each row
showing category totals. Last we created a second report to read the
summary rows in the spreadsheet and combine them to get category totals for the
entire month. It worked great, but the 2-step process wasn't ideal.
Then I thought through another approach that would eliminate the need for two
steps – although it might be stretching SQL to make it work. Check out this
blog post for more details.
New Crystal Reports licensing overview
Blair Wheadon of SAP has published an updated overview of Crystal Reports
licensing terms. He does a good job and covers multiple versions
and editions. So this is a good place to start if you have question on
product licenses. If this doesn't answer your questions I might be able to
help (I actually read them).
My only other comment on this article is that SAP still has not given up trying
to add a hefty fee to every Crystal Reports course book produced by anyone.
They try to do this by adding restrictions within the product license covering
the use of screen shots. So, Blair reminds us that 'commercial course
material' is the only medium that is not allowed to use these images. And
if someone insists on creating course material for Crystal Reports, SAP will
want to charge a significant fee. One vendor who wanted to be 'authorized'
was selling Crystal course materials for $65. As soon as they were
authorized their price went to $98, and they are now at $140 for a single book.
Fortunately there are several companies that ignore SAP on this and sell more
reasonably priced books for $55 to $80.
Personally, I have never felt screen shots to be essential, so
my course books have been without images for 16 years and have been used
to teach thousands of satisfied students.
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.
Formatting chart dates (part 2)
Last month I
wrote a post
to complain that Crystal didn't give you a way to directly control the format of
dates along the bottom of a monthly bar chart. So, of course, this week I find
something in Crystal Reports that I had never seen before, and which shows that
I was only half correct.
A customer sent me a chart to troubleshoot and he had used a chart type called
"Numeric Axis". This type of chart is down the list pretty far, below
Radar charts and Bubble charts so I have never experimented with it.
But when I changed the chart to a normal bar chart it became obvious why this
type was used. The number series along the bottom of the chart had changed
to show decimals, and I now had no way of changing the format so that the
decimals didn't show. I hit undo and saw that this chart type allowed me
to directly control the format of a series of numbers at the base of a series of
bars or lines.
My first thought was why hadn't they done the same thing for dates. My
second thought was that maybe they had, so I went back into the options for
these Numeric Axis charts. Hidden in there were three options for date
axis charts – Line, Bar and Area. These options only give you 8 date
formats to pick from, so you don't have as much control over the format as with
the methods I described. But they are worth mentioning, since they will
probably work well enough in 80 percent of cases.
Mysterious blank pages
I am used to helping users troubleshoot
blank pages in their Crystal Reports. The usual culprits are blank report
footers or group footers that need to be suppressed. Occasionally there is
a page break that isn't put in correctly. But I recently had one that I
had not seen before. In preview the report showed 3 pages. But
if the report was printed there were double the number of pages, with a blank
page between each printed page. The same doubling would happen when it was
exported to a PDF.
What I found was an image object (an OLE object) that was just a hair wider than
the printable area on the page. In preview this would show as a white
space to the right of the printed page, which was easy to overlook. It
also didn't affect the page count. But when printed or exported to
PDF it would alternate printed and blank pages and double the page count.
Once the OLE object was moved to within the printable area the blank pages went
away.
This surprised me because normal CR objects that have a static size (fields,
charts, boxes, etc) are just truncated if they stretch to the right too far.
The only object that I knew of that could generate these pages to the right were
cross-tabs, because their width is dynamic. So if you have a
mysterious extra page in your report this is one more thing to check.
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 June 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.
Or, if you want to schedule a class at your office, using your data, that is my
specialty. I also have several top-notch instructors in the the US, UK and
Canada that can deliver my class at your location for a very competitive price.
Call or visit my web site for more details.
Other recent blog articles:
Crystal
Reports 2011 release notes
Evaluate
CR Server "in the cloud" (or maybe not)
Odd error using
ODBC to IBM U2
Classic
VB runtime and PDF exports
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 2011 by Ken Hamady
All rights reserved - Republishing this material requires written permission