Using Specified-Order Grouping in Crystal Reports

As we get into some of the more subtle ways to use grouping in Crystal Reports, we sometimes find that either groups of groups, or arranging groups in a hierarchy, can improve the usefulness of the report. One of the overarching keys to a good report is that it be organized so as to make it easier to understand, and therefore more useful. This is even more important given the size of the batches of data we work with now.

A good example would be customer sales “levels”—gold, silver, etc. based on how much a customer spent with a company.

Basic Report

We can start by creating a report with the necessary data, in this case the company name, country, and sales amount. (In a real-world situation, one would include whatever data the grouping should be based on.)

Insert Grouping

Then, we can insert a group (Insert–>Group), but we use the In Specified Order capability to create arbitrary-but-logical groups based on (in this case) sales amounts. This is where we can customize the grouping using any reasonable criterion we want. The example here is sales levels, but any data from the database can be used. It can even be categories of items sold rather than quantities, just so long as there’s some way to tell one group from the other.

Set Up Group Names

We set up names for the groups using the Named Group box, and limits (in this case monetary) in the Define Named Group box which pops up for the purpose.

Other Customers

We also need to make sure we set up a space for the customers which don’t fall into one of the three “main” groups. (“Occasional”, “Other”, etc., are fine.)

Grouped Report

Once we rearrange the labeling a little for convenience, and maybe sort the customers by name, we’ve got a decent grouping set up. One thing to consider about this (and many other kinds of business documents) is to try and have a good idea of the final layout or structure in advance, if possible. It’s a lot easier if you know where you’re going, and you have a decent map.

Conditionally Formatting Multiple Sections in Crystal Reports

Alright, I admit it—this item on conditionally formatting sections has a little nostalgia for me, but it’s useful nostalgia. If you run a web search for “greenbar paper” you’ll see where this is going. The reason is a sound one, though. The horizontal stripes, sometimes called “banded rows”, let the user see what’s in which row of a report, or other printout, more easily.

Insert section below

The first step, after setting up the report and laying out the data, is to create a Details “b” section in the report—with no data in it. Right-click the section name, and “Insert Section Below”.

Borderless box

The only thing we want to do there is create a horizontal box, borderless, of any light fill color (since it’s going behind some of our data) in the same place as the data will be. Take your time when drawing, as the box should be the same height as the section, or just a hair less.

Conditionally formatting

The slightly more complex part comes with the conditionally formatting portion of the recipe. We want this color background to show up behind every other record. Luckily, the formula is simple. We go into the Section Expert, click the X+2 button for “Suppress (No Drill-Down)”, thus into the Formula Editor for the section, and type RecordNumber Mod 2=0. Basically, this function looks for even-numbered records/rows by doing the division, and if the remainder is 0, the color details section is suppressed. So…on, off, on, off. The conditionally part being, even- versus odd-numbered rows.

Underlay following sections

Finally, still in the Section Expert, we go to the Details A section and check Underlay Following Sections, so as to get them to overlap (or underlap, as the case may be).

Completed work

Granted, we don’t usually print much on 15-inch wide pinfeed carriage paper in high-speed dot-matrix printers anymore, but with the density of content in some reports, stemming from smaller print sizes and such, you never know when this trick might come in handy.

Ah, nostalgia. “Everything old is new again.” And still useful.  😉

Creating Report Alerts in Crystal Reports

In Crystal Reports, a feature called Report Alerts has become more important the last few years. It has to do with the fact that there are at least occasional exceptions to many kinds of report data. “Problems or conditions outside the norm” is a good way to phrase it, and when those happen, we often need to know about them ASAP. Hence Report Alerts.

Creating one isn’t hard—we just have to know three things going in. [a] What to call it, [b] what condition triggers it, [c] what message to show when it kicks in—this last is optional but strongly recommended.

Report Alerts box

Looking for a low order count is a good example. Having set up a field to count orders per company, we can now set up an alert to see who placed less than, say, thirty orders for the year in question. In the Report menu, we slide to Alerts, then click Create or Modify Alerts.

Create Report Alerts

Next thing is to set up the actual alert. We click New on the right side of the box, give the alert a name, and write a user message—some people skip this, but having at least a brief message is important, as you never know who’ll be using the report.

Report Alerts condition

Then, most important, set up the condition which triggers this alert. (Back to our old friend, the Formula Editor.) In our case, we’re looking for suppliers who placed fewer than thirty orders this year, so the formula reflects this.

