The IsNull function in Crystal allows us to deal efficiently with nulls, or empty fields, in a data source. I talked about this in an earlier post. We don’t want to skip partly empty records, in most cases. But if we bring in nulls, we have to be able to work around them or compensate for them. The first example shows what happens if there is nothing in the Region field, and no IsNull to help. We get a comma between city and state/region, but there is sometimes no state/region to display. And some people find this distracting, or even irritating. [...]
Crystal Reports handles nulls, or completely unfilled fields, in a particular way. And it’s very important that anyone using the program understands the details clearly. A null, in Crystal’s view, is a database field which never had anything in it, and doesn’t now. A real-life example of this is in some people-related databases; folks sometimes don’t have an official home phone anymore. They do 99.9% of their stuff via smartphone, and don’t need a hard line. So they’ll have a number in the Cell Phone field, and nothing in the Home Phone field. But this means a technically incomplete record, and [...]
We usually have to export data from Crystal Reports, for a couple of reasons. One is, the program normally has to be there to read a Crystal file, so exporting allows others to read something. And unless the report is on a shared drive, it usually has to be emailed to the other parties. Luckily, the dialog box makes the process about as simple as possible. One can click File->Export->Export Report, or go directly to the Export button in the first toolbar. When the box appears, we have two main choices to make. One is the file format we’ll export data [...]
When we print a report in any database-connected situation, we sometimes get blank results, i.e. nothing to report. But we certainly don’t want to waste paper on such a thing. So how can we let a user know about it without sending more than one page (at most) to a printer? If you routinely run reports that can come up blank, this might be important. Some programs can show an alert message to this effect: “No results match your criteria.” And though Crystal Reports has what it calls Report Alerts available, I haven’t yet found a direct way to pop up [...]
Fields, in Crystal Reports, are the way we bring data from a source into a report. Sometimes, we need to combine the data with text even more directly than placing them side by side. The technique we can use is similar to creating a mail merge with fields in a couple of other programs. First, we pull up the basic data of a report. Choose the data source and table(s), deal with links between them if necessary, and OK out of the Database Expert. Next, create the text object. One thing to be careful of is making the text object large [...]
SkillForge SkillSheet Crystal Reports Keyboard Shortcuts Click here to download the PDF version. Menu Bar Shortcut Keys In order to… Press Create New Report Ctrl + N Open Report Ctrl + O Save Report Ctrl + S Print Report Ctrl + P Cut Ctrl + X Copy Ctrl + C Paste Ctrl + V Delete Del Select All Ctrl + A Find Ctrl + F Go To Page Ctrl + G Design View Ctrl + D Refresh Report Data F5 Formula Editor Shortcut Keys In order to… Press Browse selected field Alt + B Check for Errors Alt + [...]
Crystal Reports is capable of inserting charts almost anywhere the user likes. The choice of type is nearly as wide as in Microsoft Excel. The main difference is, the chart has to be inserted before choosing type, layout specifics, and formatting. Though this can be a little daunting at first, it’s not really too different from the way other programs employ them. One key element, generally, is to make sure the chart can be as self-sufficient as possible. As in programs like Excel, and rather unlike PowerPoint, a chart in Crystal has to be pretty self-sufficient, since we don’t know whether [...]
When we use conditional formatting, we usually think of it in terms of things like making better sales numbers green and lower numbers red. But we can be more subtle than that in Crystal. A common example is where records are incomplete, or where data are filled in at some levels and not others. Sales databases might have country-level data, and city-level data, but not state-level if there are no states in a small-enough country. So if there are no states, or provinces, we don’t want to see blank spaces in the report. We can go into the Section Expert in [...]
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 them in a hierarchy, can improve the usefulness of the report. One key to a good report is to organize it so as to make it easier to understand, and therefore more useful. This is even more important now, with 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. We can start by [...]
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. 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”. The only [...]
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. [...]
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, means a single report can answer a larger number of related questions with reasonable efficiency. Since the user can input anything the field allows, parameter fields simply need to handle the necessary data type. Once you have a file open, you start by going to the Field Explorer, right-clicking [...]
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 [...]
QlikView, one of the big BI (business intelligence) programs you can get today, has a number of graphical ways to display data, which it calls charts. Among these, one of the coolest (sorry, but it is) would have to be the Gauge chart. And of the subtypes in that group, the speedometer style is probably one of the easiest to understand. For quantities, percentages, and a couple of other kinds of numbers, it’s very straightforward. Setting it up takes a little work, but it’s worth it for readability. Once you’ve got your data ready (the scenario we use is an analysis [...]
When you run a report in Crystal Reports, you have a lot of latitude as to what goes in, how to format and organize, and so on. But one consideration that doesn't always come up is how to filter, or 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 report in question, the feature that does the filter work is the Select Expert--specifically, the Record part. Among the logical operators available to filter is "Is Not Like" (sometimes seen [...]
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. Next you [...]
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. 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. 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 [...]
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. It looks like this in print preview. We [...]
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. 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 [...]
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 [...]
https://youtu.be/5a3j9-nvDf0 In this tutorial, you'll see how to create a report in Crystal Reports using data from an excel worksheet. This video will demonstrate the different methods for connecting to Microsoft Excel data from Crystal Reports and covers using both Excel 2003 and prior as well as Excel 2007, Excel 2010 and Excel 2013. For more Crystal Reports training see our Crystal Reports Training classes.
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. For those who prefer to create their own templates, and possibly add them to the library, the preview feature may not display if certain [...]
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 [...]
Crystal Reports uses a three-pass reporting method to generate reports. A pass is a process that Crystal Reports uses each time the data is read and manipulated. Depending on the complexity of the report Crystal Reports may make 1, 2, or 3 passes over the data. This feature allows for complex reporting and formula manipulation. […]
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: […]
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. 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. […]
If you are trying to export your Xcelsius file to a SWF file (Flash) and are having trouble either because: Xcelsius crashed before you were able to export your file to a SWF or You are using a trial version of Xcelsius or Your file isn't exporting even though the preview is available Your SWF file IS still available. To find where Xcelsius automatically creates a SWF file in your file system visit this post on the Analytics Tools blog.
In this Crystal Reports tutorial you will learn how to link tables within a Crystal Report. You will also learn how the difference between a left outer join, right outer join and inner join.
SkillForge 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.
In this Crystal Reports Tutorial, SkillForge Crystal Reports Instructor Joel describes how to use dynamic cascading prompts in Crystal Reports 2008 to filter a report. This content is from our live, instructor-led online Crystal Reports 2008 Training course. To learn more, visit our Crystal Reports Course page.