The Crystal Reports® Underground

There is a trick I teach my students to help them remember Boolean (true/false) syntax.  I have them enter criteria into the select expert and then click the “Show Formula”  button to see the same criteria written in Crystal formula syntax.  So if you forget how to write a formula that does “between”, “is one of” or “not equal to” just put that into the select expert and copy the formula that it generates.

And just last week I learned something new.  The Select Expert will let you mix ranges and single values when using the “is one of” comparison.   So say I want to return 62 specific order numbers.  I want all orders numbered from 1001 to 1030, all orders numbered from 2001 to 2030 and two other individual orders numbered 2035 and 2037.  In the select expert you can put all of this in one rule by
Continue Reading »
Mixing single values and ranges in the select expert

A few months back I was invited by SAP’s PR group to be one of the “Crystal solutions experts” in an SAP sponsored competition.  The goal was to promote the CR product line and let some experts show off their reporting skills.  I was intrigued.  But thanks to my customers I have had a very busy year so I declined to be an official participant.   Instead I thought I would review the challenges and compare my own approach to those of the official participants.

So I was a bit disappointed when the project finally went public as the Reportapalooza.  Despite the name, the primary creative challenges are now for interactive dashboards rather than for reports.  Since very few of my customers are currently focused on dashboards I probably won’t be following this as closely as I had planned.  But I did read the PR group’s case study of the campaign and I found that even more interesting than the project.  I will be curious to see how effective a social media campaign is in the BI market.

One of my customers had a dozen or so formula fields used as headings throughout a long report.  He wanted to be able to adjust the color of the font assigned to all of these objects in one place.  So we created a formula that stored three numeric values like this and put it into the report header (suppressed):

WhilePrintingRecords;
NumberVar RGB1 := 0;
NumberVar RGB2 := 64;
NumberVar RGB3 := 128;

Then we used the following as the condition formula for the font color
Continue Reading »
Setting the RGB color of multiple fields at the same time

This is a follow-up to an earlier post about reading XML File with Crystal Reports.  One of the main things I mentioned at the time was the lack of documentation.  I have since found the following articles that have helped a great deal:

Structuring XML Data for the Crystal Reports XI Native XML Driver

Using ODBC XML Data with Crystal Reports (version 10 and prior)

If these articles are ever moved you can search the SAP Support site and it should bring them up:

Another thing I mentioned was that out of three XML connection methods offered by CR, only one could read both the XML and XSD files.  If you don’t have both the XML and the XSD then you get either:

1) the data types but no data, or

2) the data with all fields being character data.

But what I missed is that instead of a separate XSD file you can embed the entire XSD schema inside the XML file.  One of my readers, Guy Francoeur, was nice enough to point this out and even sent me an example.  I have since found that this is called an “Inline” schema.  So I used Guy’s example to create my own example which you can view or download if needed.  Crystal can connect to this XML file using either the “ADO.NET (XML)” connection method or the “Database Files” connection method.  And either way you get both the records and the defined data type of each field.

My wife was reading something called “deal news” and told me that there was a free download for something from SAP.   It turned out to be Xcelsius (under its new name).   I downloaded it  just to have it but I probably won’t have time to play with it for a while since CR consulting is keeping me very busy.  But if any of you are interested in playing with Xcelsius you can try it out for free.  Note that it is no longer called Xcelsius, but is now known as “SAP Crystal Presentation Design”.  Also note that a promotion like this usually indicates a new version of the software in the wings.

In general, I think interactive dashboards
Continue Reading »
Free download of Xcelsius

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.