In most cases, subreports are a last resort. Typically they slow things down by adding an extra query to the process. But this week I found that moving some tables to a subreport actually sped things up.

The data came from the fundraising software Raisers’ Edge, which uses data exported to an MDB. The customer had designed a new report and found that it ran for over an hour without completing the query. Nothing looked wrong in the structure so I did some troubleshooting. I started with one table and then added the other tables a few at a time to see which table was the problem. All was fine until I reached the last 17 tables which were all linked back to a single table. We only needed one record from each of the 17 tables and they all had about 500 records.

I was able to add the first three tables without issue, but beyond that the report would slow down more with each table added. It only took a few more tables to realize that we couldn’t add all 17 tables to the report and expect it to complete. I double checked the links, confirmed the indexes were in place and still couldn’t find any cause for the slowdown.

Finally, I removed those tables from the report and created a subreport that included just those tables. I also included the table that linked them all together. The subreport ran instantly both on it’s own and when inserted in the main report. My guess is that the MS Access engine was struggling with the number of joins, so splitting them into two separate queries made it more manageable.

I have a customer who had me create report with a complex layout and lots of optional sections. In addition to using the report itself, he wanted to export the report values into a format where they could be read by another program. We decided to use XML so that the program could search for specific field name tags and extract the matching values. When we started testing the program we noticed some discrepancies between the report values and the XML export values. It mainly had to do with variables that were accumulated in the details and then displayed in the group or report footers.

It took over an hour to identify the root of the problem. Even after fixing the problem I couldn’t explain it, so I created a very simple report to test it. It had one group, and the the following two formulas:

//Accum
WhilePrintingRecords;
NumberVar Accum;
Accum := {Customer.Customer ID}

//Display
WhilePrintingRecords;
NumberVar Accum;

I placed the Accum on the details band and hid that section. I placed the Display formula on a group footer. This exported to XML and showed all the Group Footer values correctly.

Then I split the detail band into A and B subsections. Both subsections were still hidden. The preview of the report looked the same, but when I exported to XML all the group footer values in the XML were zero. It didn’t matter if I put the Accum formula in Details A or Details B. Whenever the Details section was both SPLIT and HIDDEN, the Accum formula would increment correctly in preview but NOT for the XML.  If I used “suppress” instead of “hide” the Group Footer values exported correctly to XML. But even though the details did not appear in preview, they would now be included in the XML.

This behavior looks like a bug to me.  That means there may be other situations where variables don’t behave correctly in XML. So, if you are going to use XML exports, and you are using variables in your report, you need to test the output carefully to confirm that the variables export correctly.

Update 6/21/2019: I found a second scenario that generates the same issue. When the section is split and the parent section above the split sections has a conditional suppress formula. This causes the exact same behavior.

Update 12/19/2023: The same issue occurred today when the details were split and the parent details section was suppressed – even though the formula accumulating the variables wasn’t located in the details section.  Moving the suppress check mark to the individual subsections resolved the problem.

Ido Millet at Millet Software has recently added some unique new functions to the Cut Light UFL. Cut Light now adds 130 new formula functions to Crystal Reports. Here are the ones most recently added.

1) Creating charts from formulas.
This includes advanced gauges, sparkline charts (bar and line) and bullet charts. These charts are generated in real time by a formula function. The functions do not rely on Crystal’s integrated chart engine. They are generated as image files which are automatically read into the report. These charts are simple and have no customizable features. There is also no drill-down capability. But these charts can be generated on any series of numbers, including numbers that come from print time variables or even shared variables . Ido has posted some image examples: And you can read the details in the user manual.

2) Image cropping.
Allows you to read an external image file and crop it before displaying it in the report. This can be used with the chart images above and also with existing Cut Light functions that allow you to read the image properties and resize an image. Here is the relevant section of the Cut Light user manual:

3) Google sentiment analysis.
This allows a formula to process a block of text through Google’s “sentiment analysis” engine. The result will be 2 numbers which represent the score (positive, negative or neutral) and the “emotional magnitude”. This can be used to evaluate any comments, Email messages or product reviews you have in your database. It does require that you have an ID to use the Google API, but there is no charge for the first 5,000 calls per month.  You can read more details in the user manual.

If you write Crystal Reports against Accpac, now called Sage 300 ERP, your reports have probably seen or used the function pwFormatDate(). This is a custom function installed by Accpac that converts the numeric dates in Accpac into true date values. The raw data is an 8 digit numeric like 20161231 which is converted into a date by a formula like this:

pwFormatDate ({BKTRANH.TRANSDATE})

Sometimes my Accpac customers will try to send me a report saved with data. But because I don’t have Accpac installed, I don’t have the dll that provides this custom function (u2lcapw.dll). Crystal can’t load the saved data because the formulas that use this function generate errors. In researching this topic I have found that even some Accpac users get this error, despite the fact that the dll is installed.

Fortunately, you don’t need the dll or the custom function to convert these numbers into dates. I have had formulas on my web site for years that convert string and numeric dates into true dates. I just recently updated the formula that can replace pwFormatDate(). The formula above could be replaced by the following formula. It is longer, but it doesn’t require installing or troubleshooting any dlls:

Local NumberVar input := {BKTRANH.TRANSDATE}; // use your field name
// This line checks for a minimum value, any value will work.
Local StringVar DateString :=
If input < 19500101 then '19500101' else Totext (Input, 0, '');

Date (
Val (DateString [1 to 4]),
Val (DateString [5 to 6]),
Val (DateString [7 to 8]) )

Also, using this formula instead of using pwFormatDate() allows your reports to be opened with saved data on any PC that has Crystal Reports. So, if you are getting errors that say this dll can’t be found you can convert to using the formula above and be done with the dll.

Update 10/12/208:

Ido Millet of Millet Software suggested an alternate approach:

Local NumberVar input := {BKTRANH.TRANSDATE}; // use your field name
// This line checks for a minimum value, any value will work.
If input < 19500101
then Date (0,0,0) else
Date(Picture(ToText(input,0,""),"xxxx/xx/xx"))