The Crystal Reports Underground News Volume 2014.07
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for July 2014:
** RPT management utilities for 2014
** Crystal Reports and dashboards
** My library of Crystal Reports materials
** Automatically fill in "Overridden Qualified Table Names"
** Formula failures without error messages
** Let me create your Crystal Reports
** Syntax error or access violation
** Add a blank section, and the totals change?
** Open enrollment Crystal classes in Frederick, MD
** Source Code for RPT management software for sale
** Crystal Reports for Enterprise
Gems from the Archives
** Report File Name without the path
** Displaying values of multiple and
range parameters
Report management utilities
comparison (2014)
I have just updated my comparison of RPT management utilities for 2014. These are tools that allow you to scan, document, compare and update RPT files.
One vendor has closed shop since
last year’s list. Within a
month or so I expect one new product to be added and one existing product to
release a major upgrade. Also, one of the current vendors is looking to
sell the rights to their software.
The current list of tools is below:
Report Miner by the Retsel Group
Find it EZ Code Search Professional by Find it EZ Software Corp.
Report Analyzer by Cortex Systems
.rpt Inspector 3 Professional Suite by Software Forces, LLC
Crystal Reports and dashboards
My daughter always wants to try whatever the grownups are eating. When she was
very young she saw my wife eating something new and blurted out:
"I want summa dat … wad is dat?"
I often remember that when people call me about dashboards. They tell me they
need a dashboard, and then make it clear that they don't know what a dashboard
is. Often, someone higher up has heard the buzzword or seen something flashy on
a web site and decides that they want "summa dat".
So lets start with a definition of a dashboard. According to the dashboard
specialists at ExcelDashboardWidgets, a dashboard is a report that:
1) Fits on a single page or screen
2) Is graphical and easy to read
3) Shows Key Performance Indicators (KPIs)
4) Shows the current status in real time*.
Of course, "real time" varies based on the need. In a manufacturing plant "real
time" might mean every few minutes. In other business, it might mean once a day
or even once a week. Whatever the frequency, a dashboard report should function
like the dashboard in a car where you can see the speed, fuel level, temperature
and a few warning indicators if something else goes wrong. A dashboard report
should do the same for your business.
I am not sure everyone agrees with me on this. If you call someone at SAP and
ask about dashboards they will talk about "SAP Crystal Dashboard Design"
(formerly known as Xcelsius). This is for creating interactive dashboards where
you can twist dials and slide levers to change inputs. They can be fun
interfaces for exploring historical data or playing with future projections but
most of my customers have no use for these. I think that the essential purpose
of the dashboard remains simple, to show the current state of things.
I am also not convinced that you need another tool to create your dashboards.
Unless you need "eye candy" you can create pretty sophisticated dashboard
reports in Crystal Reports. Usually it requires a handful of subreports, each
reading data for a different metric. The subreports are usually presented as
charts that illustrate each metric visually. You probably already have this data
in your more detailed reports. You simplify these reports to become the
components in your dashboard.
Not that the process is easy. I think the biggest challenge in setting up a
dashboard is defining the list of metrics. You have to avoid the temptation to
squeeze in the data from every existing monthly report and focus on the key
metrics.
So, if you need some help working through the process, please give me a call.
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.
Automatically fill in "Overridden
Qualified Table Names"
This info will be useful mainly for developers who create reports that need to
be launched from an application. I have written before about property called "Overriden
Qualified Table Names" or OQTN.
Filling in this property makes it easier to point the report to a different
connection at runtime. The OQTN property starts out empty for each table. To use
it you go into the properties of a table in "Set Datasource Location" and enter
that table's name into the OQTN property. You have to do this for each table,
view or stored procedure in the report. It surprises me that this property isn't
filled in automatically at the time the table is added, like the table alias
property.
A few months back a user contacted me because they had over 100 reports and each
report had lots of tables. They wondered if there was a way to automatically
fill in all of the OQTN properties without doing it manually. I asked the
developers at FindItEZ and they have decided to add this feature to their new
release. So now FindItEZ can take a a folder of reports and automatically fill
in the OQTN property for every table, view and stored procedure used in those
reports.
For more information about FindItEZ and other RPT Management tools, see
my feature comparison.
Formula failures without error
messages
I cleared up a mystery today, one that I thought was just a Crystal glitch. It
explains why Crystal formulas sometimes fail without an error message to
describe the problem. We are not talking about invalid formulas. An invalid
formula generates an error when you save it. We are talking about a formula that
fails only when the report is run and it finds data that it can't process. For
example, a formula that divides one field by another is valid, but will fail if
the denominator field has a value of zero. A formula that converts
characters from a string field into a date will also fail if the characters
don't make up a valid date. Both of these failures would normally pop up an
error message. So what does it mean when it doesn't?
Take an example where formula A is used inside formula B, and then formula B is
in turn used inside formula C. If formula A fails then both of the others will
fail as well. Which one pops up on your screen? It is the one that the report
encounters first. And if that happens to be formula B or formula C then there
will be no error message. All you will see is a field highlighted in the
formula. That is the field that is causing the problem in this formula. So if
formula C pops up, it will highlight formula B but there will be no error
message. If formula B pops up it will highlight formula A with no error
message. But if formula A pops up, the error message will show.
So how can you tell which of the three will be encountered first? If they are in
different sections, the formula in the section that would be printed first is
the formula that will fail first. If the fields are all in the same section they
are encountered in the order that they were inserted into that section,
regardless of where they were located in that section.
So what do you do when a field is highlighted but there is no error message
telling you what to fix? Temporarily eliminate that field from the formula. Then
rerun the report. The formula that was highlighted should now pop up. If it
doesn't pop up, make sure that the formula that was highlighted is also sitting
somewhere on the report and run it again. If it pops up but still doesn't give
an error message then it will highlight another field. Repeat the same process
with this highlighted field, until you get to the formula that pops up an error
message. Then you know what needs fixing.
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.
Syntax error or access violation
I was recently dealing with this error message. It was generated by a report
that uses a SQL command with a parameter. The parameter was a numeric and when
we replaced the parameter with a specific number, the error went away. Putting
the parameter in the same spot generated the error. So as part of
troubleshooting I started taking out pieces of SQL until there was very little
left of the query, and the error remained. So we were surprised to find
that moving the same SQL to a new report worked fine, even with the parameter.
The customer was ready to recreate the report from scratch.
Then she made a comment that she had once deleted and recreated a parameter to
fix a similar problem. Unfortunately that hadn't worked for her today.
But this rang a bell, so I asked her if she had used the same parameter name
when recreating the parameter. She had, so I deleted the parameter and
created a new parameter with a different name. I used the new parameter in place
of the old one and the SQL ran correctly with no error message. It appears
that this parameter name, which she had used successfully in several other
reports, had somehow gone flaky inside this report. She tried to rename
the working parameter back to its original name and the same error popped up.
So the problem wasn't with the SQL Syntax or with the value but something
specific to that parameter name in that one report.
So if you run into a similar problem with a SQL Command parameter and nothing
else seems to fix it, here is one more thing you can try.
Add a blank section, and the
totals change?
A customer sent me a report today with an unusual behavior. There was a total on
the report and the problem was that the total would change when she unsuppressed
a details section and change back when she re-suppressed the details. Even
adding a blank details section made the total change.
At first I figured she had to be mistaken. I could think of no way that
suppressing a section or splitting a section could have an effect on totals. But
as I thought about it I realized there was one one obscure way. Both of the
changes she mentioned could change the number of pages. And there is one way
that adding pages can affect totals.
There is a property in the group expert that allows you to repeat a group header
on each page. This allows you to repeat the label of a group when that group
spills over to a second or third page. So say you are creating running totals
using variables. And also say that there is a formula that increments or resets
the variable in the group header. By adding more pages to the report you would
be adding repeated group headers, and this would repeat the variable assignment
more often than intended.
So I opened the report and located the total that was changing. As I suspected,
it was based on a variable. Then I checked the Group Expert and found that Group
1 had the option set to repeat the headers on each group. Last, I looked at that
group header and found the reset formula for the variable. When I changed the
group property to NOT repeat the header, the total no longer changed when I
suppressed or unsuppressed the section. So that was one solution.
An even better solution, especially if you really need to repeat the Group
Header, is to alter the formula so that it doesn't make a variable assignment in
repeated sections, but only in the original. There is a function specifically
for this that you use as follows:
WhilePrintingRecords;
NumberVar XYZ;
If not InRepeatedGroupHeader then XYZ := 0;
This would reset the variable at the
beginning of each group, but not when the Group Header repeats.
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 August 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.
No Crystal Reports viewer in Sage 50/2015 menu
A few months back I wrote about Sage (Peachtree) dropping the built in Crystal license from their 2015 accounting software. Today I heard from a customer who has already upgraded and realized something that I had missed. There is no longer the ability to run Crystal Reports from within the Sage menu. You can still write reports in Crystal if you have it. You can even use the "GetPeach" custom functions. But Crystal Reports can now be run only outside of Sage software.
This is important because it involves extra costs. To run the reports outside of Sage you either have to have a copy of Crystal Reports, or a third party report viewer. Since Crystal Reports is licensed by "named user" an office would need to have a separate Crystal Reports license for each person running reports – even if they all ran the reports on the same machine. Even with just a handful of users you would be talking thousands of new dollars. Fortunately these reports can also be run from third party viewer programs which are much less expensive. A viewer lets users open, refresh, preview, print and export reports. The difference is that the viewers don't allow you to modify the reports. So I expect to see Sage customers shopping for viewers in the next year.
If you are not familiar with the available viewers you can read my annual comparison.
Source Code for RPT management
software for sale
Cortex systems has two software packages that allow you to manage your RPT
files. They are:
Report Analyzer – Allows you to search, cross-reference and document your
reports
Object Compare – Finds all the differences between two objects, including RPT
files and several other object types.
The developers at Cortex are changing their focus and are interested in selling
the source code and IP for these two products. Anyone interested in finding out
more can Email support@cortexsystems.com.au.
Crystal Reports for Enterprise
I recently met with a customer to discuss a project using Crystal Reports for
Enterprise. This is the first time a customer has called about this
product, which is one of the reasons I haven't been in a hurry to experiment
with it. Another reason is that it is only available within BO Enterprise.
To experiment with it on my own would require configuring an Enterprise
environment, including a Web Server and getting some data configured within
Enterprise. Last, SAP is still in the process of incorporating all of CR's
features into this version, so it is a work in progress. The longer I put
it off, the more complete it would be. For instance, support for
subreports was added with the last release (4.1). But SAP has said that
eventually this will be the only version of CR. If they make good on that
I will have to migrate some day.
So, during the meeting I was able to check out a few of the standard feature
windows, like the group expert and the section expert. I found that most
of the same features are there, but it takes time to find them because the
interface is completely different. And we aren't talking about minor
adjustments. Most of the dialogues look absolutely nothing like their
older counterparts. Then there are some confusing new names. For instance,
in the Section Expert the old "Suppress" feature is now called "Hide" while the
old "Hide" feature is now called "Show only on drill-down". The Details
band is renamed "Body". Fortunately, the formula editor still seems to
have most of the existing functions and features, but UFL functions are still
not supported. If they hire me for the project I am sure I will have more
time to explore, and will catalog any missing features I find.
But I think the biggest challenge for existing CR users will be making
connections to the data, which is the first step in doing anything. The
new interface will be completely unrecognizable to an existing CR user,
since it is focused on connecting to Enterprise Universes and other types of
Enterprise data. There is currently no way to connect to any data that is
not part of the Enterprise environment, so even starting a simple report to
explore the interface can be a challenge.
If any of you have experience with the the new CR For Enterprise I would be
interested in your opinions. And if you have any specific features that
you have noticed are still missing, please let me know.
Gems from the Archives
Report File
Name without the path
Displaying
values of multiple and range parameters
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