The Crystal Reports® Underground

I have struggled many times while helping customers create fixed length text files from Crystal Reports. These files are standardized files used to transfer data from remote systems into centralized systems. Banks, insurance companies and government agencies often require certain business to submit data in fixed length files so they can be imported into legacy applications. The challenge has always been Crystal Reports wanting to truncate the data based on the defined page width, even though the export doesn’t actually go to a physical printer.  I recently did days of research to write an article on how to get the widest possible text export from different versions of CR.  This is published in my Expert Techniques volume 4.  Exporting to text with this method is limited in CR 2008 to 367 or 377 characters depending on your version of the export dll.  Some older versions of CR can go up to 1500 characters if you get all the right pieces in place.

But I was talking with Ido Millet about scheduling these exports when I realized that another of his tools has a very simple solution to the problem. Millet Software produces a DLL called
Continue Reading »
Fixed length text exports using Cut Light

Have you ever wanted to:
1) Find all the reports that use a particular table, field or calculation?
2) Compare two versions of a report to see what was changed?
3) Make the same change to a group of reports?

The more reports you have, the more likely that you will face these challenges, and you can solve problems like these with the tools in this article. All of these tools can be found in the LINKS page of my site under RPT Management Utilities.

Below is a brief rundown on the core features of each product as well as what sets each product apart. I have also posted a more detailed
Continue Reading »
RPT management utilities

I just read a blog post by Blair Wheadon of SAP.  They want to encourage migration from Microsoft SQL Server Reporting Services to Crystal Reports so they have introduced a way to run SSRS reports from within the CR Server environment.  This is intended to cushion the transition and means that you can get your reports and dashboards accessible from one system.  Note that this does NOT import the SSRS reports or convert them.  It just links to them so the run in the SQL Server environment.

What I found interesting is Blair’s explanation of how CR Server is a better solution – even going so far as to claim a lower TCO.  That is an interesting claim when you consider that SSRS is free for most users who are already paying for their SQL Server license.   If anyone would like to weigh in on either side of this issue, let me know.

I just heard from Roberto Brum from Brazil who found a way to simplify my Formula #19.  This is a formula that you can use to find the last Friday of any month (or any other weekday).  I liked his approach so I put it side by side with my original formula for testing.   For the most part they worked exactly the same, and the few differences I found were due to my own errors in the original formula.   So, not only is it more elegant, but it is also more accurate.  So I have now posted Roberto’s formula on the page above.  Many thanks, Roberto!

A customer had a line chart with square markers on the line.  We went to change the size of the markers using the “Appearance” tab under Chart Options.  We found we couldn’t make the change stick. We could change other properties  (marker shape, line thickness, line color, etc) but changes to the marker size were ignored.   So I added a new chart and found the same behavior, which really had me puzzled. I finally went through screen by screen and found that new charts have a check-mark setting in the Chart Expert (Options tab) that says “Auto Arrange”.    When this is checked the Marker size on the “Appearance” tab is locked.  Taking out the check mark unlocked the property.

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

I am writing this because I have found little meaningful documentation on how to report on XML data in stand alone CR.  I would be thrilled if someone wants to show me where it is hiding.  But here is what I have found from trial and error.

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.