Refresh for Data

When done, we OK or Close out of all the boxes, save (desirable), and refresh (F5).  We should see the alert kick in, with the option to let us know which records triggered it.

Report Alerts result

Here we can see the number of records has narrowed down to only eighteen pages’ worth, rather than fifty-nine, and at least one supplier’s data confirms only twenty-three orders (less than thirty). So the alert lets you know something’s different, then allows you to see what the changes are.

As is often the case, the hard part is getting the details clarified. Someone said, “Computers don’t do vague.” It’s a point to bear in mind with Report Alerts.

Creating and Using Parameter Fields in Crystal Reports

When setting up layouts in Crystal Reports, the parameter field can be a tremendously helpful feature. As with a parameter query in Microsoft Access, it gives the report a significant degree of flexibility—the report’s basic structure and purpose are the same, but the ability to change, say, the range of dates involved, means a single report can be used to answer a larger number of related questions with reasonable efficiency. Since the user can input anything the field allows, parameter fields simply need to be set up for the necessary data type.

New Parameter Field

Once you have a file open, you start by going to the Field Explorer, right-clicking the Parameter Fields item, and clicking New.

Name Type Static Dynamic

The dialog box which opens will ask for the name, type (of data), and whether the field should be dynamic or static—meaning whether it should get choices from the database, or from a separate list you load in.

The critical thing is, if you want to hook the type to a value from somewhere, regardless, the type of the field and the type of the value have to be the same—number to number, string (text) to string, and so on, for the same reason that two railroad cars have to ride on the same gauge rails and have the couplers at the same height. If they don’t, they can’t work together.

Prompt Text

Once you’ve told the field what kind of data, there’s one other thing you should do—enter a prompt text or phrase. Most times a user will know what the input should be about, but setting up a prompt is still a good idea, since even someone who uses the report may not use it for a while.

Design View Preview

Parameter fields can even be used in or next to text objects, as labels which are specific to the report you’re running. You can put them almost anywhere you need the viewer to see what the report is reporting on. So even your labeling is flexible.

Using Multiple Sections in Crystal Reports

When “laying out” data in Crystal Reports, we usually think of written information. Graphics such as charts do enter into reports, but we don’t always use photographs or other non-data images, especially in the Details section where the data will go.

If, for example, we wanted a graphic (division logo, photograph, etc.) at the beginning of each batch of data, and the data to follow, it’s easy—put the graphic in a group header. But what if we want the graphic next to the data in the Details section? We can still use group headers, but how do we “force” the data and the graphic to sit next to each other? There doesn’t seem to be any text-wrapping feature like the ones in word processors or desktop-publishing programs.

It turns out there’s a loophole. We can create additional sections—for instance, a second header section—right under the original, and end up with group headers  A, B, and so on. Normally we don’t need more than a few, but even two can suffice. Then, we can literally slide the next section(s) under the first (or second), to simulate text wrap.

Start with the normal layout. We’ve got a report partly built; the group header section gets the usual setup of labels.

Basic Layout

We can put the graphic there, in the group header.

Graphic Placed

Then, the trick. Right-click the name of the section you want to use, and click “Insert Section Below”. This creates (in our example) a second group header.

Popup Menu

Now we can move the graphic down into it and resize the first one. Then, bring up the Section Expert for that second header, and in the Common tab, check the Underlay Following Sections checkbox, and OK it.

Section Expert

There you have it.

Completed Mod

Text wrap, as such, doesn’t exist within Crystal Reports. Though fields, text boxes, graphics, etc. are all objects and do follow some of the rules we run into in DTP programs (where everything is in a box and “object-oriented layout” is the overarching principle), Crystal simply isn’t a DTP program, nor a word processor. So we have to have workarounds for some things those other kinds of programs can do much more straightforwardly. Luckily, the CR design team accounted for this. It’s just a matter of knowing the recipe.

Filtering Data in Crystal Reports

When you run a report in Crystal Reports, you have a lot of latitude as to what goes in, how it’s formatted and organized, and so on. But one consideration that doesn’t always get mentioned is how to leave data out.
Turns out, it isn’t difficult. In fact, the technique is similar to one we find in several other programs which deal with data and databases.


After opening the program, the database and the report in question, the feature that does the filtering is the Select Expert–specifically, the Record part.


Among the logical operators available to us is “Is Not Like” (sometimes seen in other programs as “< >” or “Not Equal To”). This is the one that says “DON’T show me…” whatever it is you want to exclude from a given field, like City.


