The Crystal Reports Underground News Volume 2014.11
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for
November 2014:
** Crystal Reports formula function libraries (2014)
** Free report management tool from Jeff-Net
** My library of Crystal Reports materials
** Two new report management tools
** "Set Location" on a batch of reports
** Let me create your Crystal Reports
** ReCrystallize Light
** "Instant" Data Dictionary
** Open enrollment Crystal classes in Frederick, MD
** Report changes triggered by rpt location
** Field disappears during Verify Database
Other blog articles
** Working with binary fields in SQL
** Java script error on the Crystal Reports start page
Gems from the Archives
** The chart changes based on parameter values chosen? (Volume
2003.11)
** Some Functions not allowed when writing custom Functions (Volume
2003.11)
Crystal Reports
formula function libraries (2014)
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)
The only product that has changed since last year is Cut Light, where Millet
Software has added a handful of new functions. You will find these highlighted
in the matrix. If you need help deploying one of these functions in a project
let me know.
Free report
management tool
I just had a call from a customer who wanted to run lists of reports grouped by
the report 'owner'. They wanted to know the simplest way to enter this into each
report and also have it available to create lists of reports for each owner.
Their original plan had been to add a text object to each report to display the
owner info, but they couldn't think of a way to generate lists based on that
data.
As it happens, Jeff-Net had just let me test drive their new tool for doing
report management and documentation, called
Report Runner Documentor. It is simple to use and is available for
free. My favorite part is how easily it loads the report's information (tables,
fields, links, formulas, summary info, etc) into a SQL Server database. This
allows you to create reports about your reports.
So I suggested that my customer put their 'owner' information into one of the
five "Summary Info" fields like "Author" or "Keywords". These are found in
each report under "File > Summary Info". These fields can be displayed on
the report, and they also get stored in the Documentor database. They can then
tap into this database to create their own lists in Crystal Reports, using that
field for grouping or filtering. Uncomplicated and inexpensive.
This past July I reviewed report management tools like this one and posted a
feature comparison matrix. Since then I have written about two new tools
so those have just been added. So, for the complete list of the tools available
in this group you can read my
updated annual comparison.
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.
Two new report
management tools
The developers at R-Tag have released two new tools for managing reports.
The first, R-Tag Crystal Reports Documentation and Search, is for searching and
documenting reports. For example you can find all the reports with Saved Data,
or PaperSize = A4 or the expression BackColor = CrSilver. It supports
search in text, formulas, SQL Expressions, table names and any other report
property. The price is $299 for the first user and $49 for any other user.
The second, R-Tag Crystal Reports Version Control, is a full source code
management system like Visual Source Safe. It provides the ability to check
reports in and out, keep all prior versions, restore previous versions and check
for changes between the versions. In addition it includes all of the
features of R-Tag Crystal Search. It costs $999.
Both are available for
purchase on their web site. And here is a document
explaining the
Documentation and Search features:
"Set Location" on a
batch of reports
In 2012, I
wrote about a tool that allows you to update the data source in all the
reports in a folder. It is called
CR Data Source and is put out by R-Tag. The original version would only
work on reports that use a single connection, which is the majority of the
reports that I do. But R-Tag has just updated this tool so that it can deal with
reports that have multiple connections. It will still only work with OLEDB and
ODBC connections. If you want to learn more about the tool, you can check out
a short video on the CR Data Source web page.
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.
ReCrystallize Light
ReCrystallize has just released a simplified edition called
ReCrystallize Light. Like
ReCrystallize Pro, it generates a web page for each of your reports.
These pages allow your users to run, refresh and export those reports on demand
from your intranet, extranet, or SharePoint site. The Light edition doesn't have
all the features of ReCrystallize Pro but it is simpler to use and less
expensive.
So what features are only found in the Pro edition? For a complete list of
differences between the Light and Pro you can
see the comparison posted on the ReCrystallize Web site. Some key
features found only in the Pro edition include the ability to:
Run reports that use more than one database connection.
Change the database connection/DSN/server name at runtime.
Create customized forms for parameters.
Pass hidden parameter values within a URL or from an
application.
If you need any of these you will need to upgrade to the Pro edition. Otherwise
you might want to give the Light Edition a try. Either way there is a free
trial.
"Instant" Data
Dictionary
I have a previous post that shows simple ways to
report on the structure of a database. This month one of my
readers sent me a report that gives you more detail, if you are using a SQL
Server database. You point the report to your DB connection, the report reads
all the tables and fields and prints out a schema of the tables and views. The
schema includes the table name, field names, date type & length, primary key,
foreign key, if the field allows nulls, and a description (if there are
descriptions in the meta data). Just do a Set Datasource Location on it to your
server/database and let it run. Note that if you have hundreds of tables this
might take a while.
If you have access to the server you can get this same information directly from
the Enterprise Manger, but not everyone has been granted this kind of access.
Once run, you have all the tools in CR to find specific items, or you can export
to .pdf and search the PDF.
You can
download the RPT file here. The saved data is an example taken from a
Goldmine contact manager database.
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 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, Omaha, Detroit, Tampa, Philadelphia, New York City, Boston,
Vancouver and Montreal. Call for details.
Report changes
triggered by rpt location
If you create reports for others, you might find a use for this technique. It
changes a report automatically based on the folder where the report is stored. I
use it to show a message in preview while in my design environment and have the
message automatically suppress when the report is moved to production.
Example:
I have a handful of customers that still use v8.5 which is almost 15 years old.
Unlike all versions of CR from v9 forward, there is no way to save changes done
in a later version of CR back into v8.5. So I have to maintain a copy of v8.5
just to use for these customers. When I receive a report to change the version
doesn't usually matter, so I don't always remember to check and see if the
customer still uses v8.5. I might do quite a bit of work on the report in my
current version before I check. Only when I go to save the file does CR warns me
that the report is from the older version of CR (if I do "Save As" it won't warn
me at all). At that point I have to reopen the original report in v8.5 and
make all the same changes a second time.
So I just came up with a way to add a warning to reports that I work on in v8.5.
This warning will appear in my preview window as soon as I open the report in my
environment, but will never appear to the customer. It works because I use each
customer's account number as a folder name to store all of their reports. This
helps because Crystal formulas can read the path and file name of the report
being run. So when I work on a v8.5 report I add logic to have it check for a
specific folder to control the appearance of the warning.
I create a new Report Header section that isn't otherwise used by the report.
I place a text object in this new section that has a large bold red warning ">>>
use v8.5 <<<"
I create a section suppression formula that says something like:
not "\1234\" in FileName
The 4-digit number is a
customer account number which is where the rpt is stored in my environment. It
is unlikely that the report will be in a folder with that same name in the
customer's environment. So the section will suppress in their environment but
show up automatically in mine the next time the report is opened.
I found on surprise while testing this. The FileName function is read
before the selection formula is processed, so you can use the folder name to
change your filter. And it is even processed early enough to affect the
SQL that CR automatically generates, so it doesn't have to slow down the report.
If you discover any creative uses for this, please share.
Fields that
disappear during "Verify Database"
All reports pull their data from tables of some kind, and these tables do change
over time. Fields are added or removed and sometimes field names or their data
types are changed. Crystal has the "Verify Database" feature to update the
report so that the table structure stored with the report matches what is in the
database. If a field used by the report is removed, renamed or has
been changed to a different data type, you will have to map the old field to the
new field in a small mapping window.
But I found an interesting exception. One of my customers had a character
field that was changed from a long string to a memo field. Apparently the
Verify Database 'mapping' feature can't see memo fields, even when they have the
exact same name as the original string field. So when I went to map the
original string field to the newer memo field, that new field wasn't listed.
I had to leave the field unmapped to complete the Verify Database step.
Normally Crystal will remove unmapped fields from the visible layout of the
report, but it can't remove them from any formulas. So normally when a
field is left unmapped it will generate an error when it is referenced in a
formula, because the field it references is no longer there. However in
this case the formula referenced a field that still existed under the new data
type, so the formulas all worked fine despite the fact that I couldn't map the
field.
So if you ever wonder why fields that exist in the table are not listed during
the "Verify Database" mapping step, one thing to check is if the fields are memo
fields.
One other interesting note about this case is that the structure of the table
hadn't actually changed. For some reason, when the report is run in the
design environment the field is seen as a string, but when the report is run
from within the application, the same field is seen as a memo. Once we got
the report working I told them that the behavior was probably because the
application used either a different set of Crystal drivers or a different
database driver. I haven't heard anymore about the problem so I am not
sure if they pursued it. If anyone knows more specifically what would cause this
behavior, please drop me a line – I am curious.
Other blog articles:
Working with
binary fields in SQL
Java script
error on the Crystal Reports start page
Gems from the Archives:
** The chart changes based on parameter values chosen?
(Volume 2003.11)
** Some Functions not allowed when writing custom Functions
(Volume 2003.11)
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