phone: (540)338-0194

Individual Instruction by Ken Hamady

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: 

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

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

Copyright 2014 by Ken Hamady
All rights reserved - Republishing this material requires written permission