The Crystal Reports® Underground

This week I had 4 different customers ask about the same problem – duplicate records in a report.  After explaining the process I use to troubleshoot this for the fourth time I decided it might make a good BLOG article.

1) The first thing I do is check the joins to see if one of them is obviously wrong or only partially complete.  A partial join is a join that needs 2 or more fields linked, but where only some of the needed links were made. 
Continue Reading »
Dealing with duplicate records or “table inflation”

One of my customers sent me a report with an unusual “divide by zero” error. I was going to explain to her how you prevent this error with an IF-THEN statement.  You check to make sure that the field you are dividing by is not zero, like this:

If {FieldA} = 0 then 0 else
{FieldB} / {FieldA}

But when I looked at her formula I noticed that the first line did exactly what I would have done, and it was still generating a divide by zero
Continue Reading »
Formula trouble using “Integer Divide” (the backslash)

According to this article on vnunet.com, 90% of SAP users surveyed at the Sapphire user group conference last month are experiencing monthly performance problems, with most of those reporting 3 or more performance problems each month.  The article’s author asked SAP  to comment on this but they haven’t yet.  So if you are having performance problems you are not alone.

One of the challenges of creating reports is finding the data that you are looking for.  This is especially challenging if you are working with a very large and complex database, like an ERP system.  So if you have to create reports against a Lawson database, you might want to check out lawsonguru.com.  They provide an online copy of the searchable structure documents called dbdoc and dburf, and they have this for several versions of Lawson.  They also have some forums and a job posting board.  Some areas require a (free) registration.

Many of you have purchased one or more volumes in my Expert Techniques series. These contain my favorite tips and tricks in Crystal Reports, accumulated over the years. I have just put together Volume 4 with another 30 articles. Each is illustrated with an annotated report that demonstrates how to use the technique.  As always, they cover a wide range of topics.

Some are specific advanced methods like:
How to do a fixed length text export from a Crystal Report (#115)
How to do a multi-level Bill of Materials (#118)

Some are little formatting tricks like:
How to automatically size the dotted line between two columns – like a TOC (#105)
Lines and boxes that grow or disappear based on a condition (#107-108)

Some are general purpose lessons like:
Different ways to use “feeder” formulas (#94-95)
Tips for naming formulas (#91)

Check out the full list of articles that are in Volume 4. Even if the article titles don’t seem to apply to your reports, I am confident that you will learn some tricks that you will find useful down the road. The price is only $19 per volume – a bargain even if only one article helps you solve a problem.

The 2009 SAP BusinessObjects User Conference is being held in Dallas this year on October 18-21, 2009. You can read more about it on the official site. The cost is $1,495 at the door, $1,395 in advance and $1,195 before 8/21/2009.

If you want to go, and aren’t sure you can get that amount approved, you can always download their handy “Conference Justification Document“.  This is a boilerplate proposal (fill in the blanks) that you can submit to your manager to convince them that the conference is worth the money and time.  The first paragraph ends with my favorite line:

” This content will be directly applicable to my work on <project(s) you are working on>. ”

I won’t be attending the conference but several of my colleagues are regulars.  If you decide to go let me know if you found it worthwhile.  If, like me, you decide not to go I can think of several ways to spend that amount of money and truly make it ‘directly applicable’ to the reporting project that you are working on.

A recent change to the process of getting BOCP certifications has caused some confusion among users, as indicated by this Forum Thread.  It appears that you now need an SAP User ID before you can register for any certification exams on BO products.  Everyone knows that I am not a big proponent of the BO certification process, but if you do decide to register for an exam, say through the Pearson Vue site, you will now need an SAP ID to do so.

If your company has a support contract you may already have an SAP ID.  If not, you can get one from SAP Central Registration by calling (888) 777-1727.

If anyone has more information to add on this change please let me know.  And thanks to Gordon Portanier of Crystalize in Toronto for tipping me off to the change.

IT World Canada just published an article on the games that SAP/BO is playing with the proposed new CR license terms.  The article includes some history and feedback from instructors, analysts and publishers.  I found it interesting and learned a few things.

Some of you are probably familiar with CrystalReports.com (known as CRDC).  This is the site sponsored by SAP/BO where you can host your reports and make them available on the web.  When CRDC was introduced a few years ago it only allowed you to share reports that had saved data.  Then it changed to allow you to refresh reports if your had a server product like CR Server or BO Enterprise.  More recently they broadened this to refresh data from your SalesForce.com database.

I just learned recently that Crystal Reports.com now has the ability to refresh reports from pretty much any data that can be made available to your web server, which makes a workable solution for some users. You have to install some software on your server which allows CRDC to reach behind your firewall.  This software
Continue Reading »
Refreshing reports hosted on CrystalReports.com

According to this document posted by Blair Wheadon of SAP there is a significant incompatibility between CR 2008 SP0 and CR 2008 SP1.  So much so that they do not recommend that the two Service Packs be installed together.  The document explains which environments should be installing which service packs, and what to do if you have accidentally installed both.

The problems seem to affect primarily application developers and those using SAP server level products like CR Server and BO Enterprise.  Service pack 1 has been pulled off the site and will be released again (or ‘refreshed’) with some fixes.  However the planned fix doesn’t seem to address the incompatibility.  It will simply check your environment and prevent you from installing SP1 if there is an incompatible product, like SP0, installed.

And for those of you who aren’t sure which SPs (if any) are installed in your PC, there is a link that lists the version numbers for each service pack in an earlier post.