The Crystal Reports Underground News Volume 2013.11
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for November 2013:
** Crystal Reports formula function libraries (2013 update)
** Tell a friend
** Using a Report Custom Function
** My library of Crystal Reports materials
** Crystal Reports vs SSRS #3
** Reduced fonts when exporting to PDF
** Let me create your Crystal Reports
** Running a macro when exporting to Excel
** Adding an index based on a cross-tab
** Open enrollment Crystal classes in Frederick, MD
** CR Server vs the CR Runtime Engine
** Technical documents for Web deployments
Gems from the Archives (August 2002):
** Reporting on Data that Isn't There
** Is there a subreport hidden in this report
Crystal Reports formula function libraries (2013
update)
It is time for my annual
comparison of formula function libraries. If you aren't familiar with
User Function Libraries (or UFLs) they are DLL files that add new formula
functions to your Crystal Reports formula editor. With these functions your
formulas can do some pretty amazing things like:
1) Carry values from today's report to tomorrow's report
2) Carry values from one report to another.
3) Append lines of text to an external text file.
4) Automatically copy a value to the clipboard.
5) Check the user name of the user running the report.
6) See if a file or folder exists (on your network or on the Internet).
7) Rename/copy/delete a file on your hard drive or network drive.
8) Launch an application or run a batch file.
9) Execute a SQL statement (Select/Insert/Delete).
10) Send an Email using information in the report.
11) Create a table of contents or an index for your report.
12) Calculate distances between zip codes or long./lat. coordinates.
If this sounds interesting you can read my
complete comparison including a list of all the functions provided by
each DLL. The five UFL providers are:
Bjarke Viksoe (U2lwin32)
Maginus Software (CRUFLMAG)
Millet Software (Cut Light)
Chelsea Tech (File Mgt, Text, Share and others)
CrystalKiwi (Export, Table of Contents)
So far, the only vendor that has reported significant changes since last year is
Millet Software. Ido Millet has added a handful of new functions to Cut Light.
You will find these highlighted in the matrix. If you need help deploying one of
these functions in a project let me know.
Tell a friend.
I regularly hear from Crystal Reports users who discover my site/blog/newsletter
and wish they had known about it sooner. So if you have any colleagues
that use Crystal Reports, please share a copy of this newsletter with them.
Using a report custom function
I did something for the first time this past month. I wrote a report custom
function to use in production report. I have always taught that Custom
Functions are only worthwhile if you have a repository and can use it once
across reports. I hadn't run into a situation where a single report merits
having its own function, until my current project. In this report we need
to convert total seconds into a string that looks like 36:45 [hh:mm]. The
formula is easy and is even posted on my "formulas" page. But by the time the
report was done we had used the same logic in 140 different formulas. And after
that we discovered that our logic had to also account for negative times. This
meant changing every formula. It was at that point that I decided to write the
logic as a function and update all the formulas to use the function. If we
find another improvement, we will only have to make the change in one place.
I even plan on transferring this function to other reports that need the same
logic. Even without a repository it is a worthwhile. And it makes the
formulas much cleaner. This is the function:
//SecondsToTimeString
Function (numberVar TotalSec)
NumberVar Hours := Truncate (Abs( TotalSec / 3600)) ;
NumberVar Minutes := Truncate (Remainder ( TotalSec , 3600) /
60) ;
(if Totalsec < 0 then '-' else '') +
Totext ( Hours , '00' ) + ':' +
Totext ( Abs(Minutes) , '00' )
And here is what it looks like when used:
SecondsToTimeString ({@ActiveSeconds})
My complete library of Crystal Reports
materials:
Do you want to fully 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.
Crystal Reports 2013
I have been asked a couple of times this week about Crystal Reports 2013, so I
guess it is time to post something. Personally, I have not upgraded since
CR CR 2008. When CR 2011 came out a couple of years ago I downloaded the
trial and found that it was virtually identical to what I had. There were
3 new features that I mentioned in my blog, but none of these were worth the
cost of the upgrade to me. Since then, SAP has made it clear that they do not
plan to develop new features for the standalone version of CR. They are devoting
their energies to the version of CR that is embedded into Business Objects
Enterprise.
So when SAP released CR 2013 I wasn't expecting many changes. In fact I am
not aware of ANY changes. And since SAP is describing this as a
'rebranding' of the product as opposed to an upgrade, I don't think I will spend
much time looking for new features. If anyone is is aware of any new
features in CR 2013, please let me know.
Crystal Reports vs SSRS #3
Today's comparison comes courtesy of Hessel de Walle, from The Netherlands.
Much of his blog is in Dutch, but he has written two articles that compare
specific tasks done in both Crystal Reports and SSRS. The first is
creating alternating section colors. The second is doing
page number resets and "page N of M". The third is
creating running totals.
Reduced fonts when exporting to PDF
I thought I had written about this before, but apparently I have only mentioned
this in forums and never on my own blog. When you export a Crystal Report to
PDF, Crystal will reduce the fonts by around 5%. Most people don't notice this,
but in some situations this causes problems. The solution requires that you go
into the registry and add some keys. Finding where to make the change and what
the change should be is a bit tricky because there is an older method and a new
improved method.
The older method requires that you add the following registry entry called
ForceLargerFonts. This is case sensitive. Set the type to DWord and the value to
1 to activate. You add it in the following two places if you are using the CRv12
designer (CR 2008):
HKEY_CURRENT_USER\Software\Business
Objects\Suite 12.0\Crystal Reports\Export\PDF
HKEY_LOCAL_MACHINE\Software\Business Objects\Suite 12.0\Crystal
Reports\Export\PDF
The number after the word "Suite" will reflect your version. If the "Export" or
"PDF" keys don't exist you will have to add them. Also note that if you are
doing app deployment the registry path for the runtime engine might be slightly
different (see below). If you are doing this on an IIS server you may have to
change the permissions on these keys.
The problem with the old method is that it occasionally truncates text on the
right side of the page. The new improved method became available in CR 11(XI) SP
5. You can do a web search for SAP document ADAPT00940675 which explains how to
use these registry keys:
HKEY_CURRENT_USER\Software\Business
Objects\Suite 12.0\Crystal Reports\Export\PDF\TruncationAdjustment (=2)
HKEY_CURRENT_USER\Software\Business Objects\Suite 12.0\Crystal
Reports\Export\PDF\UsePrecisePositioningForText (=1)
HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 12.0\Crystal
Reports\Export\PDF\TruncationAdjustment (=2)
HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 12.0\Crystal
Reports\Export\PDF\UsePrecisePositioningForText (=1)
The settings above enlarge the font and also eliminate the truncation in most
cases, and when it doesn't you can increase the TruncationAdjustment from 2 to 3
(or higher) until the problem is resolved.
Last note. If you are using Crystal Reports runtime for Visual Studio 2010, the
registry key paths will all start with something like this, but the four keys
are the same:
HKLM\SOFTWARE\SAP
BusinessObjects\Crystal Reports for .NET Framework 4.0\Crystal Reports\…
See my full
blog post for more links and more details.
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.
Running a macro when exporting to Excel
Ido Millet of Millet software has added a new feature to Visual Cut, one of the
products in my
desktop scheduling tools list. Now, when you export to Excel you
can have Visual Cut automatically invoke an existing Macro. The Macro can be in
the target file (if you are replacing data in a file) or in a completely
separate file. This means, for example, that you can export data to
replace an existing tab in a workbook, and then automatically invoke a macro to
make changes to the replacement data. If you have a specific use for a
feature like this, I would like to hear from you.
For more information about Visual Cut and a comparison to other tools in this
category, see my
annual review of desktop schedulers.
Adding an index based on a cross-tab
Jamie Wiseman, one of the SAP bloggers, wrote up a clever way to
create an index using a cross-tab
and using the 'Display String' property. It uses some fairly complex formulas
but I was surprised that he didn't use any arrays. Instead he used a very long
string of characters. By using the InStr() function to find each pair of
matching entry and page number he avoided the complexity of looping through
arrays. Not simple, but simpler.
The second part of the post has to do with creating hyperlinks to take you to
the index pages, but this only works if you are publishing this to CR Server or
BOE. Still the index idea is pretty clever.
Open enrollment Crystal classes in Frederick,
MD
Stop struggling with Crystal Reports and learn how to use it fully. Come
join me in my January 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, Dallas, Omaha, Detroit, Philadelphia, New York City,
Boston, Vancouver and Montreal. Call for details.
CR Server vs the CR runtime engine
I recently read a forum thread where users were comparing the different web
deployment options for Crystal Reports. One post, by Blair Wheadon of SAP, gave
a good comparison of the two different CR engines. He compares the one
that supports CR Server/BO Enterprise with the CR runtime engine that supports
home grown and third party web applications. He has given me permission to post
it here:
"Its important to
understand the differences between SAP Crystal Server, and other server
reporting products based on our runtime engine.
Our runtime engine is designed and licensed to add reporting to server
applications. It is a lightweight, embeddable component engine without any
security or scheduling built it. It is limited to processing a maximum of 3
simultaneous report requests. It is designed to run within the web server
itself. Server products like those sold by Reportal and Christian Steven depend
on this limited runtime engine, and add their own scheduling functionality.
SAP Crystal Server however is the same technology used by our high-end SAP
BusinessObjects Business Intelligence product, with some limitations that allow
us to price it very aggressively. It includes support for Xcelsius dashboarding,
the new Explorer tool for casual BI users, Sharepoint and Office integration,
Active Directory and LDAP security integration, scheduling, and new
Enterprise-class features to reduce the cost of ownership like lifecycle
management (for promoting reports between test, development, and production),
auditing, and monitoring to ensure uptime.
Unlike the limited runtime engine, the SAP Crystal Server report engine uses all
the server resources available, including page-level caching, and unlimited
threads to effectively manage load. Plus it now includes entry-level BI
functionality like dashboard support, the Explorer tool for casual BI use cases,
and support for 64-bit servers.
The major limitations are: limited to a single server, no support for ERP
integration (like SAP integration or Oracle integration), mobile support
available only as an add-on, and it does not include Web Intelligence. None of
these limitation are material to customers looking for a simple reporting
server.
It does not use CPU licensing, so you can throw as many CPUs at your reporting
problem as you want (as long as you're on a single server) , and deploy to
either virtual or physical servers without any licensing impact."
Technical documents for Web deployments
And one addition to the post above. For those of you who still plan on using the
runtime engine there is a great pair of documents that explains how to get the
most out of your hardware/software combination.
The first one
is newer and is designed to help you select the most appropriate engine for your
deployment. It explains the differences between the engines and then highlights
the advantages of the more expensive engines. It also gives you a method
to estimate the number of supported users in the runtime engine. But the one
thing that this newer document doesn't do is provide specific techniques that
you can use to maximize throughput of the runtime engine. For that there
is an older
document. I have confirmed with the folks at SAP that this older
document still applies in a .NET runtime deployment.
If you have any trouble locating these documents because the links have changed,
please let me know.
Gems from the Archives (August 2002):
Reporting on Data that Isn't There
Is there a subreport hidden in this report
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