But the next part is also easy–just a little different. If, let’s say, we want Crystal to show all cities in a shipping list other than Albuquerque (watch your spelling on this one! 🙂 ), we put that name between asterisks, like so: *Albuquerque*.


This means that every record whose city name does NOT include Albuquerque (beginning with, ending with, or containing) will show in the report.


It may seem a little odd at first, to want to exclude information in Crystal Reports, since usually we’re trying six ways from Sunday to figure out how to include information. But don’t forget that panning for gold meant sifting out what you didn’t want, and reducing a sauce involves removing water or other fluid to make it denser and more flavorful.
As a related point, doing Web searches can involve a similar idea. We often use Google to look for things, but the Advanced search capability lets us say, in essence, “Look for this and this but NOT that.” Hardly ever gets used, but it’s always there if we need it. Ditto in Crystal.

Using Character Mapping to Enhance a Crystal Report

It is possible to dress up a report using nothing more than a font and some color. But not in the way you think. There are some specialty fonts installed on a Windows computer know as Wingdings. Each letter is a small picture (like a phone, scissors, knife, etc.) that can be used in a report to enhance the appearance.

The first step in formatting the report is to find the characters you intend to use. They can be found in the Character Map dialog box within Accessories > System Tools or by typing Character Map in Windows Search.

charactermap

Next you will need a formula to determine when a specific character will be displayed.

alternatefont

 

In this example when sales drop below $40,000 a cross will appear otherwise a check mark will take its place. The letters “Q” and “R” represent the values you’ll need in Wingdings2.

We can dress it up even more by conditionally changing the font color.

 

oddevencolor

The final step is formatting the formula font to use Wingdings2. This is accomplished by selecting the Formula object in Design View and opening the Format Editor, going to the font tab and selecting Wingdings2 from the font list.

wingdings2

The finished report now contains a “Cross” or “Check Mark” in red or green where there once was just a capital Q or R.

checksandxs

 

Crystal Reports Performance Information Tool

After designing a report Crystal Reports provides a built-in utility that will allow you to test performance and shine a light on areas where optimization would be beneficial. It is called the Performance Information tool and can be found by selecting Report > Performance Information in the menu.

menu-performance-information

Once opened, the Performance Information dialog box has a tree structure on the left side that provides five sets of information captured by the tool.

performance-information

Report Definition – Information about the reports content: number of fields, number of summaries, Chart objects, Special Fields, etc.

Saved Data – Data captured in the report: number of data sources, total number of records, size of the saved data.

Processing – Processing of the selected report: grouping and sorting on the server, required page count, number of summary values.

Latest Report Changes – Recent changes to the report.

Performance Timing – These values provide the specific benchmark to determine if any modifications have impacted performance: timing to open the document, formatting the first page, number of pages formatted, and the average time to format a page.

To keep track of these pieces of performance data the Performance Information dialog box offers the option to save the information in a separate text file.

performance-information-save

performance-information-saved-text

These saved files can then be used for comparison purposes. What has improved and what had the greatest impact.

performance-information-text

Making “Can Grow” a Global Setting in Crystal Reports

There are a lot of different setting we talk about during our Crystal Reports classes. Much of the information we end up displaying within these reports consists of text, and in our quest to make it fit we end up making constant adjustments to width and height. That is, until we are introduced to the string format option can grow.

With one simple format setting we eliminate the need to make those height adjustments.

Here is what we are talking about. Our example will use a simple group heading to identify the country of origin.

design_view

It looks like this in print preview.

print_preview

We decide to increase the group heading font size from 10 points to 12 points and discover upon previewing that some of the text has been truncated.

cropped_group_text (more…)

Code Hinting in Crystal Reports Formula Workshop

When using Crystal Reports, formulas play a large role in preforming such basic tasks as conditional formatting, selecting records, and general calculations. The code window for all of these tasks is called the Formula Workshop. It is a centralized window divided into five sections; Workshop tree, Field Tree, Function Tree, Operator Tree, and Definition area.

formula_workshop

Normally when you want to create a basic calculation formula such as taking one database field and multiplying it by another database field you will expand your connection object within the Field Tree so you can see all of your available tables. Clicking the plus sign next to each table will reveal all of the fields in that table. You next step is either double-click the field to insert it into the Definition area or left-click and drag it into the Definition area.

The same can be said for inserting functions or operators. The only difference being you would need to expand the categories each function or operator is listed under.

