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

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.

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

(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

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.

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.