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 maintain a list of commonly used formulas on my site and some of these formulas go back nearly 10 years. I have started to notice that some of the older formulas can be done more simply using features in the newer versions of Crystal. So I will occasionally update a formula to simplify it.

For example, one of the first formulas I wrote converts a character date into a true date. The original worked for text stored in formats such as M/D/YY or MM/DD/YYYY. The challenge back then was determining if the month or day was 1 or 2 digits and the method I used was to look for the positions of the slashes. This job is made easier now because we can use the SPLIT () function. So I have finally updated the formula. I also added formulas that convert values in “YYYYMMDDHHMMSS” or “YYYYMMDD” formats.

If you group on a date field in Crystal Reports, the groups are always based on calendar periods. So a group by year will be by calendar year and a group by week will be by calendar week (Sunday to Saturday). If you want to group by a non-calendar period, like a fiscal year, then you have to write formulas. I have BLOG entries that show you how to group by fiscal year and fiscal quarter.

One of my former students asked me today how to group dates into weeks that aren’t calendar weeks, like their pay week that starts on a Monday and ends on a Sunday. Continue Reading »
Grouping by non-calendar week

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

Traditionally, a CSV file would not be expected to have field or column headings on the first row. But this has been a requirement in a number of environments that I have worked in. Providing this has been a challenge until recently. In most Crystal environment, a CSV export would take every visible object on the page and make it into a column. The last column is often a column of ones, created from the page number in the page footer.

Up through CRvXI release 1 it was possible to get column headings to print as the first row of the CSV export, but it was a complicated process. It involved modifying the report, installing a special driver and even tweaking the registry settings for Crystal Reports. The steps are outlined in knowledge base article c2014451 which is no longer available.  I found the same steps in a Forum Article that seem to be the same thing.

If this is a regular requirement in your environment, then you should seriously consider an upgrade to XIr2 or CR 2008 (also known as versions 11.5 and 12). I just noticed that the CSV export in these versions has a new check mark asking if you want to ‘isolate’ the Page Headers. If you check this property the values of the page header become the first row of the CSV export.

Note that if you are on CR v8.5 you may need an updated copy of u2fsepv.dll. I think the minimum version of this DLL that supports this method is 8.6.2.475. If you can’t find the DLL I can help there as well.

Say you have 2 group levels in your report Customer and Product. You have subtotals at the end of each group, so you have 2 levels of subtotals. What do you do if you want to have a page break after each product? Your first attempt would be to simply go into the section expert for GF2 and check “New Page After”. This works fine except for the last product in each Customer. The Customer’s subtotals would end up printing on the top of the next page, which would be a different Customer’s page. So you add Continue Reading »
Cascading page breaks (on Interior Groups)

Crystal will allow you to conditionally suppress a Page Footer. But even when the section is suppressed, the space it would have taken up is still there, reserved as blank space at the bottom of the page. This is because other features like ‘can grow’ and “keep group together” make the job of determining the page size very complex.

There is a related option for page footers called “reserve minimum space” but this only applies Continue Reading »
Conditional Page Footer

The start page in Crystal Reports v11 and v12 is a web page. It pulls up dynamic web content from the Business Objects web servers each time you start the program. If you are not interested in this content or have other reasons not to want Crystal requesting a internet connection, there is a simple method of preventing it. Continue Reading »
Prevent Crystal Reports from requesting an Internet connection at startup.

There are three ways to move/size individual objects in Crystal Reports.

1) Dragging with the mouse. To move an object you drag the object by its middle. To size the object you drag the sizing handles on the sides or corners.

2) Using the “Size and Position” property available through the right-click menu. You can enter or change the coordinates for the position of the upper left corner as well as the height and width.

3) This one is lesser known. You can click on an object and move it by holding down the CTRL key and clicking one of the four arrow keys. What you are moving is the upper left corner, but since the size of the object isn’t changing you are really moving the whole object. To size the object you hold the SHIFT key instead and use the same four arrows. This moves the lower right corner of the object without moving the upper left corner, thus changing the size.

But there are two limitations to be aware of when using this technque:

1) If the object’s right side or bottom has touched a guideline and ‘attached’ itself, the SHIFT-Arrow will no longer change the object’s size. You can, however, still move the object with the CTRL-Arrow.

2) If you select more than one object, AND you are in preview mode, AND you hold down the arrow key to move the objects continuously, then Crystal Reports will almost always crash. Thanks to Gordon Portanier of Crystalize in Canada for pointing this latest behavior out.