The Definition area offers an interesting code hinting feature I find very useful. If I want to use a particular field from a database table all I need to do is type an opening curly brace in the Definition area and a pop-up tip gives me a list of all my available database tables. Typing the first letter of the table will select it and I can now press the tab key to insert it. As soon as it is inserted another pop-up tip shows a dot and all the fields within that table. Typing its first letter or first few letters will select it and I can now press tab to insert it.

orderdetails

quantity

A wonderfully fast way to get to any function or operator in the Definition area is to use a simple keyboard shortcut. Just press Ctrl + Spacebar and the same pop-up tip will start an alphabetical list of functions and operators. As you type the pop-up tip eliminates anything that does not contain the letters you are typing.

control_spacebar

control_spacebar_typing

As soon as you can see the function or operator you are looking for you can use your up and down arrow keys to select the item you want. Pressing the tab or enter key will insert it.

control_spacebar_more_typing

This can be a real time saver because you do not need to expand the function or operator categories. Just start typing and your code can be inserted.

tab_or_enter

Crystal Reports – Add True Bullets to a List in a Text Object with the CHRW Function

Suppose you want to display a list of items in a text object and you want that list to appear as a bulleted list.  If your list is hard coded (i.e. USA, Canada, Mexico), then you could simply type the character that represents a bullet, like an asterisk, and have your list in no time flat.

Our example will have a bit of static text at the beginning followed by the bullet list.  The static text will read as follows:

“Last Year’s Sales and Suppliers for”

We will follow up the text with a carriage return to ensure the bullet list begins on a fresh row in the text object.  This is where you could type something like the following:

* USA

* Canada

* Mexico

The finished product would look like:

Bullet01

Suppose your list if items is the result of selections made within a parameter and you want to display that user-defined list with bullets.  The first thing you have to realize is that you can’t just place the parameter in the text box and get the list. (more…)

Crystal Reports – Missing Template Preview

When applying a template to a report, it’s often preferred to “peek” inside the template to see what it has to offer prior to applying it to your report; to “take it for a test drive”, as it were.

If you have a vast template library, and many templates are similarly named, it can be of great benefit to see a preview of what you will receive if applied.  The Template Expert provides just such a preview.

Preview01

For those who prefer to create their own templates, and possibly add them to the library, the preview feature may not display if certain prerequisites have not been satisfied. (more…)

Crystal Reports – Importing Values and Descriptions into Parameters

If you are creating a list of choices for a parameter drop-down list, you can acquire the choices from several sources:

  • Manually enter the choices in the list (static and least exciting of all of the options)
  • Append a unique list of choices derived from a field in the database (static)
  • Point to a database field for available existing choices (dynamic)
  • Import items from a text file (static)

Each of the options has their pros and cons.  If you are trying to generate a list that is unlikely to change (like a list of states in the USA) and you need to ensure all options are made available from Day 1, the first option of manually entering all of the choices is the typical approach.

But what if all of those choices exist in another report or file?  In this case, the ability to import list selections has great appeal. (more…)

Crystal Reports – Adding a Watermark to a Report

If you have ever looked for the watermark feature in Crystal Reports, you no doubt walked away smothered in soul-crushing despair wondering how Life could be so cruel.

True, there is no official tool in Crystal Reports that adds a watermark to a report, but that doesn’t mean it can’t be done.  Adding a watermark just takes a bit of creativity and a handful of mouse click and keystrokes.

The trick is to add a subsection and underlay the following sections in the report.  The steps are as follows: (more…)

Crystal Reports Clear Recent Reports History

Crystal Reports – Clear Recent Reports History

As with most applications, Crystal Reports maintains a history of the last several reports opened so as to facilitate repeated access.

ClearRecent01

This is a very helpful feature, but there comes a time in every report user’s life when they just want to clear the list and start clean.

Many applications contain a mechanism that allows the user to clear either individual entries or empty the entire list.  Sadly, Crystal Reports contains no such feature. (more…)

Crystal Reports Training Courses

Crystal ReportsSkillForge offers a comprehensive line-up of Crystal Reports training courses. From introductory to more advanced topics our instructor-led, online and onsite Crystal Reports courses are delivered by expert instructors.

Our Crystal Reports 2011 Training class – like all of our classes –  includes an in-depth course manual – perfect for reference after class, as well as hands-on exercises to practice concepts demonstrated throughout the course.  See all of our Crystal Reports Training classes by visiting our Crystal Reports class page.