I recently reread a newsletter article I wrote a few years back and was reminded of a trick that I had forgotten. The article was about ranking groups using a formula that contains summary functions. It is simple to summarize most formulas in a report (sum, count, maximum, etc). And once you have a summary field it is usually a simple matter to use that summary field to rank the groups. Crystal calls this a Group Sort (or a TopN in older versions).

The challenge comes if the formula itself contains a summary operation. Then Crystal will not let you do a summary of that formula, and then there is no way to rank the groups based on that formula. However the article I wrote points out two very important exceptions to this rule:  You can do a summary of these formulas either inside a cross-tab or inside a chart.

So take a common formula that calculates a profit margin percentage: Continue Reading »
Ranking based on a ratio

A few years back I wrote about the Stealth Subreport. This is a subreport that doesn’t appear on the report but does run in the backround, providing shared variables to the main report. One of the limitations I listed at the time was the need to have a visible section that occurs at the same frequency as the subreport so that it can be hidden ‘behind’ something. In other words you couldn’t have a suppressed subreport at the detail level but have it still run and generate shared variables for use in the Group Footer or Report footer.

But I just learned that this limitation goes away with v9 of Crystal Reports. For versions 9 and later there is a method that allows you to truly have a suppressed subreport that is run invisibly in the background. It stems from a new feature introduced with version 9 called “Suppress blank Subreport”. Normally this used in combination with “Suppress blank Section” to hide a subreport that doesn’t find any data

What I didn’t realize till this month is that “blank” can also mean a subreport that has all of its sections hidden or suppressed. So even if there is data, as long as there is no visible output the subreport is considered blank and will be suppressed. I also didn’t realize that using these properties to suppress the subreport does not prevent the subreport from running. Normally suppressing a subreport means that it won’t run. But apparently these blank subreports are run before Crystal determines that they are blank, so there is time to actually store and capture their shared variables.

Thanks to Phyllis Swenson of CFI who pointed this method out to me.

I recently posted a problem that I found with printing labels from Crystal Reports. This month I get a note from a software company that has done quite a bit of testing and is convinced that this is a bug with Crystal Reports. Here is his note. If anyone else has anything to add, let me know.

———————————————————-

Hello guys,

We are experiencing the same issue on both a zebra label and a Cognitive Advantage DLX printer on printing to a 2.3 x 1″ label.

We also found that printing on a 4×6 label, that the rotation does NOT happen but on random label sizes around the 2.3×1 label, it will indeed incorrectly rotate. I have been on the phone with the cognitive driver developers for a week and we have both agreed that the issue is a crystal reports issue in both XI and 2008 also in XP, Vista home, business, and ultimate editions of both 32-bit and 64-bit.

No matter what settings I set this to, any configuration of size, orientation, etc will not allow me to print a label correctly.

Like I said, using both of these printers, I can print the 4×6 label PERFECTLY, but on the 2.3×1.. it rotates 90 degrees.

I would like to help out as much as possible in this as we are a software company and have done extensive testing in this issue as well has have made high level contact at our distributor and the cognitive manufacturer as well. Please keep me updated on any advancements you guys make on this and I will do the same.. Any ideas how we can proceed to file a bug with Business Objects?

Thanks,
Benjamin Seidler, President
Stimulus Retail Solutions, Inc.

Some of you may have played with a technique that I have written about in the past. It is where you use a variable to create running totals within a cross-tab. These running total values can then be used within the cross-tab or even outside the cross-tab.

But I recently heard from a user who was having trouble whenever the cross-tab went over a page break. His variables would reset to zero on the new page and he thought it might be a bug in v10. So I did some testing and found that this behavior occurs in all versions, right up to CR 2008. Whenever a cross-tab goes over a page break it resets any variables it is using, even though the variable is global. The reset goes back to the value of the variable at the beginning of the split section. Usually this means a reset to zero. However in cases where the variable already has a value before the cross-tab section starts, it resets to that value.

There must be a bug or design flaw in the way the split pieces of a cross-tab are processed. Unfortunately, I could find no way to prevent the reset.

Thanks to Peter Lapic for pointing out this bug to me.

Here is a little secret that not everyone knows. The Crystal Reports formula editor has an autocomplete function that allows you to type the beginning of a function or other reserved word, and let Crystal give you choices to pick from. To invoke the autocomplete you type CTRL - Spacebar and a window will open with all of the reserved words in it. As you type the list will narrow down to the functions that start with the characters that you have typed.

So say you want to use the function InRepeatedGroupHeader. You do your CTRL - Spacebar and type “IN” and you get a choice of 10 options. But if you add the “R” there is only one available choice so that choice is entered into your formula for you.

Or if you start typing a function you can hit the CTRL - Spacebar and the same window will open showing what matches what you have typed so far.

This is available in versions 10 and later.

Many of you have purchased my guide to using Crystal Reports in VB (v6) or my guide to using Crystal Reports in VB.NET. In those guides I cover the basics of configuring and running a report from within an application. I also explain how to change the basic features of the report (groups, parameters, formulas, etc.) and how to log the report into a secured database.

One thing that isn’t currently described is how to log the report into a connection that is different than the connection used at design time. While the code is the same, there is one thing you have to do Continue Reading »
Overridden Qualified Table Names

The latest versions of Crystal Reports have a menu option in the “Report” menu called “Check Dependencies”. This is a handy little feature that allows you to see if there is anything that would prevent the report from running. For instance, I often find formulas that use a field or function that is no longer available.

How does a report end up with a missing dependency? Here are two examples: Continue Reading »
What does it mean to “Check Dependencies”

For those of you who do VB development, there are several choices for creating an installation file. The main commercial tools are InstallShield and Wise, but there is also a free tool (donations accepted) called Inno Setup, by Jordan Russell.

For those of you that create VB applications incorporating Crystal Reports, you should check out Inno Setup. One of the main advantages (besides being free) is that you can use a great little script generator for Inno Setup called InnoScript by Randem Systems.  InnoScript isn’t free but is a great deal for $25.

InnoScript is designed for VB developers and generates complete Inno Setup scripts directly from the VB project file. Best if all, InnoScript now provides full and automatic support for Crystal Reports versions 8.5 thru 11(XI). Randem Systems even maintains an archive of the Crystal redistributable files. You can download the correct Merge Modules in an installable MSI format. To quote Will Fastie, a freelance IT management consultant:

“This is a brilliant piece of work that is available nowhere else, at least as far as I know. I’m using this system now. Flawless.”

And thanks to Will for pointing out these tools.

A few years ago I wrote a newsletter article related to Excel exports. I described what you should do to prevent extra rows, extra columns and merge cells when exporting to Excel from Crystal Reports. Just last week I found another piece of the puzzle that explains why my methods would occasionally not eliminate all of the extra rows. It appears that if your row of objects in the report is not at the very top of the section (position 0) then Crystal will create double rows in the spreadsheet. Even if all the objects are the same height, and all are aligned in one clean row across, a tiny space above the row of objects will export to a spreadsheet with data on every other row. Read the article above for more information on cleaning up your Crystal Reports exports.

I worked with a customer yesterday who had a Zebra TLP 3842 printer to print individual labels. They recently upgraded from CR 9 to CR12 (2008) and with that upgrade they lost the ability to print labels correctly. After many attempts to isolate the problem we got down to this.

1) The preview in CR12 is correct, but the label contents print rotated 90 degrees. It seemed like an Continue Reading »
CR 2008 doesn’t like Zebra Label Printers?