The Crystal Reports® Underground

I have faced this twice this month with two different customers.  In both cases we design the report to use a numeric field and the report runs fine at design time.  But when the report is run within the application it generates a formula error saying  “a number is required here”.  What is happening is that the data type at runtime is changing so that fields that were numeric at design time are seen as strings at runtime.  The source of the problem for one customer was an Excel spreadsheet data source.  Excel is notoriously flaky when it comes to data types.  The other customer had XML data where the XSD was missing at runtime  so all fields became strings at runtime.  (See this article for more on XML and XSD Files).

In both of these cases the customer didn’t have the flexibility to change what was happening at runtime, so we needed a workaround.  Fortunately there is a trick I recently learned
Continue Reading »
Dealing with data types that change at runtime

It is very easy to change the format of numeric fields that appear on the report.   Even a numeric parameter that is placed on the report can be formatted just about any way you can imagine.  But for some reason the format of a numeric parameter “List of values” (LOV) is much more difficult to control.  Users get annoyed when a numeric parameter input for an account number or some other numeric code is formatted with commas and (in some cases) decimal points.

In most versions up through v10 there was one less-than-ideal way to control of the format of numeric
Continue Reading »
Numeric parameters without commas

(For an update to this post, read my follow-up article)

When you create a connection to data in Crystal Reports there are 3 different methods to connect to XML data.  The first two (“ADO.NET (XML)” and “Database Files”) allow you to connect to a single XML (data) or XSD (structure) file on your local PC. If you connect to an XML file then you can read the data, but all the data types will be strings.  If you connect to an XSD you will see the data types specified in the XSD but
Continue Reading »
Reporting on XML data

There are two “special fields” in Crystal Reports that allow you to print the date or the time of when the report file was last modified.  Any change to the design of the report should cause these fields to update. But Gordon Portanier of Crystalize in Canada just pointed out an apparent bug to me.  If a report has a subreport then the modification date and time update every time you open the report.

So say you saved 2 reports last night, one with and one without a subreport. They both had the special field “modification date” in the report header.   Then you open and run them both this morning without making any changes.  The one without a subreport will show a modification date of yesterday, which is correct.  The one with a subreport will show a modification date of today.   Every day you run a report with a subreport the modification date/time will show as if a modification was made today.  This behavior exists in all versions of CR that I tested – from v8.5 up through v12 (CR 2008)

I found an exception that I thought would lead to a workaround.  Say that
Continue Reading »
Modification date/time bug

This may not sound like much of a change, but I think it is important because it signals SAP’s commitment to the Crystal Reports platform and the value that SAP puts on the product.  According to this blog post by Blair Wheadon, Crystal Reports has now been promoted to a full ‘portfolio’ instead of just being a piece of the SAP/BO product mix. Xcelsius is even being re-branded as “SAP Crystal Dashboard Design” (DD) which clearly shows which product is considered the long-term brand anchor.

One comment made me smile. All of the products are now following the SAP naming convention with the goal of allowing us to “tell what the product does (at a high level) from the product name”.  What a novel idea.

Also tucked in the FAQ section was a note that the next major release of CR won’t be until 2011 – a bit later than I had expected.

Percentages in Crystal behave slightly differently than they do in Excel.  Say you calculate 13/21 and get .619.   In Excel you can click the percent symbol on the toolbar and get the value to show as  61.9%.  And if you multiply another value by this you will still by multiplying by .619.

In Crystal, clicking on the % on the toolbar does not move the decimal over for display.  So if you have the same calculation of .619 and you click the percent symbol it will simply add the symbol, showing as .619%.   If you want the displayed value to shift the decimal you have to
Continue Reading »
Percentages in Crystal Reports vs Excel

Drill-Down is a wonderful feature, but it can confuse those who don’t expect it.  Even when you suppress sections a user can drill-down to an empty screen and not realize what they have done.  Or they will accidentally drill down when the details are not hidden.  They end up on a new tab and don’t know how to get back.

The simplest solution I have found is to cover all the fields in the group header or group footer with a single large text object and make sure it is in front of the the other objects.  Crystal won’t drill-down on a text object so putting this in front of all of the fields prevents the user form using any object in this section to launch a drill-down  – intentionally or accidentally.

And if your users will be running their reports from within CR it is also a good idea to make the text object “Read Only”.  This way they won’t double click by mistake and edit the text.

I have divided the dozen or so scheduling tools into two groups – desktop and server. Most people who need to schedule reports will be fine using a desktop scheduler, even if they have to deliver reports to many recipients.  You can read my previous blog post for a comparison of desktop schedulers. But in some environments there will be many people scheduling reports and in those cases it might make sense to upgrade to a server-based scheduler.  This post will compare the server-based scheduling tools and should help narrow down your search.  So below is a brief introduction to each product and a link to the vendor’s website.

I have also created a detailed feature matrix (PDF) that compares roughly 70 characteristics. It includes prices and the install base of each product. And with so many complex features I decided to also write a feature glossary to define some of the key features so the comparisons were clearer. What has emerged is a short course in Crystal Reports scheduling technology. If you
Continue Reading »
Server-based scheduling engines

There is a handy grid in a blog post by Ludek Uher.  It shows all the different SDKs and which versions of Crystal Reports supported them.  So if you have to upgrade a Crystal related application, and you want to know how far forward you can go and keep within the same SDK, then this grid should help.  Users have suggested that he create some similar grids.  If he does then this link will be even more helpful.

I don’t go off-topic much, but this article is one I thought worthwhile.  It is because I am constantly amazed at how little value people put on their time (or their employee’s time) when making decisions.  Maybe because I am self employed and bill by the hour I am more sensitive to wasted time than most people.

This article from the Boston Globe covers a study that shows that sometimes the security measures we are forced to follow aren’t worth the time they cost us, which is probably why compliance isn’t always complete.