Printing Your First Report

This lesson walks you through the steps of creating a new report. You will see how easy it is to use the Report Creation Wizard to make a simple report. When you are finished, you will have created and printed out a report that shows employee names.

Two-Pass Report Processing Model

It’s quite possible that one of the most important parts of learning how to use Crystal Reports is understanding how Crystal Reports reads data and processes formulas while building a report. Understanding the two-pass report processing model will help you make proper design decisions and avoid problems later.

Formatting Text Objects

This tutorial demonstrates various formatting properties of the text object. Over the next few pages, you’ll add text objects to the Sandbox.rpt report and see how different formatting options affect the output.

Applying Multiple Formatting Properties

Learn the techniques for changing the formatting properties of report objects.

Creating a Grouping Report

To practice working with groups, let’s modify the Employee List report created earlier. That report listed each employee on a separate line. We are going to modify the report so that it first groups by the region and then by the city.

Grouping by a Specified Order

Crystal Reports has an excellent sample report that demonstrates how to create a grouping report using the specified order option. The report Group By Intervals.rpt is a sales report that shows total sales from the prior year for each customer. Each customer is broken down into three categories based on their sales volume: Less than $10,000, $10,000 to $25,000, and Greater than $25,000. Within each category the records are sorted by the Customer Name field. We’re going to see how this report works by creating the same report from scratch.

Hiding and Suppressing Details

This tutorial walks you through the process of toggling the detail data on and off so that it can be drilled down into.

Creating Summary Fields

This tutorial walks you through the process of creating summary fields that are displayed in the Group Footer. You will build a sales report that lists the number of purchases they had, their total sales and their average sales.

Adding Summary Fields

This video walks you through the steps of adding summary fields to your reports.

Running Totals with a Formulas

Using a formula to replace the running total field probably sounds like a lot of work. But you’ll see that it’s really quite simple once you do it a couple times.

Using Formulas to Calculate Running Totals

Running totals are much more powerful when you build them using formulas. In this video you see how easy it is to use formulas to build more flexible running total calculations.

Sales By Country and Product Type

Before getting into the tutorials, we first need to build the base report that is used in the next two tutorials. This will be used for demonstrating how to modify an existing report so that it can handle dynamic groups.

Dynamic Grouping Totals

In this tutorial we will base the groups on formulas. We will fix the previous report so that it shows either both groups or only one group. This gives the user the ability to choose how the type of report they want to see.

Dynamic Group Sorting

When creating a group, you have to specify the order to sort the data in (the default is ascending order). To make a report more flexible, you can let the user choose how to sort the report. Crystal Reports allows dynamic sorting by letting you specify a formula for the group sorting order.

Dynamic Group Sorting

This video shows you how to let the user decide the way the report is sorted by entering their choice using a parameter and integrating it into a custom formula.

Group Headers that Repeat

When grouping data, it is common to repeat the group header section at the top of each group. This can include printing the group name and column headers. While this is very helpful, there are times when this makes things tricky because you don’t always want the group header fields printed every time.

Formatting Hierarchical Groups

Hierarchical groups give you the option to customize the indentation level of each child record. This tutorial shows a tree-view structure illustrating the relationship between the parent and child records.

Selecting Records

In this tutorial, we set a filter on two fields: Country and Last Year’s Sales. For Country, we want to select a specific set of countries to print (Canada, England, and USA). For the Last Years Sales field, we only want to see records with a sales amount over $25,000.

Selecting Records with Wildcards

See what wildcards are available in the record selection formula and examples of how to use them.

Selecting Records by Grouping Data

Let’s modify the previous report so that it shows the details for all countries that had last year total sales greater than $100,000. This requires deleting the two previous fields from the record selection and summing the total of last year’s sales.

Dynamic Grouping with Formulas

Most reports use grouping with a simple data field. But groups can be much more dynamic by basing them on a formula. In this video we combine a formula with a parameter so that the user chooses how the report data is grouped.

Grouping with a Specified Order

The Specified Order option lets you pre-set the order the groups are printed on your report. We walk through an example of how to use the Specified Order option and customize the group names.

Creating Parameters

Let’s do a tutorial illustrating what we’ve learned so far about creating parameters. We’ll create a parameter that lets the user specify which country to print. We’ll populate the list by importing all the records from the database. Secondly, we want to practice creating a record selection formula that is more complicated than what can be created using the Select Expert. We will give the user the option of printing just the specified country or printing all countries by selecting “ALL” from the drop-down list.

Creating Multi-Value Parameters

The benefit to setting parameter options is that they give the user a more robust user interface for selecting parameter values. Let’s modify the report we created in the previous tutorial so that rather than just selecting a single Customer to print, the user can select multiple customers.

Printing a Parameter’s List of Values

In this video, we take look at how to take a parameter’s list of values and print it in the report header.

Modifying Parameters with Visual Studio .NET 2008

Build your own custom interfaces for your reports using a Visual Studio .NET application. This video shows you how to modify the parameters during runtime. You also see how to view parameter names during debug and set parameter values for subreports as well.

Creating Dynamic Cascading Prompts

This report will now let the user print the report for one or more customers and the user will narrow down the list of customers to pick from by first picking the Country. Once they pick the Country, then only the customers located in that country will be displayed.

Selecting Records using the ‘OR’ Operator

There will be times when your report needs to display records that meet ANY of the conditions. If only one of the two conditions is met, then the record should still be printed. To do this, you have to use the OR operator to join the conditions.

Adding ALL to the List of Values

When using the Select Expert for filtering report records, it forces the user to either enter a single value to filter on or pick from a list of values. The problem with this is that sometimes the user will want to print every record in the data source but they can’t because the selection formula doesn’t account for this. You need some way to let the user specify that they want to print all the records when the need arises.

Customizing the Enter Values Input Screen

In an effort to make Crystal Reports accessible for all people regardless of disability or impairment, the Enter Values dialog box is fully customizable. This enables you to make it easier for all your users to enter parameter values for a report.

Creating Green Bar Reports

Let’s create a “Green Bar” report that simulates the paper style that was used to be common in business reports printed on wide paper. Every other line was shaded in a light green color. This made it easy for the reader to scan across a long line of data and not lose track of which row they were reading. We’ll use conditional formatting to simulate the green bar paper.

Printing the Contents of an Array

This is part 1 of a 3 part video. In this video, you see how to print the contents of an array on a report. You print all the contents on one line, or break each element onto a separate line.

Preventing Duplicate Data from being Printed

This is part 2 of a 3 part video. In this video, you see how to only print unique values in a set of data. Thus, you are preventing duplicate data from printing. It uses an array to track the data that has already been printed. Before a new element is printed, a formula first checks to make sure it isn’t already in the array. Only then will a new item be added.

Using an Array in a Subreport for Hidden Calculations

You see how to use a subreport to populate an array with data and the array is then printed on the main report. The subreport is hidden so that the user never sees the how the calculations are derived behind the scenes.

Using the Formula Extractor

Let’s practice converting a formula using the formula extractor. As an added bonus, we’re going to convert a formula that breaks some of the rules of custom functions and see how to handle it.

How to Print Checkboxes on a Report

Displaying checkboxes on a report presents a new challenge. Checkboxes represent when a Boolean value is either true or false. But Crystal Reports doesn’t have a way to print checkboxes. Displaying Boolean values as a checkbox requires a little more creativity. You have to trick Crystal Reports into printing checkboxes by switching to the Wingdings font.

Video Tutorial for Printing Checkboxes

This video walks you through each step of printing checkboxes on your report.

Eliminating Blank Address Lines

A common problem with printing addresses is that not every line is always used. Every address has a line allocated for the street address, but some addresses need a second line for other miscellaneous information.

Adding Optional Sections

If you can use sections to suppress blank lines, it makes sense that you can use sections to do the opposite: add optional information. By adding an additional section and only showing it when necessary, the report keeps a consistent look.

Suppressing Sections for a Repeated Field

Individual report objects have a Suppress If Duplicated property which suppresses the object if its data is the same as the previous record. This prevents the report from duplicating the same information multiple times. But sections don’t have this property because Crystal Reports would have to analyze every field within the report section to make this decision. But you can write your own formula to do this and only base it on the fields that are important.

Creating a New Report Alert

Let’s see how easy it is to create report alerts by walking through a simple tutorial. This example uses a report that prints sales by product and also shows which country the purchase was made in.

Using Parameters with Alerts

Crystal Reports comes with an excellent example of a report that not only uses report alerts, but combines it with report parameters. This example links together parameters with report alerts with the alert message. We’ll walk through it to see how it works.

Building a Customer Sales Report

Before we practice using subreports, let’s first build the main report. This is going to be the foundation for all the tutorials throughout the subreport tutorials.

Linking the Subreport with a Data Field

Let’s start working with subreports by creating a subreport that links to the main report using a data field. We are going to take the Customer Sales Report we created in the previous tutorial and list the customer credits on it. We’ll link the two reports together using the Customer Credit ID field which is found in both tables.

Creating an Unlinked Company Header

Let’s create an unlinked subreport that displays the company address in the report header. You might be tempted to think that it is easier to just type in the company address manually rather than going through the effort of adding a subreport. But there are benefits to using a subreport here.

Re-Using Subreports

We know that subreports are saved within the definition of the main report. So we need to save it independently of the main report.

Using Shared Variables

To illustrate how to use shared variables, let’s modify the example report shown earlier that lists the customer sales and credits issued to the customer.

Linking Subreports with a Data Field

Linking a subreport with a data field is the most common way of associating subreports with the main report. This video walks you through the steps for performing this process.

Linking Subreports with a Formula

There are a variety of ways to link a subreport to a main report. Linking with formulas gives you more flexibility for connecting your subreport to the main report. This video walks you through the steps of creating a formula and using it to link to the main report.

Using Subreports for Hidden Calculations

There is a way to trick Crystal Reports into hiding the subreport but still perform its calculations. The key is that even though you can’t suppress the subreport object, you can suppress the sections within the subreport.

Putting subreports in Separate Sections

In this tutorial, we will examine the first two rules of sub reports mentioned earlier and demonstrate why each subreport should be put in its own section.

Resetting Subreport Variables

One thing you might not have noticed in the previous report is that there is actually a problem with it. The subreport doesn’t reset the shared variable back to zero. Let’s fix this right away so that all customers have the correct totals.

Hiding Empty Subreports

Another problem that you’ll eventually encounter with subreports is when they don’t have any data but they still print. You’ll see the column headers but no detail records. In fact, you can see this in the previous tutorial. The column headers for the credit report are there, but no data is displayed. This makes for an unprofessional looking report. Correcting this requires making two changes.

Adding a Group Chart in the Page Header

Let’s take a report that uses groups and print a bar chart in the page header that summarizes each group’s value. This gives the user a high-level overview of how each group compares to the other groups.

Customizing Group Charts

Let’s look at how to customize an individual group chart by walking through a tutorial on it. we will place a gauge chart in the Group Header and then modify an individual chart for only one of the groups. This demonstrates how one group can have formatting that is unique from the other charts.

Your First Cross-Tab Report

LLet’s create a simple cross-tab report to see the basic steps. We’ll create an annual sales report for customers.

Adding Groups to a Cross-Tab

We are going to explore how groups work by modifying the report from the previous tutorial. We are going to add grouping to both the rows and columns.

Customizing the Cross-Tab Layout

In a large cross-tab report, it can sometimes be difficult to spot important information among the many pages of data. In this circumstance, it helps to customize the look of the individual fields within the cross-tab.

Customizing the Data in a Cross-Tab

Formulas can also be used to customize the summary data that the cross-tab report is based on. For example, a formula can be used to dynamically change the value that gets summarized or it can change the text that is displayed within the cross-tab.

Customizing the Column Grouping with Parameters

Rather than formatting the cross-tab object based upon the data within the report, you can use parameters to let the reader choose how to customize the cross-tab.

Different Formulas for Each Row

Customize the look of each row by using formulas that differentiate each row.

Copying Cross-Tab data into an Array

We are just going to focus on how to copy the cross-tab object into arrays. Since there are so many things you can do with these arrays, I’ll leave it up to you to customize it as your needs dictate.

Adding a Map to Your Report

Let’s walk through two tutorials of how to add a map to a new report. You’ll see that you can quickly create a map in just a few simple steps. We are going to create a map that shows Last Year Sales value for each country.

Creating Pie Charts

Let’s get a little fancy and create a map type that plots multiple values: the pie chart. We learned earlier that the pie chart and bar chart types are only available when the Data tab has different fields for Geographic Area and On Change Of. By using a third data field, we can plot values against a category. Let’s see how this works by mapping the total sales by city against multiple product types.

1.01 Introducing Crystal Reports

Information is power. In today’s world, where we have computers that are as powerful as yesterday’s supercomputers, there is a lot of information at our disposal. Being able to convert all this information into useful data is key for many businesses. There are many products available to make this possible, and Crystal Reports is by […]

1.02 What’s New In Crystal Reports

What’s New in Crystal Reports XI Crystal Reports XI is the eleventh release of this popular reporting tool. The “XI” is the Roman numeral 11 as well as an abbreviation for Xtreme Intelligence (playing on the business intelligence theme). Crystal Reports XI has exciting new functionality that goes far beyond the previous versions of Crystal […]

1.03 Getting to know Crystal Reports

Getting to know Crystal Reports After opening Crystal Reports, you are presented with the Start Page. The start page is brand new for version XI. The goal is to make it easy to either open the most recent reports you have been working on or find out the latest news and updates about Crystal Reports. […]

1.04 The Report Designer

The Report Designer The Report Designer lets you design reports by adding report objects, formatting the report layout, creating groups, etc. It’s where you spend the majority of your time when using Crystal Reports. If you’ve used previous versions of Crystal Reports then you are very familiar with the designer. Figure 1-2. The report design […]

1.05 Tutorial 1-1. Printing Your First Report

Tutorial 1-1. Printing Your First Report This lesson walks you through the steps of creating a new report. You will see how easy it is to use the Report Creation Wizard to make a simple report. When you are finished, you will have created and printed out a report that shows employee names. Open Crystal […]

1.06 Getting to Know the Report Designer

Getting to Know the Report Designer The Report Designer is the area where you create reporting magic. With it, you design new reports, modify existing reports, write formulas, preview reports and print them out. It gives you all the tools necessary for designing hundreds of reports. Figure 1-4 shows the designer and its various windows. […]

1.07 Managing the Toolbars

Managing the Toolbars The toolbars are located directly below the menu options listed along the top of the designer. The toolbars make it easy to access the different designer features by the simple click on the mouse. Most buttons either perform an action or make a change to the currently selected report object. Some of […]

1.08 Dockable Windows

Dockable Windows When you first install Crystal Reports, there are three dockable windows displayed in the Report Designer: the Workbench, the Report Explorer, and the Field Explorer. Each window is located along the right side of the designer. If you want to personalize your workspace, you can move them to another location within the designer. […]

1.09 The Workbench

The Workbench Do you work with so many reports that it’s hard to keep track of all of them or even remember for whom or which department they were created for? The Workbench, located on the top right portion of the designer, makes it easy to organize and categorize a large number of reports. Within […]

1.10 The Report Explorer

The Report Explorer The Report Explorer shows a snapshot of every report object on the report. This lets you browse through all the report objects in a hierarchical tree structure and look at each section and see every object within that section. By clicking on the + or – next to the section name you […]

1.11 The Field Explorer

The Field Explorer The Field Explorer gives you a list of all the available database fields, formulas, parameters, summary fields and special fields. The Field Explorer serves two purposes. First is that you can see and edit the different fields on the report. For example, the Database Fields category shows you which fields are part […]

1.12 The Design Tab and Preview Tab

The Design Tab If creating reports were an art, the Design tab would be your canvas. It’s where you place the report objects, change colors or fonts, and set the layout of the report. The details of using the Design tab to create reports are the covered throughout this whole book. For now we will […]

1.13 Understanding the Report Wizards

Understanding the Report Wizards The report wizards make it easy to create most types of reports. By selecting your data source and clicking on a few buttons, you can quickly create new reports. Depending upon your needs, creating reports can be a simple or complicated process. For example, it is very easy to print mailing […]

1.14 The Wizard’s Dialog Boxes

Using the Wizard’s Dialog Boxes Each report wizard uses a combination of different dialog boxes to question you about how to build your report. Each wizard presents a slightly different combination of these dialog boxes. This section describes how to use each dialog box and explains any aspects of it that may not be obvious. […]

1.15 Two-Pass Report Processing Model

Two-Pass Report Processing Model It’s quite possible that one of the most important parts of learning how to use Crystal Reports is understanding how Crystal Reports reads data and processes formulas while building a report. This is called the two-part report processing model and it is the reason why you can and can’t do certain […]

2.01 Setting the Designer Defaults

Learning the Report Designer Many people like to spend their weekends fiddling around their home doing small projects to keep their place fixed up. But before starting any new project, you have to make sure you are familiar with the tools you are going to use on the project. For example, if you just got […]

2.02 Setting the Layout Options

Setting the Layout Options The Layout tab (see Figure 2-1) specifies the default properties for both the report designer and the report preview. For example, it lets you set how the designer looks, change the preview window, and display field information. The options within the Layout tab are divided into four different categories based upon […]

2.03 Setting the Default Format Options

Setting the Default Format Options After adding fields to a report, you frequently have to format each one to the proper font, alignment, format, etc. If you need to format report objects a particular way for multiple reports, you can set the default properties that are applied to each field. This saves you work because […]

2.04 Setting Report Options

Setting Report Options After setting the options for customizing the report design experience, the next step is customizing the settings for an individual report. There are different options that affect a report’s behavior and how it is printed. These options are based upon how the report is to be used. Page and Printer Options The […]

2.05 Designing Reports

Designing Reports The best way to become familiar with how to design reports is to start with the basics. Once you understand how the designer and the individual report objects work, you’ll be ready to move on to advanced topics of report design (and finally start doing something fun!) The default view of the Design […]

2.06 Adding Report Objects

Adding Report Objects The majority of your time spent designing reports is spent working with the report objects. Crystal Reports gives you many types of objects for designing reports. Learning how each works is critical to being a proficient report writer. This section covers most of the available report objects (the more complex ones have […]

2.07 Tutorial 2-1. Formatting Text Objects

Tutorial 2-1. Formatting Text Objects This tutorial demonstrates various formatting properties of the text object. Over the next few pages, you’ll add text objects to the Sandbox.rpt report and see how different formatting options affect the output. Add a text object to the report by right-clicking anywhere on the report and selecting Insert Text Object. […]

2.08 Tutorial 2-2. Applying Multiple Formatting Properties

Tutorial 2-2. Applying Multiple Formatting Properties Add a new text object anywhere on the report and make it tall enough to show a few lines of text. But don’t make it too long. Enter some text to fill up the text object. Notice how the cursor automatically moves to the next line when it gets […]

2.09 Fields That Can Grow

Fields that Can Grow When working with database fields, it’s common to show fields that display a large amount of data. For example, an inventory table can have a Description field that stores a lot of data. Depending upon the inventory item, some descriptions will be short and other will be long. When you add […]

2.10 Formatting Strings, Numbers, and Objects

Formatting Strings The tutorials earlier in the chapter showed you different ways to format a text object. But it didn’t discuss every formatting option available. The Format Editor has many more options for modifying the way string based report objects appear. The different formatting options are available for textbox objects as well as formulas, report […]

2.11 Printing Wingding Fonts

Printing Wingding Fonts Don’t think that printing graphics involves just displaying JPG and BMP files. You can also get some interesting results using the non-standard fonts. One font in particular that is very useful is the Wingdings font. You can add special symbols such as checkboxes, arrows, folder, etc. For example, open the Windows Character […]

2.12 Printing Other Objects

The Chart Object The Chart object lets you add a chart to your report. There are a variety of options to choose from so that you can customize it however you need. There is so much to cover with this object that it was given a separate chapter. See Chapter 12 for more information. The […]

2.13 Moving and Sizing Report Objects

Moving and Sizing Report Objects Even though we all wish our reports were perfect the first time through, this is rarely the case. After adding objects to the designer, you frequently need to reposition objects and change their size. An earlier section of this chapter showed you how to add report objects to the designer. […]

2.14 Moving Object with Guidelines

Moving objects with Guidelines The previous section discussed how to add objects so that they lined up with existing objects. But it didn’t cover how to move multiple objects so that they stay lined up. This is where Guidelines are used. Guidelines create an anchor for attaching multiple objects. The benefit is that anytime you […]

3.01 Sorting Records

After learning the basics of creating simple reports, you will quickly find yourself developing reports that are more involved then just listing sequential records one by one. For reports that consist of dozens, if not hundreds, of pages, providing a meaningful format that groups the data into logical units goes a long way towards making […]

3.02 Grouping Records

Grouping Records When a report has a lot of pages, it is sometimes hard to quickly get a general idea of what the report is telling you. Sorting the data helps you find a specific record, but it doesn’t help give you a high-level summary of what the data means. Grouping records lets you summarize […]

3.03 Adding and Customizing Groups

Adding and Customizing Groups Just like sort fields, multiple fields can also be used for creating groups. Each field is given its own group header and footer. An example of grouping on one field is a sales report that groups the sales people by their territory. If there are a lot of sales people within […]

3.04 Tutorial 3-1. Creating a Grouping Report

Tutorial 3-1. Creating a grouping report To practice working with groups, let’s modify the Employee List report created in Chapter 1. That report listed each employee on a separate line. We are going to modify the report so that it first groups by the region and then by the city. Open the Employee List report […]

3.05 Navigating Groups

Navigating Groups Adding groups to a printed report makes it easier to flip through the report and get a quick overview of the report’s information. But the Preview tab makes it even easier to work with groups. To the left of the preview window is the Group Tree window which lets you navigate to each […]

3.06 Formatting Groups

Formatting Groups The default layout for groups is that they print one after the other. When the group footer is printed, the subsequent group’s header is printed right after it. Some reporting requirements require changing this default behavior. For example, an invoicing report is designed so that it is grouped by client and each client […]

3.07 Advanced Grouping Options

Advanced Grouping Options The majority of the time that you add a group to a report you just need something simple that displays the group name and sorts it in ascending order. This is the default behavior when you add a group to the report and nothing additional is required. But there are times when […]

3.08 Sorting in a Specified Order

Sorting In a Specified Order The second dropdown box at the top selects the sorting order of how the groups are listed. With one exception, the sorting options are what you would expect and don’t need any explanation. You can choose a sort order that is Ascending, Descending, Original Order (no sorting) or Specified Order. […]

3.09 The ‘Others’ Grouping Options

The last tab in the Change Group Options dialog box is the Others tab. It lets you decide what to do with the records that aren’t otherwise specified. Since it is possible that some reports won’t need to specify how every value is grouped, this tab accumulates all the remaining values that didn’t get included […]

3.10 Customizing the Group Name

Customizing the Group Name In most circumstances, the Group Name field displayed on the report is the field’s current value. For example, if you are grouping by country, then the group name shown on the report is the current country name. If you don’t want to display the current value as the group name, you […]

3.11 Keeping the Group Together

Keeping the Group Together The second and third checkboxes on the Options tab control whether the report should try to keep the entire group on the same page. These properties were mentioned earlier in the Keep Together discussion within the Formatting Groups section. Setting the Keep Group Together checkbox is important if you want to […]

3.12 Sorting the Grouping Data

Sorting the Group Data After creating the groups and running a test report, you may notice that although your groups are fine, the detail rows within the group are out of order. They might appear to be printed randomly or their natural sort order isn’t how you want it sorted. For example, in Figure 3-15 […]

3.13 Reordering the Groups

Reordering the Groups Sometimes you might create a report with multiple groups and then decide that groups should be rearranged. For example, a report that sorts first by state and then by product could be changed to sort by product first and then state. There are two ways to reorder the group fields. The first […]

3.14 Tutorial 3-2. Grouping by a Specified Order

Tutorial 3-2. Grouping by a Specified Order Crystal Reports has an excellent sample report that demonstrates how to create a grouping report using the specified order option. The report Group By Intervals.rpt is a sales report that shows total sales from the prior year for each customer. Each customer is broken down into three categories […]

3.15 Displaying Top N Reports

Displaying Top N Reports An alternative to standard grouping is to create reports that show the first or last set of records in a certain group. For example, rather than showing all the sales people for the company, you could show the top 5 sales people that have the best sales for the month. Or […]

3.16 Grouping on Summary Values

Grouping on Summary Values The Group Sort Expert dialog box actually has a dual purpose. In the previous section, I quickly skimmed over the fact that the dropdown box defaults to the value All so that all groups are displayed. But this setting is also useful for creating new ways of sorting your groups. Normally, […]

3.17 Displaying Hierarchical Reports

Displaying Hierarchical Reports A hierarchical report shows the relationship between records in the same table using a tree format. An example is an Organization Chart that uses the personnel table to display a list of its employees and their supervisors. Each employee has a record in the personnel table. But each employee also has a […]

3.18 Drilling Down on Data

Drilling Down on Data The purpose of creating groups is to organize a lot of data in a report so that the reader can quickly find their data by scanning through the different groups. This lets the reader skip over the data that doesn’t relate to them and instead focus on what is important. But […]

3.19 Tutorial 3-3. Hiding and Suppressing Details

Tutorial 3-3. Hiding and Suppressing Details This tutorial walks you through the process of toggling the detail data on and off so that it can be drilled down into. Open the report Group Selection.rpt. This is one of the example reports installed with Crystal Reports. It is located in the Feature Examples folder. Right-click on […]

3.20 Summarizing Data

Summarizing Data A major benefit to grouping data is that it lets you put summary data within the group footer and header. This is beneficial because when there are a lot of detail records, you don’t want the reader to have to get out a calculator to calculate sub-totals and averages of columns. You want […]

3.21 Tutorial 3-4. Creating Summary Fields

Tutorial 3-4. Creating Summary Fields This tutorial walks you through the process of creating summary fields that are displayed in the Group Footer. You will build a sales report that lists the number of purchases they had, their total sales and their average sales. Go to the Start Page and click on Standard Report Wizard. […]

3.22 Running Totals

Running Totals Running totals are like an advanced version of summary fields. They give you more ways of summing data and give you more flexibility for determining which data to summarize. The primary difference is that summary fields are calculated by group and running totals can be calculated many ways (by group, record by record, […]

3.23 Placing the Running Total Field

Placing the Running Total Field You might recall from earlier in the chapter that when printing a summary on a group, you can put that field in either the group footer or group header and it still prints the same result. The location of a summary field doesn’t affect its value. This isn’t true for […]

3.24 Comparing Running Totals to Summary Fields

Running Totals Compared to Summary Fields Running totals are interesting because they have similar functionality to summary fields, and they can also be duplicated with formula fields. This can cause some confusion as to when you should use a running total, a summary field, or a custom formula field. Each of these options has unique […]

3.25 Using Forumulas instead of Running Totals

Using Formulas instead of Running Totals Running totals have two limitations. The first is that you are limited in the types of calculations you can do (Sum, Avg, etc.). The second limitation is that running totals are restricted to using only first pass data. For example, the formulas can’t use summary functions, the Next() or […]

3.26 Tutorial 3-5. Using a Formula for the Running Total

Tutorial 3-5. Using a Formula for the Running Total Using a formula to replace the running total field probably sounds like a lot of work. But you’ll see that it’s really quite simple once you do it a couple times. This tutorial uses a formula to print the line number on every row, and for […]

3.27 Advanced Tutorials Intro

Advanced Tutorials As you learned throughout this chapter, Crystal Reports gives you many options for grouping report data and customizing the output. It will probably take some practice before you become familiar with the different ways each option can be used. As you learn more about each option, you’ll discover new ways of creating advanced […]

3.28 Tutorial 3-6. Sales By Country and Product Type

Tutorial 3-6. Sales By Country and Product Type.rpt Before getting into the tutorials, we first need to build the base report that is used for tutorial 3-7 and 3-8. This will be used for demonstrating how to modify an existing report so that it can handle dynamic groups. On the Start Page click Standard Report […]

3.29. Tutorial 3-7. Dynamic Grouping Fields

Tutorial 3-7. Dynamic Grouping Fields In this tutorial we will base the groups on formulas. We will fix the previous report so that it shows either both groups or only one group. This gives the user the ability to choose how the type of report they want to see. There are three changes that need […]

3.30 Tutorial 3-8. Dynamic Group Sorting

Tutorial 3-8. Dynamic Group Sorting When creating a group, you have to specify the order to sort the data in (the default is ascending order). To make a report more flexible, you can let the user choose how to sort the report. For example, a person who wants to see the most recent sales activity […]

3.31 Tutorial 3-9. Group Headers that Repeat

Tutorial 3-9. Group Headers that Repeat When grouping data, it is common to repeat the group header section at the top of each group. This can include printing the group name and column headers. While this is very helpful, there are times when this makes things tricky because you don’t always want the group header […]

3.32 Tutorial 3-10. Formatting Hierarchical Groups

Tutorial 3-10. Formatting Hierarchical Groups Hierarchical groups (discussed earlier in this chapter) give you the option to customize the indentation level of each child record. This shows a tree-view structure illustrating the relationship between the parent and child records. You can also have them line up directly below each other when there is a limited […]

3.33 Tutorial 3-11. Breaking Reports Apart By Group

Tutorial 3-11. Breaking Reports Apart by Group As discussed earlier in the chapter, the properties New Page Before and New Page After are used to cause page breaks between groups. This lets you break apart the report and distribute the individual pages. Unfortunately, setting these properties causes a blank page to be printed either at […]

4.01 Selecting Records

The previous chapter showed you how to make reports easier to read by sorting and grouping the data. This made it possible to break down large amounts of data into smaller and easier to understand sections. Another way to make it easier to understand reports is by filtering out unnecessary data prior to printing the […]

4.02 Using the Select Expert

Using the Select Expert To open the Select Expert, select the menu items Report > Select Expert. The other method is to click on the Select Expert button on the Toolbar. If this is the first time the Select Expert has been run for the report, it shows the Choose Field dialog box. This is […]

4.03 Filtering with Wildcards

Filtering with Wildcards The comparisons Is Like and Is Not Like have two characteristics which require more explanation. The first characteristic is that they let you build a custom list of allowable criteria. This is similar to the comparisons Is One Of and Is Not One Of described previously. As long as a field’s value […]

4.04 Introducing the Formula Editor

Introducing the Formula Editor The Select Expert is a very helpful tool for selecting one or more fields using the basic comparison operators. This is probably adequate for many of the reports you write, but what about the reports where you need to build more complex filtering criteria? For those reports, there is the Formula […]

4.05 Selecting Records for Grouping

Selecting Records for Grouping The Select Expert is used for selecting records based upon the value of one or more fields in each record. Thus, each record is looked at individually to determine whether it should be used in the report or not. Crystal Reports also lets you filter data based upon group values. In […]

4.06 Tutorial 4-1. Selecting Records

Tutorial 4-1. Selecting Records Let’s put what we learned about selecting records to use. In this tutorial, we set a filter on two fields: Country and Last Year’s Sales. For Country, we want to select a specific set of countries to print (Canada, England, and USA). For the Last Years Sales field, we only want […]

4.07 Tutorial 4-2. Selecting Records by Grouping Data

Tutorial 4-2. Selecting Records by Grouping Data Let’s modify the previous report so that it shows the details for all countries that had last year total sales greater than $100,000. This requires deleting the two previous fields from the record selection and summing the total of last year’s sales. Open the report from the previous […]

4.08 Filtering with Parameters

Filtering with Parameters The first part of this chapter showed how filtering data lets you customize a report to fit a specific user’s needs. The only drawback to this approach is that you have to know in advance what that user’s needs are when you are designing the report. And if different users have different […]

4.09 Creating Parameters

Creating Parameters When creating a report that uses parameters, you have many jobs to do. You have to determine how the parameter will prompt the user to enter a value, determine the types of values that can be entered, decide if there are default values, and use the parameter within the report. The remaining portion […]

4.10 Choosing the Data Type

Choosing the Data Type After entering the parameter’s name, you have to select its data type from the dropdown list in the top right corner of the dialog box. A parameter stores information inside the computer’s memory as one of five possible data types: Boolean (True/False), String (text entries), Number, Currency (monetary values) and Date/Time. […]

4.11 Creating a List of Values

Creating a List of Values When the user is presented with a parameter prompt, rather than make them type in a value for the parameter, you can give them a list of possible values to choose from. This is called a List of Values. It is a predefined list of default values that the user […]

4.12 Manually Entering a List of Values

Manually Entering a List of Values The easiest method of populating the list of values is to manually type each entry. By clicking the Insert button on the Create Parameters window, the cursor is moved to the first available line in the list so you can type in the value. Alternatively, you can also click […]

4.13 Using a Table to Populate the List of Values

Using a Table to Populate the List of Values The drawback to populating tables manually is that you have to type in each entry yourself. If the list of values is going to be large then this could take a lot of effort. In addition, you have to be careful that you spell everything exactly […]

4.14 Choosing the Proper Fields

Choosing the Proper Fields Before deciding which fields to use in the list of values and its description, you should consider the database structure first. For the purpose of importing values, you need to figure out whether the field you want to display has a primary table or not (it may not exist). A primary […]

4.15 Tutorial 4-3. Creating Parameters

Tutorial 4-3. Creating Parameters Let’s do a tutorial illustrating what we’ve learned so far about creating parameters. We’ll create a parameter that lets the user specify which country to print. We’ll populate the list by importing all the records from the database. Secondly, we want to practice creating a record selection formula that is more […]

4.16 Setting Parameter Options

Setting Parameter Options At the bottom of the Edit Parameters dialog box is an Options area that lists the available options that can be set for a parameter. It has options that let you do more with parameters than just display a list of default values for the user to pick from. Some of the […]

4.17 Using Edit Masks

Edit Mask Some string parameters have a format where special characters appear within the string at particular locations. For example, a phone number has the format of (999) 999-9999. No matter what the phone number is, it has to be formatted the same every time. Crystal Reports gives you the ability to specify an edit […]

4.18 Multiple, Discrete and Range Values

Multiple, Discrete and Range Values A parameter has the flexibility to store a single value, a range of values, or a combination of the two. This gives you a lot to think about when deciding which is appropriate for each parameter. Since these three options can be used in conjunction with each other, it’s best […]

4.19 Using Boolean Parameters

Using Boolean Parameters Boolean parameters are unique from the other parameter types because they can only hold one of two values and they can be grouped together. With other types of parameters, you can list many possible choices in its list of values. But Boolean parameters don’t let you decide the list of values. Although […]

4.20 Managing the Parameter List

Managing the Parameter List After adding a parameter to the report, you often need to modify it. This could be renaming it or changing its options. As you might expect, Crystal Reports lets you perform the typical maintenance tasks with parameters. You can modify them, delete them, rename them and reorder them. All the tasks […]

4.21 Tutorial 4-4. Creating Multi-Value Parameters

Tutorial 4-4. Creating Multi-Value Parameters The benefit to setting parameter options is that they give the user a more robust user interface for selecting parameter values. Let’s modify the report we created in the previous tutorial so that rather than just selecting a single Customer to print, the user can select multiple customers. Open the […]

4.22 Using Paramters

Using Parameters After you’ve created the necessary parameters, you need to know how to use them within your report. After doing all this work, you don’t want it to go to waste! There are three primary ways of using parameters: filtering records, displaying them directly on the report, and using them in conditional formatting. We’ll […]

4.23 Printing Paramter Values

Printing Parameter Values The second method of using parameters is displaying their value on a report. There are various reasons for displaying parameters on a report. A very common reporting requirement is that when filtering data, it is helpful to print the filter criteria so that anyone reading the report knows that only a subset […]

4.24 Entering Parameters

Entering Parameters Creating parameters and using them within a report’s selection filter and its formulas is just one aspect of parameters. The second aspect is when the user runs the report and enters the data into the parameter fields. Although this is a pretty simple process, it helps to review it to make sure you […]

4.25 Dynamic Cascading Prompts (DCPs)

Dynamic Cascading Prompts Crystal Reports XI gives you a wonderful new feature for working with parameters: Dynamic Cascading Prompts, commonly referred to as DCPs. This gives parameters the ability to be dynamic because they are linked directly to the database. They can also be cascading because the value in one parameter is used to filter […]

4.26 DCP Benefits and Limitations

The beauty of DCPs is that they give you the ability to link prompts together so that each successive parameter’s list of values is determined by the selection in the previous parameter. The value chosen for one parameter is used to filter the data in the next parameter. Thus, each prompt’s selection “cascades” down to […]

4.27 Creating Dyanamic and Cascading Prompts

Creating Dynamic and Cascading Prompts Creating a parameter that uses DCPs involves entering options in the same Create New Parameters dialog box that you learned about when we were discussing static parameters. The difference being that instead of choosing the Static option you will now choose the Dynamic option. When you do so, the dialog […]

4.28 DCP Helpful Tips

When selecting which field to display, it’s important to choose the proper table. As we discussed earlier in the chapter, some tables have a complete list of all the available values and some tables only show the values that have been selected as part of another record. This second list is most often incomplete. For […]

4.29 Tutorial 4-5. Creating DCPs

Tutorial 4-5. Creating DCPs The previous tutorials in this chapter have focused on different ways to modify the Group.rpt sample report and customize it with parameters. This tutorial is no different. It builds on the other tutorials by showing you how to use DCPs to create the same parameters and demonstrates how it makes them […]

4.30 Tutorial 4-6. Selecting Records using ‘Or’

Advanced Tutorials Tutorial 4-6. Selecting Records Using the ‘Or’ Operator If you recall from the discussion about using the Select Expert dialog box with multiple fields, Crystal Reports builds the selection formula for you automatically. When there is more than one field used, each field’s selection criteria must be met for the record to be […]

4.31 Tutorial 4-7. Mixing Static and Dynamic Parameters

Tutorial 4-7. Mixing Static and Dynamic Parameters In the discussion about DCPs, I said that Crystal Reports doesn’t allow you to mix static and dynamic parameters. If you want to use DCPs, then every prompt within the prompt group must be dynamic (i.e. pull its data from the data source). The problem with this is […]

4.32 Tutorial 4-8. Adding ALL to the List of Values

Tutorial 4-8. Adding “ALL” to the List of Values When using the Select Expert for filtering report records, it forces the user to either enter a single value to filter on or pick from a list of values. The problem with this is that sometimes the user will want to print every record in the […]

4.33 Tutorial 4-9. Customizing the Enter Values Input Screen

Tutorial 4-9. Customizing the Enter Values Parameter Interface In an effort to make Crystal Reports accessible for all people regardless of disability or impairment, the Enter Values dialog box is fully customizable. This enables you to make it easier for all your users to enter parameter values for a report. You can increase the size […]

5.01 Using the Formula Workshop

Crystal Reports comes with a powerful set of tools that let you build sophisticated formulas in your reports. You can perform tasks such as conditional formatting, specialized calculations, selecting records and report alerts. Formulas give you advanced functionality that is often only limited by your experience or imagination. The next three chapters are a thorough […]

5.02 Tutorial 5-1. Creating Green Bar Reports

Turtorial 5-1. Creating “Green Bar” Reports Let’s create a “Green Bar” report that simulates the paper style that was used to be common in business reports printed on wide paper. Every other line was shaded in a light green color. This made it easy for the reader to scan across a long line of data […]

5.03 Adding and Editing Formulas

Adding and Editing Formulas Formulas are used by many parts of a report. You can create formulas from scratch and display their results directly on the page. You can attach reports to different properties of a report object so that the property can be conditionally turned on or off. Crystal Reports also filters report data […]

5.04 Writing Formulas

Writing Formulas with the Formula Workshop Formulas are referenced in different ways depending upon which part of the report is using them. But they all share the same editor. The Formula Workshop is the command center for entering and editing formulas. Getting a solid understanding of how to use the Formula Workshop is essential before […]

5.05 Using the Formula Toolbars

Using the Toolbars Before getting into the details of what each window does and how to use it, let’s first explore the toolbars and the buttons on each toolbar Each group of buttons has a different way of assisting you with writing formulas. Displaying Windows These buttons make minor modifications to the windows within the […]

5.06 Creating New Formulas

Creating New Formulas The New button creates a new formula. When you click on it, it shows you a list of the different types of formulas and you click on the type you want to create.
Figure 5-3. Menu items for the New Formula button. If you’ve been working on an existing formula and […]

5.07 Editing Code

Editing Code These five buttons assist you with writing formulas. The first button checks the syntax of the formula. If it finds an error in the formula, it prompts you with a message box telling you if there is a syntax error or not. You can check the syntax after you are finished writing the […]

5.08 Working with Programming Code

Working with Programming Code These three buttons assist you with programming specific tasks. The first button tells Crystal Reports whether you are programming with Crystal syntax or Basic syntax. We’ll discuss programming languages in more detail in the next two chapters. For now you just need to know that this button lets you choose the […]

5.09 Setting Bookmarks in Formulas

Setting Bookmarks in Large Formulas Although it is common for formulas to only have a few lines of code, there are certainly times when you need to write a very complex formula that consists of dozens of lines of code. Of course, a formula this complex will require quite a bit of debugging to make […]

5.10 The Formula Workshop Windows

Understanding the Formula Workshop Windows The Formula Workshop lists all available fields, formulas and operators. It makes it possible to almost write your formulas without doing any typing. Just double-click the appropriate functions and report fields and let the formula be built for you. Personally, I don’t find this very practical on a regular basis. […]

5.11 Creating Formulas, Calculations

Creating Formulas The two most common reasons for using the Formula Workshop are for performing calculations and using conditional formatting. Both of these tasks involve different steps for creating the formula and working with it. The steps for doing each are described next. Creating Calculations A report gets the majority of its data from a […]

5.12 Using Conditional Formatting

Using Conditional Formatting Reports by their very nature are static. Although the printed data changes and the calculation results are different every time, the report format stays the same. For example, if the first field in a column has a font of Arial and is black, every field in that column is also going to […]

5.13 Using the Highlighting Expert

Using the Highlighting Expert The Highlighting Expert is a simplified version of the Formula Workshop. If you’re having a tough time learning how to use the Formula Workshop, you can use the Highlighting Expert until you get more experience with formulas. However, it has limitations on what you can modify. It only lets you modify […]

5.14 Error Checking Reports

Error Checking Reports This chapter gave you an introduction to using the Formula Workshop and the next chapter dives deeper into writing programming code. But before you jump in the water head first, you need to be aware of the debugging tools available to you. No matter how good your intentions are of writing the […]

5.15 Checking Run-Time Errors with the Call Stack

Checking Run-Time Errors with the Call Stack Run-time errors occur while a report is being generated. These errors are not caught while you are designing the report because they are syntactically correct. They are usually triggered by data that the report is processing. For example, here is a syntax error that would be caught immediately […]

6.01 Programming with Built-In Functions

Programming with Built-In Functions The Formula Workshop in Crystal Reports gives you the ability to write very powerful formulas. In addition to writing your own formulas, Crystal Reports has dozens of built-in functions that decrease the amount of work you have to do. After all, why re-invent the wheel when you don’t have to? This […]

6.02 String Functions

String Functions The ability to modify and concatenate strings is a powerful feature of many programming languages, and Crystal syntax doesn’t disappoint. This section breaks out the different categories of string functions and summarizes how they work. The categories are: Analyzing a String, and Manipulating Strings. Throughout this section, many of the functions listed use […]

6.03 Manipulating Strings

Manipulating Strings It is common for a string to be modified before it is displayed on a report. This can consist of reformatting the string or even joining the different elements of an array into a single string. Table 6-4 shows the functions for manipulating strings. Descriptions of each function are listed next to its […]

6.04 Converting Data Types

Converting Data Types Crystal syntax is a type safe language that requires all constants and variables in the same formula to be of the same data type. It also requires you to pass constants and variables as arguments using the exact data type that the formula expects. Even though the data types in Crystal syntax […]

6.05 Formatting Output

Formatting Values for Output When formatting output to be displayed in a report, the output is usually a combination of different data types. For example, there will be a standard text message that also displays the current value for a field. But this can cause problems because Crystal syntax is a type safe language. It […]

6.06 Math Functions

Math Functions Table 6-8. Math Functions Function Name Description Abs(number) Return the absolute value. Ceiling(number, multiple) Rounds up to the nearest multiple you specify. Truncate(number, decimals) Return a number with a specified number of significant digits. Int(number), numerator \ denominator Return the integer portion of a fractional number. Pi 3.14… Remainder(numerator, denominator), Return the remainder […]

6.07 Random Numbers

Generating Random Numbers Generating random numbers is a very common task for programmers. But if you aren’t familiar with it, the way they work isn’t very intuitive. This section gives you an overview of what random numbers are and how to use them. Crystal Reports uses the function Rnd() to return a random number. The […]

6.08 Date and Time

Date and Time Functions Crystal Reports gives you a plethora of date and time related functions to utilize. You can also combine different functions together to create very powerful date calculations. There are a lot of new concepts to learn. Table 6-12. Date and Time Functions Function Name Description CurrentDate, CurrentTime, CurrentDateTime, PrintDate, PrintTime Returns […]

6.09 Adding and Subtracting Dates

DateAdd(interval, number, date) For adding and subtracting dates and times, the easiest function to use is the DateAdd() function. Using the DateAdd() function requires passing a string representing the type of interval to modify, the number of units to add or subtract, and the date to modify. There are a number of different strings that […]

6.10 Extracting Date Parts

DatePart(interval, date, firstdayofweek, firstweekofyear) The DatePart() function returns a number representing the part of the date that you specify using the interval argument. These intervals were listed in Table 6-11. Pass the interval as the first argument and the date as the second argument. //Get the current quarter DatePart(“q”, CurrentDate); //Returns a number 1 – […]

6.11 Creating Date and Time Variables

DateSerial( ) and TimeSerial( ) The DateSerial() and TimeSerial() functions can be used to create a date or time by passing the parts of the value as separate arguments to the function. The DateSerial() arguments are the year, month and day. The DateTime() arguments are the hour, minute, and seconds. In the simplest form, these […]

6.12 Custom Functions

Building Custom Functions This chapter has shown you a multitude of functions built into Crystal Reports. But these functions were designed to be utilized by a wide variety of people and they can’t possibly do everything you need them to do. Because of this, Crystal Reports gives you the ability to create your own custom […]

6.13 Understanding the Formula Extractor

Understanding the Formula Extractor The easiest way to create a new function is to use an existing formula as the template. Crystal Reports gives you the option of specifying an existing formula and having it extract the code from it and use it to create a new custom function. This has two benefits. The first […]

6.14 Using the Formula Extractor

Using the Formula Extractor The formula extractor is found within the Formula Workshop. When you create a new custom function you are given the option to use the editor to type it in directly or use the formula extractor. If you click the button Use Formula Extractor, it opens the Formula Extractor dialog box shown […]

6.15 Custom Function Optional Properties

Custom Function Optional Properties At the top right corner of the Formula Extractor dialog box is the Enter More Info button. Clicking on it opens the Custom Function Properties dialog box shown in Figure 6-2. This lets you set additional properties for the custom function. Figure 6-2. The Custom Function Properties dialog box. This dialog […]

6.16 Tutorial 60-1 Using the Formula Extractor

Tutorial 6-1. Using the Formula Extractor Let’s practice converting a formula using the formula extractor. As an added bonus, we’re going to convert a formula that breaks some of the rules of custom functions and see how to handle it. The formula we’re going to convert displays the histogram chart on the Crystal Reports sample […]

6.17 Writing Manual Functions

Writing Functions Manually Without a doubt, using the formula extractor makes creating functions easy. But it only works if you have existing functions already written. For those times when a formula doesn’t already exist, you have to write the custom function from scratch. Luckily, if you are adept at writing formulas, then writing custom functions […]

6.18 Using the Formula Expert

Using the Formula Expert The previous section made references to the Formula Expert dialog box, but unfortunately, you probably didn’t completely understand what it was because it had not been discussed yet. The Formula Expert is a tool that makes it easier for you to use the custom functions you’ve written. It lets you select […]

7.01 Formula Fundamentals

Formulas are used in Crystal Reports for enhancing report functionality as it prints. For example, Tutorial 5-1 showed how to use a formula to have the rows on the report alternate colors. Another example is suppressing a row by comparing a field against its current value. Crystal Reports gives you the option to program formulas […]

7.02 Simple Data Types

Simple Data Types Crystal syntax supports the standard data types that we expect in a language: number, Boolean, currency, string, date and time. Table 7-1. Data Type Default Values Crystal Data Type Basic Data Type Default Value NumberVar Number 0 CurrencyVar Currency $0 BooleanVar Boolean False StringVar String “” DateVar Date Date(0,0,0) – The Null […]

7.03 Declaring Variables

Declaring Variables Variables store a piece of data so that it can be used again within the formula. For example, if a calculation is performed multiple times in a formula, then you can store the result in a variable and reference that variable wherever it’s needed. This makes the formula easier to read and it […]

7.04 Global Constants

Using Global Constants One item that is very important, but I rarely see addressed, is the different ways to create global constants. A global constant is a variable that has a fixed value for the entire duration of the report. It stores a common number/string/date that can be shared among all the formulas. For example, […]

7.05 Assigning Variables

Variable Assignment After creating a variable, you need to assign a value to it. This can be a simple constant, a data field, or the result of a more complex calculation. The key thing to remember is that the data types on both sides of the assignment must be the same. For example, you can’t […]

7.06 Arrays

Array Data Types Arrays provide a means of storing a collection of data in a single variable and accessing each element of the array using an index. Think of it as storing data in a list and you can reference each item in the list using its position in the list. The first element is […]

7.07 Range Data Types

Range Data Types The Range data type is a very useful data type that allows you to store multiple values within a single variable. It is used to store a range within one variable and perform tests to see if another variable falls within that range. However, you can’t just store any values in this […]

7.08 Conditional Structures

Conditional Structures Conditional structures provide you with a way of testing one or more variables to see if they meet a certain condition. This could be testing if they are equal to a certain value or are within a range of values. If the test succeeds, then a code block is executed. If the test […]

7.09 Introducing Conditional Functions

Conditional Functions Conditional functions let you evaluate different conditions and return a value based upon the result. These are very similar to the conditional structures If Then and Select because both allow you to evaluate different conditions and perform some action. Conditional functions are different in that they evaluate all the conditions and return a […]

7.10 IIF()

The IIF() Function The IIF() function is a shortcut for the standard If statement. Its purpose is to put both the True and False actions on the same line. It consists of three parameters. The first parameter is the test condition. If the test condition is True, the function returns whatever is in the second […]

7.11 Choose()

The Choose() Function The Choose() function returns a value chosen from a list of values. The value returned is determined by an index that is passed to the function. This function is like a shortcut for the If Then statement and the Case statement. You can use it when the range of possible values is […]

7.12 Switch()

The Switch() Function The Switch() function is a slight variation of the Choose() function. The Switch() function uses pairs of test conditions and return values. The first parameter is an expression to test and the second parameter is a result value that is returned if the expression is true. This is repeated with every two […]

7.13 For Next Loop

Looping Structures Looping structures let you execute a block of code multiple times. The number of times this code block is executed depends upon the type of loop used and what happens within the code block. The looping structures covered are: For Next, While, and a variety of Do loops. For Next Loop The For […]

7.14 While and Do Loops

While and Do Loops The While and Do loops all have the standard syntax. The While keyword is used to continue looping as long as the condition evaluates to True. The While block is terminated with a Wend statement. The Do loops are terminated with a Loop statement. The Until keyword is used to continue […]

7.15 Conditional Expressions

Conditional Expressions When performing actions based upon how a condition evaluates, there are numerous ways to build the condition. For example, when writing an If statement, you can compare a field to a constant using a variety of relational operators and you can join multiple conditions using Boolean operators. This section shows you all the […]

7.16 Evaluation Time Defaults

Evaluation Time Defaults Chapter 1 discussed the Two-Pass Report Processing model. Knowing how this model works is especially important when writing formulas. The type of formula determines when it is processed. Where you place a formula on a report and the functionality within that formula also affects when the formula is evaluated and whether it […]

7.17 Common Evaluation Time Problems

To illustrate the importance of using the proper keyword in a formula, let’s look at an example that shows you each stage of writing a formula and how the output is affected by the evaluation time keyword. The example report is a customer report that uses the Xtreme.mdb database. The report has the first column […]

7.18 Advanced Tutorial Introduction

Advanced Tutorials When using conditional formatting to customize report output, the typical report property uses one of the simple data types to store its value. Many properties use the values True or False to turn them on or off. For example, properties such as Suppress, Italic, and Keep Object Together uses True to enable the […]

7.19 Default Attribute and Current Field Value

Using the Default Attribute and Current Field Value The value assigned to a property in design mode is called the property’s default attribute. When assigning a formula to that property, the default value is overridden by what is in the formula. There are many times when a formula is only used to specify what happens […]

7.20 Tutorial 7-1 Printing Checkboxes

Tutorial 7-1. Printing Checkboxes Displaying checkboxes on a report presents a new challenge. Checkboxes represent when a Boolean value is either true or false. But Crystal Reports doesn’t have a way to print checkboxes. There are other ways to display a Boolean constant. For example, you can display the words Yes/No or True/False by right-clicking […]

8.01 Formatting Techniques

Once you’ve mastered the basics of creating reports, it’s time to learn some of the finer points of report design. Crystal Reports gives you numerous formatting options for printing report sections for specialized purposes. For example, you can create specialized reports such as labels or multi-column reports. You also have the option of creating multiple […]

8.02 Formatting Sections

Formatting Sections Report sections have many formatting options. You can hide a section, force it to print at the bottom of the page, force a page break afterwards and many other options. Creative use of these formatting options gives you control over how the report looks. The Section Expert dialog box controls the formatting of […]

8.03 More Section Formatting

Hiding and Suppressing Sections Hiding a section is used for drilling-down on detail records. This presents the user with a much smaller report because the groups only show summary information. They can look at the detail information by double clicking on the group header. This creates a new tab in the viewer with the detail […]

8.04 Multiple Columns/Mailing Labels

Formatting with Multiple Columns/Mailing Labels The default layout of a report is designed so that each detail record uses the entire width of the page and each row is printed one after the other. Sections aren’t designed to only use a partial page width. However, if you want to print mailing labels or a multi-column […]

8.05 Adding Multiple Sections

Adding Multiple Sections Throughout this book, when different parts of a report were discussed, they were referred to by their section name. For example, when adding an object to be displayed in the header of a group, the portion of the report would be called the Group Header section. But what you weren’t told is […]

8.06 Tutorial 8-1 Eliminating Blank Address Lines

Tutorial 8-1. Eliminating blank address lines. A common problem with printing addresses is that not every line is always used. Every address has a line allocated for the street address, but some addresses need a second line for other miscellaneous information. This could be an “Attention:” comment or the suite number. If an address doesn’t […]

8.07 Tutorial 8-2 Adding Optional Sections

Tutorial 8-2. Adding optional sections. If you can use sections to suppress blank lines, it makes sense that you can use sections to do the opposite: add optional information. For example, a report can print a section just for unique circumstances. An employee report could print a special note if an employee’s birthday falls within […]

8.08 Tutorial 8-3 Suppressing Sections for a Repeated Field

Tutorial 8-3. Suppressing sections for a repeated field. Individual report objects have a Suppress If Duplicated property which suppresses the object if its data is the same as the previous record. This prevents the report from duplicating the same information multiple times. But sections don’t have this property because Crystal Reports would have to analyze […]

8.09 Designing with Report Templates

Designing with Report Templates Report templates make it easy to create reports that all have common formatting. This can save you a considerable amount of time because you don’t have to format the objects on a report individually. The template formats all the objects at one time. Report templates work by examining how the report […]

8.10 Custom Report Templates

Creating Custom Report Templates Being able to use existing reports as a template is a nice feature. But you can take this one step further by creating report templates from scratch. Custom templates are more flexible because the fields can be formatted to account for any data type. Creating a report template is very similar […]

8.11 Report Alerts

Report Alerts Report alerts prompt the user when certain report data falls within an unacceptable range. For example, report alerts are used to warn the user when inventory quantities fall below a certain level, when sales levels don’t meet a monthly quota, or when customer credit limits have been exceeded. The user has the option […]

8.12 Using Alerts in Formulas

Using Alerts in Formulas Crystal Reports gives you functions that let you work with alerts. You can find out if alerts are enabled, which records trigger the alert, and what the alert message is. The alert functions are listed in Table 8-2. Table 8-2. Report alert functions. Function Description IsAlertEnabled(“AlertName”) Returns True if the Enable […]

8.13 Tutorial 8-5 Creating a New Report Alert

Tutorial 8-5. Creating a New Report Alert Let’s see how easy it is to create report alerts by walking through a simple tutorial. This example uses a report that prints sales by product and also shows which country the purchase was made in. Management has been worried that sales within South Africa have come to […]

8.14 Tutorial 8-6 Using Parameters with Alerts

Tutorial 8-6. Using Parameters with Report Alerts Crystal Reports comes with an excellent example of a report that not only uses report alerts, but combines it with report parameters. The sample report, Alerting.rpt, prompts the user for a sales amount that they want to be alerted to. The user enters an amount and if any […]

9.01 Introducing Subreports

Have you ever worked on a report and wanted a way to show multiple reports one right after the other? For example, if you wanted to print a corporation’s annual report, you would first print the Balance Sheet, then the Income Statement and finally the Statement of Cash Flows. Each of these reports is different, […]

9.02 Are Subreports Necessary?

When are Subreports Necessary? If you aren’t familiar with subreports, it can be a little confusing at first to determine when they are needed. I said in the introduction that a subreport is useful for printing data from multiple groups of tables, but we already know that a regular report can print from a group […]

9.03 Types of Subreports

Types of Subreports There are two types of subreports available: Unlinked and Linked. The type defines the relationship between the report and the subreport as well as whether their data sources are related to each other. Unlinked subreports have no relationship to the main report. The data sources used on each report are completely unrelated […]

9.04 Adding a Subreport

Adding a Subreport Adding a subreport to a main report involves two steps: adding the report and setting the linking field. When adding the report you can choose to add an existing report or create a new report from scratch. If you create a new report from scratch, you will go through the Report Wizard […]

9.05 Editing the Subreport

Editing the Subreport Once the subreport object has been added to the main report, you will probably need to edit it. Depending upon the types of changes you want to make to the subreport, there are different ways of editing it. To edit the content of the subreport, go to the main report and either […]

9.06 Tutorial 9-1 Building the Customer Sales Report

Tutorial 9-1. Building the Customer Sales Report Before we practice using subreports, let’s first build the main report. This is going to be the foundation for all the tutorials throughout this chapter. It is a sales report which shows the detail sales record for each customer. This report will serve as the basis for the […]

9.07 How To Link Subreports

Linking Techniques To determine whether your subreports are going to be linked or unlinked, you first need to understand the different techniques available for linking reports together. That enables you to make an intelligent choice about which type of subreport to use. Even though unlinked reports aren’t officially related to each other, there are still […]

9.08 Linked Subreports

Linked Subreports Linked subreports require the main report to pass information to the subreport so that the subreport can filter its data. There are two ways that the reports can be linked to each other. You can use a field from the data source or use a formula for custom linking. Let’s look at the […]

9.09 Tutorial 9-2 Linking the Subreport with a Data Field

Tutorial 9-2. Linking the Subreport with a Data Field Let’s start working with subreports by creating a subreport that links to the main report using a data field. We are going to take the Customer Sales Report we created in the previous tutorial and list the customer credits on it. We’ll link the two reports […]

9.10 Linking with Formulas

Linking with a Formula Field Linking records in a subreport can also be done using a formula field. This gives you more creativity for how it is linked to the main report. There are many reasons for wanting to link with a formula field rather than a data field. When using a formula to link […]

9.11 Linking to Subreport Parameters

Linking to Subreport Parameters If the subreport has parameter fields in it, when the user refreshes the report they are prompted to enter values for the subreport parameters. If you don’t want the user to be prompted to enter a parameter value, you can link a field from the main report to the subreport parameter. […]

9.12 Linking to a Stored Procedure

Linking to a Stored Procedure with Parameters I recently saw two very similar questions about stored procedures posted to the book’s online forum. I thought they were excellent questions and that many people would benefit from all the details. Both questions said that they had a main report and a subreport that were linked to […]

9.13 Unlinked Subreports

Unlinked Subreports An unlinked subreport is used when you want to combine two or more reports onto one report and these reports don’t have any common data for creating a relationship between them. The unlinked subreport is completely independent of the main report and the main report’s data doesn’t affect the subreport. Figure 9-8 shows […]

9.14 Tutorial 9-3 Unlinked Company Header

Tutorial 9-3. Unlinked Company Header Let’s create an unlinked subreport that displays the company address in the report header. You might be tempted to think that it is easier to just type in the company address manually rather than going through the effort of adding a subreport. But there are benefits to using a subreport […]

9.15 Tutorial 9-4 Re-using Subreports

Tutorial 9-4. Re-using Subreports The subreport that you just created, Company Header, is completely independent of the detail data printed within the report. Since the company header is an unlinked subreport, it would be smart to re-use it on other reports. However, we know that subreports are saved within the definition of the main report. […]

9.16 More Unlinked Examples

Unlinked with a Formula Formulas are used with unlinked subreports so that information can be passed to the subreport without filtering any data. The benefit of using a formula is that although the subreport isn’t linked, you can still pass information from the main report to the subreport. This information can be displayed on the […]

9.17 Tutorial 9-5 Using Shared Variables

Tutorial 9-5. Using Shared Variables To illustrate how to use shared variables, let’s modify the example report shown earlier that lists the customer sales and credits issued to the customer. That report showed the detail sales entries and credit items, but doesn’t tie the two together. We want to modify this report so that it […]

9.18 Tutorial 9-6 Using Subreports for Hidden Calculations

Tutorial 9-6. Using Subreports for Hidden Calculations You learned in Chapter 1 that Crystal Reports uses a 2 pass processing model to generate reports. This can cause problems if you want to share data between two objects that are both processed in pass 2. An alternative around this problem is to use a subreport for […]

9.19 Troubleshooting Shared Variables

Troubleshooting Shared Variables Shared variables are a great way to pass data between the subreport and the main report. But you have to be careful because using them requires following certain rules. If you forget the rules, you’ll often find that either your calculations do not come out as expected or the report will print […]

9.20 Tutorial 9-7 Putting Subreports in Separate Sections

Tutorial 9-7. Putting Subreports in Separate Sections In this tutorial, we will examine the first two rules mentioned above and demonstrate why each subreport should be put in its own section. Open the report ‘Customer Net Sales.rpt’. The first rule we want to test is whether formulas in the subreport get calculated after the formulas […]

9.21 Tutorial 9-8 Resetting Subreport Variables

Tutorial 9-8. Resetting Subreport Variables One thing you might not have noticed in the previous report is that there is actually a problem with it. The subreport doesn’t reset the shared variable back to zero. If there is a customer without any credits, the net sales figure would be wrong because it will still be […]

9.22 Tutorial 9-9 Hiding Empty Subreports

Hiding Empty Subreports Another problem that you’ll eventually encounter with subreports is when they don’t have any data but they still print. You’ll see the column headers but no detail records. In fact, you can see this in the previous tutorial. The column headers for the credit report are there, but no data is displayed. […]

9.23 On-Demand Subreports

Using On-Demand Subreports By default, subreports are run at the same time as the main report. When you view or print the main report, the subreport information is printed as well. This may result in a performance decrease because the subreport could require just as much time, if not more, to process as the main […]

10.01 Introducing Databases

Throughout this book we practiced creating many sample reports in the tutorials. Within each tutorial, we always based the report off the Xtreme.mdb database and left it at that. Although this was done to keep things simple and focus on the topic at hand, there is so much more to what Crystal Reports can do. […]

10.02 Using the Database Expert

Using the Database Expert The Database Expert is where you do 90% of your work connecting to data sources. It lets you establish the connection to the data source, select the tables to print from, and make sure the tables are linked together properly. You get to see the Database Expert in two places. It […]

10.03 Common Data Sources

There are too many types to cover in this chapter, but let’s review some of the more common ones listed in Table 10-1. Table 10-1. Common Data Sources Data Source Description Acccess/Excel/Database Files Select a PC database/Excel file using its file location. ODBC (RDO) Connect using an ODBC driver by selecting an existing System DSN. […]

10.04 Connecting to MS Access

Connecting to MS Access Figure 10-3. Microsoft Access connection dialog box. Microsoft Access has a few different ways to connect to it. On the most simplistic level, you only need to enter a file path and filename for the Database Name property. It is easiest to click on the File Open button to the right […]

10.05 Connecting to MS Excel

Connecting to MS Excel Selecting a Microsoft Excel spreadsheet uses the same dialog box as Access, but it is easier to use. With Excel, all you have to do is set the Database Type property to be the version of Excel you are using and set the filename in the Database Name property. The difference […]

10.06 Connecting to SQL Server

Connecting to SQL Server SQL Server is a much more sophisticated and powerful database than MS Access. With its power comes more knowledge to work with it. So there are more variations on how to connect to the server and pass authorization credentials. SQL Server has two ways of connecting to it: ODBC DSN (Open […]

10.07 ODBC Connections

ODBC Connections To connect using ODBC, you need the name of an existing ODBC DSN. When you installed Crystal Reports, it automatically created the ODBC DSN called “Xtreme Sample Database 11″. To connect to it, on the Data tab of the Database Expert open the ODBC (RDO) folder. This immediately opens the ODBC dialog box […]

10.08 OLE DB Connections

OLE DB Connections Connecting to databases using an OLE DB connection is very common due to the overhead of maintaining ODBC connections. To create a new connection, go to the Data tab on the Database Expert and open the Create New Connection folder. Within it find the OLE DB (ADO) folder and open it. This […]

10.09 Viewing Available Tables

Viewing the Available Tables After selecting the data source to pull data from, you get to select the tables that have the data you want to print. Below the data source name is a list of the available tables, views and stored procedures to choose from. You might assume that this is a static list […]

10.10 Creating Favorite Data Sources

Creating a List of Favorite Data Sources Before leaving the Data tab of the Database Expert dialog box, you have the choice of saving the data source in your list of favorites. Just like your browser has a list of your favorite websites, Crystal Reports lets you keep a list of favorite data sources. If […]

10.11 Linking Tables

Linking Tables After connecting to the data source and selecting the tables you want to print, you proceed to the second tab of the Database Expert dialog box. This is the Links tab. This tab is only available when you select two or more tables. You need to link the tables together so that Crystal […]

10.12 Managing Table Links

There are a couple of buttons that are helpful for managing links. The Auto-Arrange button rearranges the tables on the screen into an easier-to-read layout. This is useful when handling a report with many tables. A multitude of tables makes it difficult to visualize the relationship with one another and the overall structure. If the […]

10.13 Join Options

Let’s look at the join options in more detail. Changing these options can result in totally different data being returned to the report and it is important to understand each one. Table 10-2 shows a list of the different linking options and how they affect the resulting data. Table 10-2. Join Options Join Type Result […]

10.14 Multiple Data Sources

Using Multiple Data Sources Complex reports often require printing data of different origins. This can happen when you have tables in a SQL Server database and they need to be linked to tables in an Oracle database or a MySql database. Although Crystal Reports is capable of printing such reports, it doesn’t always run flawlessly. […]

10.15 Connecting to Stored Procedures

Connecting to Stored Procedures As mentioned earlier in the chapter, stored procedures can be used as a data source just like a table. Crystal Reports can open a stored procedure, retrieve the data and print it. The benefit of using stored procedures is that they let you use the SQL language to process complex logic […]

10.16 Issues with Stored Procedures

Stored Procedure Concerns Since databases treat stored procedures differently from regular tables, there is an uncommon situations you should be aware of them in case you have a similar circumstance. It involves how you name stored procedure parameters. If you are using two stored procedures and linking them together, make sure that the stored procedure […]

10.17 SQL Statements

Working with SQL Statements Crystal Reports talks to databases using the computer language call Structured Query Language, commonly referred to as SQL. With SQL, Crystal Reports can specify the data that it wants to retrieve as well as how it should be filtered and sorted. As a user, you aren’t required to understand SQL because […]

10.18 Filtering Records

Filtering Records When printing records from one or more tables, you probably don’t need to print every single record. It is common to print only a subset of the original records. For example, rather than print every customer in the database, the report only prints customers that have been added within the past thirty days. […]

10.19 Grouping and Sorting

Grouping and Sorting Records The two SQL statements that control grouping and sorting are GROUP BY and ORDER BY. Grouping records with the GROUP BY statement is pretty easy. All you do is list the applicable fields after each keyword. For example, if you want to group by the Customer ID, then you would use: […]

10.20 Saving Report Data

Saving Report Data Crystal Reports lets you save the report data with the .RPT report file. This gives you two primary benefits. The first being that the user doesn’t need to have a live connection to the data source. This lets you email the .RPT file to someone working off-site and they can view the […]

10.21 Changing the Data Source

Changing the Data Source Many reports are very straight forward and all the tables come from the same data source. The tables that are used when you designed the report are the same tables that will be used when the report is put into production. But this isn’t always the case. It is common for […]

10.22 Changing PC-Style Databases

Changing PC-Style Databases PC-style databases are unique from database servers like SQL Server because they reference a physical file. This file can be located on your hard drive or on a shared network drive. The physical location of the database file can cause you problems when you distribute the file because everyone might not have […]

10.23 Verifying a Data Source

Verifying the Current Data Source Since a report is separate from the data source, it is very possible that someone can make changes to a database without you knowing it. For example, changing a field’s name causes the report to lose its reference to that field. Modifying a field’s data type can also affect how […]

10.24 Re-Mapping Fields

Re-mapping Fields When replacing one table with another table, it is possible that one or more of the fields on the report won’t have exact matches in the new table. If the fields in a table are renamed or deleted, this affects the existing fields on your report. When this happens, Crystal Reports gives you […]

10.25 Logging On and Off Databases

Logging On and Off of Databases When you create a new report and select the data sources in the Database Explorer dialog box, Crystal Reports automatically logs you onto the data source. This is so that it can find out what tables and stored procedures are there as well as getting a list of fields […]

10.26 Unlinked Tables

Using Unlinked Tables In most reports, when you add multiple tables to a report it is assumed that you will link them together. But Crystal Reports gives you the ability to use standalone tables in a report that aren’t linked to the other tables. At first this can cause problems because there is no synchronization […]

10.27 Setting a Table Alias

Setting a Table’s Alias When fields are added to a report, they are referenced by a combination of their table name and their field name. During the course of designing a report and making changes to it, the fields can have their tables renamed or the table can be replaced with a different table. If […]

10.28 Cancelling Reports

Cancelling Long Running Reports Some reports can take an exceptionally long time to process. This can be the result of querying the database for a large volume of data or the report having to process many records. If you run a report and find that it is taking too long to process you might want […]

11.01 SQL Performance

No matter what your reporting requirements are, at some point you will probably print a report that takes longer to process than you would like. And you’ll scratch your head and think to yourself, “There has to be a way to speed this up.” And you are right. In most cases, there are various techniques […]

11.02 Optimizing Field Selection

Optimizing Field Selection The SELECT statement determines which fields are selected from the tables and sent to Crystal Reports. Only fields listed after the SELECT keyword can be printed on the report. There are two ways to improve performance here. The first is to only select the records that you will print on the report. […]

11.03 Optimizing Record Selection

Optimizing Record Selection The WHERE clause of the SQL query specifies how records are filtered. It lists the fields to be filtered on and the criteria for specifying which records to select. This is what you do when using the Record Selection dialog box to specify which records you want displayed on the report. When […]

11.04 Optimizing Boolean Operators

Optimizing Boolean Operators There are some special considerations when using a selection formula that has multiple conditions. By default, when you use the Select Expert dialog box, each condition is joined using a Boolean And. For a record to be selected for printing it must meet all the criteria specified in the selection formula. If […]

11.05 Optimizing Grouping and Sorting

Optimizing Grouping and Sorting The GROUP BY and ORDER BY keywords specify how to group and sort the records. Just like performing record selections, this is a time intensive process that is best done on the database server. This section elaborates on the how-to of enabling reports to optimize grouping and sorting and the restrictions […]

11.06 SQL Expression Fields

Using SQL Expression Fields Throughout this chapter we have been referring to SQL expression fields as the best way to improve report performance. Let’s discuss them in more detail now. SQL expression fields are report formulas that only use SQL compatible functions. As mentioned in a previous section, using SQL expressions lets Crystal Reports pass […]

11.07 Custom SQL Statements

Writing Custom SQL Statements The Database Expert, discussed in Chapter 10, gives you the ability to create sophisticated data sources by linking tables from different types of data sources (tables, views, stored procedures). Being able to customize how these tables are linked together can not only impact report performance, but it can create new ways […]

11.08 Joining Tables with UNION

Joining Tables with UNION Earlier I mentioned that you can use the UNION keyword to join tables in ways that standard linking doesn’t allow, but I didn’t go into the details of how to do it. Although this book isn’t meant to teach you about writing SQL statements (there are already many books for that), […]

11.09 Stored Procedures and Views

Using Stored Procedures and Views Hundreds of books have been written on how to use SQL and optimize it. Although I’ve been working with SQL for two decades, I’m still not an expert on it. But, I do know that if you have to use a complex command object for a report, you are most […]

11.10 Inserting and Updating Data

Inserting and Updating Data with Crystal Reports A powerful feature of Crystal Reports is that it allows full access to the SQL language for manipulating databases. This means that not only can you pull information out of a database for printing, but you can also make changes to the live data in the database as […]

11.11 Tutorial 11-2 Creating a Report History Table

Tutorial 11-1. Creating a Report History Table As a report designer, a useful tool to have at your disposal is a list of each report that gets printed and when it was printed. You can review this list on a periodic basis to see how the reports are being used. The reports that are printed […]

11.12 Microsoft SQL Profiler

Analyzing SQL Using the Microsoft SQL Profiler There are two distinct stages when a report runs: generating the data and generating the report. Unfortunately, it isn’t very clear about how much time is being spent on each stage. If you have a report that needs to be optimized, how do you know if you should […]

11.13 SQL Expressions Reference

SQL Expressions Throughout this chapter we’ve discussed the different parts of a report that can be improved with SQL Expressions. The following table lists the different Crystal functions and their SQL Server equivalents. Although other databases are certain to have slight variations with their functions, they should be similar. You should consult your SQL documentation […]

12.01 Choosing a Chart

Being able to visualize data can have a tremendous impact on the reader. Compared to just printing raw numerical data, adding a chart to a report makes it possible for readers to quickly grasp the important relationships between data. Many times reports are used with proposals to sell a reader on an idea or plan. […]

12.02 Adding a Chart

Adding a Chart There are two ways to add a chart to a report: with the Report Expert and with the Add Chart Expert. If you are creating a report from scratch and you know in advance that it will use a chart, you can use the Report Expert to add the chart during the […]

12.03 Setting Data Points

Setting Data Points with the Data tab The Data tab is the primary interface for configuring the chart and it is fairly complex. It sets the fields that determine the coordinates of each axis as well as maps the data points. It also has four buttons for setting the properties of specialized charts. The Advanced […]

12.04 On Change Of Options

The On Change Of List The On Change Of list determines when a new element is shown on the chart. On a standard vertical chart, this would be the elements listed on the X-axis. For example, with a bar chart it determines when a new bar is drawn. If the selected field was Company Id, […]

12.05 Show Value Fields

Setting the Show Value Fields The Show Value list determines the values that are plotted on the chart. On a standard vertical chart, this is where an element is plotted along the Y-axis. For example, in a bar chart it determines how high a bar is drawn on a bar chart. For each field listed, […]

12.06 Adding Group Chartss

Adding Group Charts On the Data tab of the Chart Expert, below the Advanced button, is the Group button. It is used to create a chart that shows each group and one of its summary fields. It can only appear within the Report Header/Footer. When you click on the Group button, the dialog interface changes […]

12.07 Tutorial 12-1 Creating a Group Chart in the Page Header

Tutorial 12-1. Creating a Group Chart in the Page Header Let’s take a report that uses groups and print a bar chart in the page header that summarizes each group’s value. This gives the user a high-level overview of how each group compares to the other groups. The Crystal Reports sample report, Summary Group.rpt, groups […]

12.08 Customizing Charts in a Group

Customizing Charts in a Group Putting a chart in a group header implies that the chart will have the same look across all groups. In most cases, this is what you want because it creates consistency throughout the report. But there are times when you need to tweak the settings of one chart so that […]

12.09 Tutorial 12-2 Customizing Group Charts

Tutorial 12-2. Customizing Group Charts Let’s look at how to customize an individual group chart by walking through a tutorial on it. In the last tutorial, we took the Crystal Reports sample report, Summary Group.rpt, and added a chart in the Page Header. In this tutorial, we will place a gauge chart in the Group […]

12.10 Adding Cross-Tab Charts

Adding Cross-Tab Charts The Cross-Tab chart is similar to the Group chart because it takes one of the fields that the cross-tab object is grouped on and plots one of its summary fields. The related dialog box is shown in Figure 12-6. Figure 12-6. The cross-tab options of the Data tab. The On Change Of […]

12.11 Setting Capterions with the Text Tab

Setting Captions with the Text tab Use the Text tab to set the captions that appear on the chart as well as their fonts. This tab is shown in Figure 12-7. Figure 12-7. The Text tab of the Chart Expert. The Text tab is very easy to understand and modify. It consists of two sections. […]

12.12 The Options Tab

Using the Options Tab The Options tab, shown in Figure 12-8, has various miscellaneous properties for customizing the chart layout. Figure 12-8. The Options tab of the Chart Expert. There are four main areas on the Options tab. The Chart Color frame lets you set the chart to be in color or black and white. […]

12.13 Color Highlighting

Setting the Color Highlighting The colors that Crystal Reports applies to the chart objects may not be to your liking. You might want to set certain items to be a particular color, or apply colors depending upon the value that an item has. Crystal Reports lets you do this using the Color Highlighting tab. Figure […]

12.14 Chart Axes

Customizing the Chart Axes In most cases, the default formatting of the X-axis and Y-axis will be fine. But there are times when you will want to customize the axes to fit your reporting requirements. You can do things like turn the gridlines on or off, set the minimum and maximum value ranges, and set […]

12.15 Misc. Formatting Options

Miscellaneous Formatting Options The Chart Expert is the primary place where you create the type of chart to display and set the way it looks. However, Crystal Reports gives you additional ways to format charts at a more granular level. After inserting a chart onto a report, you can right-click it to see the options […]

12.16 Formating Individual Chart Objects

Formatting Individual Chart Objects The Chart Options dialog box you saw in the previous section applied to the chart as a whole. There are more Chart Options dialog boxes available for individual objects within the chart. For example, you can set the formatting for a certain group in a bar chart, change the fonts for […]

13.01 Understanding Cross-Tab Reports

Cross-tabs are a powerful tool for creating summaries of data in a spreadsheet style format. They generate summary data in a grid where the rows and columns represent groups of data. This provides the user with a report format that is easy to read and uses a small footprint on the page. Cross-tab reports are […]

13.02 Cross-Tab Limitations

As powerful as the cross-tab report is for summarizing data, it has many limitations. These limitations are discussed throughout this chapter, but let’s look at two obvious ones first. The first limitation is that the original grouping report has a lot of data on it that isn’t shown on the cross-tab. For example, the cross-tab […]

13.03 Planning Cross-Tabs

Planning the Rows and Columns of a Cross-Tab Object Creating a cross-tab object can be a little intimidating the first time you create one. It can be confusing trying to figure out what to put in the rows and columns. A little planning prior to creating the cross-tab object can really help ease the process. […]

13.04 Modifying Cross-Tab Properties

Modifying the Cross-Tab Properties After getting the empty cross-tab object on the report, you need to modify its properties so that it knows what data to display and how to display it. Right-click on the cross-tab object and select Cross-Tab Expert. This opens the Cross-Tab Expert shown in Figure 13-4. Figure 13-4. The Cross-Tab Expert […]

13.05 Placing the Cross-Tab Object

Placing the Cross-Tab Object If you look back at the cross-tab report example in Figure 13-2, you might notice that the cross-tab object as it appears in the report designer doesn’t look like the cross-tab grid as it appears when printed on the report. This is because the report designer shows the cross-tab object as […]

13.06 Tutorial 13-1 Creating a Cross-Tab Report

Tutorial 13-1. Creating a Cross-Tab Report Let’s create a simple cross-tab report to see the basic steps. We’ll create an annual sales report for customers. The first task is to plan the cross-tab object by determining which fields belong in the rows and columns. The report summarizes data by customer and sales year. Since there […]

13.07 Grouping Data within a Cross-Tab

Grouping Data within a Cross-Tab Just as reports can create groups and sub-groups on a report page, cross-tab objects can do the same. Cross-tab objects allow you to specify multiple data fields for the rows and columns. When you use multiple data fields, the cross-tab object shows multiple sets of data along a single row […]

13.08 Tutorial 13-2 Adding Groups to a Cross-Tab

Tutorial 13-2 Adding Groups to a Cross-Tab In this tutorial, we are going to explore how groups work by modifying the report from the previous tutorial. We are going to add grouping to both the rows and columns. The customers are going to be grouped by country. The column is going to be modified so […]

13.09 Formatting Cross-Tabs

Formatting the Cross-Tab Grid As with every report object in Crystal Reports, the cross-tab object has many formatting options to make it look just the way you want. These changes can be categorized according to whether they affect the grid layout or whether they affect the individual fields within the grid. Since the number of […]

13.10 Managing the Columns

Managing the Columns One of the most interesting features of the cross-tab object is that it is dynamic. The number of columns changes according to the data being displayed. As an example, assume that you have a cross-tab report where the columns represent the historical sales figures per year. If a company has been in […]

13.11 Introducing the Advanced Tutorials

Advanced Tutorials By now, you realize that cross-tab reports are a very powerful way of summarizing data and presenting it in an easy to read format. So far, we have been limited to basing cross-tabs on data fields, but there is so much more you can do with them. By using formulas and parameters as […]

13.12 Tutorial 13-3 Customizing the Cross-Tab Layout

Tutorial 13-3. Customizing the Cross-Tab Layout. The summary fields within most cross-tab reports show all the data as looking the same. This has the benefit of consistently producing a professional looking report each time. But in a large cross-tab report, it can sometimes be difficult to spot important information among the many pages of data. […]

13.13 Tutorial 13-4 Customizing Data within the Cross-Tab

Tutorial 13-4. Customizing Data Within the Cross-Tab Formulas can also be used to customize the summary data that the cross-tab report is based on. For example, a formula can be used to dynamically change the value that gets summarized or it can change the text that is displayed within the cross-tab. In this tutorial we […]

13.14 Tutorial 13-5 Customizing the Column Grouping with Parameters

Tutorial 13-5. Customizing the Column Grouping with Parameters Rather than formatting the cross-tab object based upon the data within the report, you can use parameters to let the reader choose how to customize the cross-tab. This tutorial customizes the cross-tab by modifying the grouping column. The report first asks the user whether they want to […]

13.15 Tutorial 13-6 Different Formulas for Each Row

Tutorial 13-6. Different Formulas for Each Row As I was finishing this chapter, someone posted a question on the forum that is perfect for this chapter. Rather than explain it, I’ll show the question here: Hi, I was asked to display this data in a cross-tab and I’m wondering if this is possible: We have […]

13.16 Tutorial 13-7 Performing a SQL Distinct

Tutorial 13-7. Performing a SQL Distinct Just to make sure that you have truly mastered the art of creating custom cross-tab functions, let’s add one more level of difficulty to the previous tutorial. In the last tutorial, we counted the total number of orders for the country USA. The formula returned a ‘1′ for each […]

13.17 Referencing Rows and Columns

Referencing Rows and Columns Tutorial 13-3 looked at how to format individual cells with the CurrentValue function. You saw how to use conditional formatting to change the cell properties based upon what was being printed. The cross-tab object also has a function that lets you determine which row and column the current cell is located […]

13.18 Using Arrays to Store Cross-Tab Data

Using Arrays to Store Cross-Tab Data Using the CurrentValue and GridRowColumnValue() functions gives you excellent control over formatting the cross-tab object. But you can get a lot more power by combining these two functions together. You can use them to store the entire cross-tab object in arrays. These arrays can be used to overwrite the […]

13.19 Tutorial 13-8 Copying a Cross-Tab into Arrays

Tutorial 13-8. Copying a Cross-Tab into Arrays For this tutorial, we are just going to focus on how to copy the cross-tab object into arrays. Since there are so many things you can do with these arrays, I’ll leave it up to you to customize it as your needs dictate. If you need more examples, […]

14.01 Mapping Overview

Using maps to display data gives your reports a powerful means of quickly communicating geographical information to the reader. This can consist of sales by region, population growth by state, or even green house gas emissions by country. Maps are familiar to everyone and make it easy to quickly visualize information without reading a lot […]

14.02 Creating Maps

Creating Maps Adding a map to your report is a pretty simple process and it only involves three steps: setting the data fields, choosing the map type, and assigning the map title. Let’s start out by examining the Map Expert and how to use it. To add a map to the report, choose the menu […]

14.03 Setting the Map Type

Setting the Map Type The second tab of the Map Expert is the Type tab. It lets you choose between five different ways of plotting data. The five types of maps to choose from are Ranged, Dot Density, Graduated, Pie Chart, and Bar Chart. Each one has a different purpose and you have to set […]

14.04 Ranged Map Type

Ranged Map Type The Ranged map type splits the numerical data into groups of values. Each group has a beginning and ending value and is represented by a specific color. Figure 14-5 is an example of a Ranged map showing the sales by US city. Figure 14-5. Range map showing sales by US city. The […]

14.05 Dot Density Map Type

Dot Density Map Type The Dot Density map type plots a dot on the map for every occurrence of a specified item. The areas of the map having more data points will have a larger number of dots displayed. This can be useful for seeing trends based upon geographical characteristics such as proximity to a […]

14.06 Graduated Map Types

Graduated Map Type The graduated map type is similar to the ranged map type in that each mapped area falls within a certain range and this range is plotted graphically at its location. The difference between the two is how they graphically represent that data point. The ranged type assigned a color to each range […]

14.07 Pie Chart Map Type

Pie Chart Map Type The pie chart map type shows how each data item compares to all the other data items being mapped. In other words, each item mapped is given a different percentage of the pie’s shape. The pie can display data in two different ways: by area or by category. If you map […]

14.08 Bar Chart Map Type

Bar Chart Map Type The bar chart map type is similar to the pie chart map type in that a bar chart is displayed on top of each area. The difference between the two is that the bar chart doesn’t show a percentage of the whole. Instead, each bar shows a scalar representation of each […]

14.09 Setting the Map Text

Setting the Map Text The last step of creating a map is setting the text. You can set the map title and change the text above the map legend. The Text tab is the third tab of the Map Expert. Figure 14-12. Setting the map text with the Text tab of the Map Expert. This […]

14.10 Tutorial 14-1 Adding a Map to your Report

Tutorial 14-1. Adding a Map to your Report Let’s walk through two tutorials of how to add a map to a new report. You’ll see that you can quickly create a map in just a few simple steps. We are going to create a map that shows Last Year Sales value for each country. From […]

14.11 Tutorial 14-2 Mapping with Pie Charts

Tutorial 14-2. Mapping with Pie Charts For the second tutorial, let’s get a little fancy and create a map type that plots multiple values: the pie chart. We learned earlier that the pie chart and bar chart types are only available when the Data tab has different fields for Geographic Area and On Change Of. […]

14.12 Viewing the Map

Viewing the Map When viewing the map on a computer, the reader has many options for changing the map and drilling down into more depth. The first thing to notice is that when viewing the report, the two visible map related objects are the map itself and the legend. However, when you click on the […]

14.13 Changing the Geograpahical Area Displayed

Changing the Geographical Area Displayed The default view displayed on the map is of the entire world. In many circumstances, you need to see a much smaller and more specific geographical area. For example, if your company only sells products in North America, then that is the only portion of the map you want to […]

14.14 Resolving Unmatched Map Areas

Resolving Unmatched Map Areas When choosing the field to map to the Geographical Area section in the Map Expert, you have to make sure that the data in that field is going to be what Crystal Reports expects. The names of cities, states and countries have to be exact. If there is any incorrect data, […]

14.15 All Maps are not Created Equal

All Maps are not Created Equal One thing that might cause you problems when you start changing the map view is that not all maps are created equal. What I mean by this is that not all maps have the same level of detail in them. Picking maps that are larger in scope (e.g. the […]

14.16 Changing Map Layers

Changing Map Layers When you initially view a map you are looking at it from a very high level. If you use the Zoom In option to make the map larger, you are effectively getting closer to the map. As you get closer and closer you will keep seeing more information on the map. It’s […]

15.01 Exporting Reports

Reports are written to communicate information to people. This could be as simple as a list of the CDs you have in your music collection or as complex as a set of financial statements for a multi-national corporation. While these reports can be for your own personal use, many reports are for others to read. […]

15.02 Export Formats

If you want the export to focus on keeping the report layout intact, this is referred to as page-based. If you want to export raw data for analysis, this is called record-based. Let’s look at which export formats are page-based and which are record-based. Table 15-1. Export formats Format Page or Record File Extension Adobe […]

15.03 Adobe Acrobat PDF

Adobe Acrobat PDF PDF files have become the de facto file format for electronic document distribution. It is the one format you can assume that everyone’s computer can read. Adobe’s software lets you view PDF files stored on your hard drive or directly off the internet. Crystal Reports has been exporting reports to the PDF […]

15.04 Crystal Reports Format

Crystal Reports Exporting to the Crystal Reports format is the same as saving the report and using the Save Data with Report option. There are no export options to choose from because the entire report is exported.

15.05 HTML

HTML Exporting to HTML is inherently different from exporting to the other file formats. HTML files are meant to be viewed in a web browser and this can impose certain requirements on how you present the data to the user. The first requirement is that you have a web server that can host the files. […]

15.06 HTML Options

Set the HTML version you want to export to by selecting the menu options File > Export > Export Report. After making your selection you are presented with the HTML export options dialog box shown in Figure 15-3. Figure 15-3. HTML export options. The first option is to set where you would want to save […]

15.07 MS Excel 97-2000

MS Excel 97-2000 Excel is the most popular spreadsheet program for Windows. It makes it easy for the everyday user to take a large amount of data and perform all types of analysis on it as well as creating pivot tables and charting the results. Exporting Crystal Reports to Excel is very popular because it […]

15.08 MS Excel 97-2000 Data Only

MS Excel 97-2000 Data Only The reason most people want to export to Excel files is so that the end user can work with the data, create custom formulas, and perform their own analysis within the spreadsheet. In fact, many people find that all the additional formatting provided by Crystal Reports gets in the way […]

15.09 MS Word, Editable RTF

MS Word / Editable RTF Exporting to MS Word gives you the ability to open your report in a Word document. You can make changes to the document and print out a new report. The one unique aspect of the export is that the file does not use a .DOC file extension. Instead, it uses […]

15.10 ODBC Databases

ODBC Databases Crystal Reports lets you export your report data into any ODBC compliant database. It creates a new record in the database for every detail row in the report. When exporting the data, it not only creates a field for every column in the detail section, but a field is also created for each […]

15.11 Record Style, Separated Values

Record Style, Separated Values, Tab Separated Text, Text Crystal Reports gives you a variety of ways to export reports to text files. The benefit to exporting to text files is that virtually any program that lets you import data can read text files. They are the least common denominator among software programs for sharing data. […]

15.12 Report Definition

Report Definition – Documenting Your Report The Report Definition format documents your report structure. It lists every section of the report and details its structure. Every formula and field used within the report is documented within the report definition file. You can save this file in a common folder on the network so that anyone […]

15.13 XML

XML XML (eXtensible Markup Language) is quickly becoming the standard for data exchange between applications as well as sharing data among corporations. XML is popular because it is a self-defining file. It uses a hierarchy of elements and each element has tags and values to define them. You can export a report to XML and […]

15.14 Internationalization

Internationalization In today’s world of global corporations, it is common for reports to be deployed to users in different countries. As a report designer, you want to create a single report and distribute it to all users without making any changes. Crystal Reports makes it possible for you to recognize the regional settings that the […]

15.15 Internationalization Options

For companies with many international reports, using a database is the ideal solution. Unfortunately, this has its own set of problems to contend with. You can’t use the ContentLocale formula in the record selection formula. It can only be evaluated in the second report pass after all the records have been read into the report. […]

A. 1 Introducing Financial Reporting

Of all the work I’ve done with reports and all the white papers I’ve read, I’ve never seen anyone discuss how to write financial reports. Yes, there are many examples of what financial reports look like and there are sample .RPT files you can download, but no one has actually documented the steps of how […]

A. 2 Basic Accounting Principles

Basic Accounting Principles Considering that many of us cringe at the idea of having to balance our check book (myself included), it wouldn’t be a surprise to if understanding debits and credits makes you want to run away screaming. But like it or not, learning what all those debits and credits do is very important […]

A. 3 Double-Entry Method of Accounting

The Double-Entry Method of Accounting Businesses record their financial transactions using the double-entry method of accounting. It classifies each dollar value as either a debit (DR) or a credit (CR) . The dollar amounts are listed in two columns with debits listed on the left and credits listed on the right. Debits are generally recorded […]

A. 4 Debits and Credits

Debits and Credits There are five account types: Assets (what the company owns), Liabilities (what the company owes to someone else), Revenues (how the company makes money), Expenses (how the company spends money), and Shareholder Capital (company stock). Double-entry accounting establishes rules for each account type that determines when an amount should be recorded as […]

A. 5 Contra-Accounts

Contra-Accounts An account type that hasn’t been mentioned yet is a contra-account. This is an account that offsets (or counteracts) the balance of another account. Contra-accounts have the opposite sign of the account they offset. For example, a contra-account for an asset will have a credit balance. This offsets the asset’s debit balance. Contra-accounts are […]

A. 6 Chart of Accounts

The Chart of Accounts The Chart of Accounts shows each account that a business uses in its financial transactions. It also lists the account number and generally a description. Although it is a simple list, it can be very lengthy because most companies have dozens, if not hundreds, of accounts. An account’s purpose is to […]

A. 7 Journal Entries

Making Journal Entries When bookkeeping used to be done manually, there were separate books for recording each type of business transaction. These books were called journals. There could be a Cash Journal, Sales Journal, Accounts Payable Journal, etc. Thus, the entries within a transaction became know as journal entries. As we discussed earlier, the journal […]

A. 8 Key Accounting Tables

Identifying Key Accounting Tables When given the task of creating a report, one of the first steps is identifying which tables will be used as the data source. The same applies to creating financial reports. There are three primary tables necessary for financial reporting: List of Account Types Chart of Accounts Journal Entries If you […]

A. 9 Sample Accounting Databases

Sample Accounting Databases To give you practice with learning how to identify the key tables within an accounting database, I’m going to use three different databases as examples. The first database is the sample installed with Crystal Reports XI R2. The other two databases come from software packages in use by businesses today. We’ll be […]

A.10 Crystal Reports XI R2

Crystal Reports XI R2 The version of Crystal Reports you are using with this book is probably the original version that was released in 2005. However, Business Objects actually updated this version of Crystal Reports and released a new edition called ‘R2′. This update was to make Crystal Reports compatible with the Microsoft development tools […]

A.12 MS Small Business Financials

Microsoft Small Business Financials Microsoft sells a financial software package tailored towards mid-sized businesses called Microsoft Small Business Financials. This is a robust accounting package used by many businesses. For the sake of this chapter, I’m just going to refer to it as MS Financials. You can find out more information at the following URL: […]

A.13 Quickbooks

Quickbooks Professional – QODBC Quickbooks is the most popular accounting software for small businesses. I currently use ‘Quickbooks Pro 2006‘ for my own consulting practice and I’m pretty happy with it. Although QuickBooks comes with a complete set of financial statements built into the software, I thought it would be fun to see how the […]

A.14 Account Types Table

Account Types Table This table lists the different account types that the accounts are grouped into. This is a pretty simple table. It has a primary key for identifying each record and it lists the account type. The actual names used are typically very similar to the account types listed in Table A-1: Assets, Liabilities, […]

A.15 Chart of Accounts Table

Chart of Accounts Table The chart of accounts lists all the accounts used in a company’s financial transactions. In the Xtreme.mdb database, this table is called “Account”. In addition to listing the account number and name, it also has fields that link to the other account related tables (type and class) and the balance to […]

A.16 Journal Entries Table

Journal Entries Table Finding which table stores the journal entries in the Xtreme.mdb database is pretty simple. It is called “Journal Entry”. The table lists the account number, a balance, whether it is a debit or credit, and a description. The MS Financials database doesn’t have any tables with the word “Journal” in them, but […]

A.17 Specifying Date Ranges

Specifying Date Ranges When creating financial reports, it is important to understand how the date range affects an accounts final balance. When printing most reports, you usually specify a date range that has a beginning date and ending date. But this rule doesn’t always apply when printing account balances on a balance sheet. The date […]

A.18 Balance Sheets

Balance Sheets When we discussed debits and credits earlier in this appendix, we learned that debits and credits must equal each other so that the accounts stay in balance. There is also an official formula, the Accounting Equation, which is used to show that all accounts are in balance. The accounting equation is defined as: […]

A.19 Handling Net Income

Handling Net Income A company’s net income is how much money they earned, less the expenses of running the company during that period. Near the bottom of the balance sheet, you can see that net income is shown as a single entry. This makes it appear to be a single account in the database, but […]

A.20 Tutorial A-1 Designing a Balance Sheet, Part 1

Tutorial A-1.Designing a Balance Sheet Now that you’ve learned the important aspects of the balance sheet, let’s get started with creating one. This is going to be more involved than the other tutorials in the book. Each step will have more in-depth explanations listed after it before moving to the next step. Please make sure […]

A.21 Tutorial A-1 Designing a Balance Sheet, Part 2

The problem I’ve found with writing financial reports for companies is that when given the initial specifications (and I use that term loosely), the person requesting the report says something like, “For the account descriptions and classifications, just use what is stored in the database.” I can tell you from experience that this is usually […]

A.22 Tutorial A-1 Designing a Balance Sheet, Part 3

There is an initial problem with the current report layout. There are too many account types listed. If you look back at Figure A-4, you’ll see that there are only two account types: Assets and Liabilities & Owners Equity. This corresponds to the Accounting Equation we discussed earlier. Another way to think about it is […]

A.23 Tutorial A-1 Designing a Balance Sheet, Part 4

The balance sheet is looking pretty good at this point, but there is one glaring problem: the Liabilities & Owners Equity section lists accounts for revenues and expenses. As discussed earlier, this has to do with the company’s net income and we need to handle it appropriately. To summarize the earlier section, if a company […]

A.24 Tutorial A-1 Designing a Balance Sheet, Part 5

At this point, the balance sheet is pretty much finished. The report balances, the accounts are in the correct categories, and net income appears as a single line within retained earnings. There are just a few additional changes we should make. We need to clean up the formatting a bit to make it look more […]

A.25 Income Statements

Income Statements The Income Statement, also known as the Profit and Loss Statement, tells you whether or not a company made money over a certain period of time. In its simplest form, it lists revenues and expenses and at the end it shows the net income (or net loss if the company doesn’t make money). […]

A.26 Income Statement Differences

The income statement looks very different compared to the balance sheet that we created in the previous tutorial. The important aspects of the income statement are as follows. Only income related accounts are used on the report. In contrast to the balance sheet which encompasses every account in the chart of accounts, the income statement […]

A.27 Multi-Column Income Statements

Multi-Column Income Statements Although the income statement frequently reports on the current year data only, it is also common to have the income statement show multiple years or even break down a single year into individual months or quarters. Having the income statement show multiple columns adds another level of difficulty to the report design. […]

A.28 Tutorial A-2 Income Statement Template, Part 1

Tutorial A-2. Designing the Income Statement Template This tutorial walks you through the initial steps of creating an income statement. This includes selecting the database tables and writing the formulas. It is used as the foundation for the next two income statement tutorials. The first step in designing an income statement is to define the […]

A.29 Tutorial A-2 Income Statement Template, Part 2

Now that you’re familiar with the database and which tables we’ll be reporting from, let’s create the report. Open Crystal Reports and choose Blank Report. A new report opens and the Database Expert is displayed. Under the Create New Connection folder locate the Access/Excel (DAO) option and click it. This opens the Access/Excel (DAO) dialog […]

A.30 Tutorial A-2 Income Statement Template, Part 3

The next step is to build the formulas that the report is based on. The problem I’ve found with writing financial reports for companies is that when given the initial specifications (and I use that term loosely), the person requesting the report says something like, “For the account descriptions and classifications, just use what is […]

A.31 Tutorial A-2 Income Statement Template, Part 4

For each field that is displayed on the report, create a formula that returns its value. The formulas that you need to create are listed in Table A-7. Table A-7. Initial balance sheet formulas. Formula Name Formula Account Class {Account_Class.Account Class} Account Class ID {Account.Account Class ID} Amount {Journal_Entry.Amount} These formulas are the easy ones […]

A.32 Tutorial A-3 Single Year Income Statement, Part 1

Tutorial A-3. Single Year Income Statement This tutorial walks you through the steps of creating an income statement that only displays a single year. It places all the report objects within the report designer so that you can format them however you wish. Since an income statement only shows summary data for each account class, […]

A.33 Tutorial A-3 Single Year Income Statement, Part 2

In the balance sheet report, we created a formula called Amount that was used in calculating the sub-total for each account. This formula used Table A-1 to determine whether a balance should be positive or negative based upon the account type and whether it was a debit or credit. The same rules that apply for […]

A.34 Tutorial A-3 Single Year Income Statement, Part 3

There is one last change that needs to be made to the report. If you recall, one of the rules of the income statement is that all balances must be shown as positive. Even though the expenses reduce the revenue balance and have negative values, the standard format of the income statement requires that they […]

A.35 Tutorial A-4 Multi-Year Income Statement, Part 1

Tutorial A-4. Multi-Year Income Statement This tutorial walks you through the steps of creating a multi-year income statement. It uses the cross-tab object to display multiple columns which summarize the account balances for each year. Using a cross-tab object for the income statement is fairly complex and is going to require advanced formatting techniques. You […]

A.36 Tutorial A-4 Multi-Year Income Statement, Part 2

The first problem with the cross-tab is that it only shows one year. This is because the record selection formula that was written in Tutorial A-2 is only allowing journal entries that have the same year as the report parameter. The formula should allow the past two years to be included in the output. We […]

A.37 Tutorial A-4 Multi-Year Income Statement, Part 3

Before getting into the details of creating the formulas, let’s first review the cross-tab specific functionality we need. This will help refresh your memory and it makes a good reference to look back on. Table A-9 lists the cross-tab formulas we’ll be using and gives a short description of how to use each one. Table […]

A.38 Tutorial A-4 Multi-Year Income Statement, Part 4

The next step is to fix the section balances. They are currently displaying the sub-total of their related account classes and we need them to display a running total that runs from the top to the bottom of the cross-tab. This requires creating a formula which calculates a manual running total. The difficulty is that […]

A.39 Tutorial A-5 Comparative Income Statement, Part 1

Tutorial A-5. Comparative Multi-Year Income Statement Anytime you create a report that shows two consecutive years side by side, someone always comes along and says, “Hey – can you add a third column that shows the difference between the two years?”. Income statements are no exception. In fact, having a third column to show the […]

A.40 Tutorial A-5 Comparative Income Statement, Part 2

If you preview the report, you’ll see that the balances in the prior year had all their balances negated. You will also notice that the Total column for the account classes is now accurate (this doesn’t apply to the sections yet). The Total column is showing the difference between the category columns. So far, so […]

A.41 Tutorial A-5 Comparative Income Statement, Part 3

If you preview the report, you should see that the balances for the account class sub-totals are shown as positive for both years. This is looking closer to the original income statement now. We need to make the same changes for the section balances because those amounts are still shown as negative. The difference is […]

B. 1 Introducing Basic Syntax

Chapter 7 taught you how to program using Crystal syntax. This appendix builds on that chapter by showing you details of the Basic syntax. It was written as a companion tutorial with Chapter 7 that shows what is unique about Basic syntax compared to Crystal syntax. It doesn’t try to teach you programming all over […]

B. 2 Data Types and Variables

Simple Data Types Basic syntax uses the same data types as Crystal syntax. However, the naming convention varies slightly. In Crystal syntax all data type have “Var” at the end (e.g. NumberVar, StringVar, etc.) Basic syntax doesn’t use the “Var” suffix after each data type. It simple uses the data type name by itself: Boolean, […]

B. 3 Arrays and Ranges

Array Data Types To declare an array variable in Basic syntax, use a set of parentheses in the variable declaration. Dim X() As String To reference an element of the array, use parentheses with the index number. X(1) = 5 Formula = X(3) The ReDim statement changes the number of elements in the array and […]

B. 4 Conditional Structures

Conditional Structures The If statement uses the standard syntax of testing a condition and performing one action if the condition is true and another action if it’s false. The code in the Else block is executed if the test returns false. Basic syntax also supports the ElseIf statement. An If block must always be finished […]

B. 5 Looping Structures

Looping Structures Looping structures let you execute a block of code multiple times. The number of times this code block is executed depends upon the type of loop used and what happens within the code block. The looping structures covered are: For Next, While, and a variety of Do loops. For Next Loop The For […]

B. 6 String Functions

Function Conversion Charts The following charts show the equivalent functions between Basic syntax and Crystal syntax. Though many are the same, each table does have a few variations listed. The tables use the same table numbers so that you know where they are in the book and you can reference the sections if you need […]

B. 7 Data Type Conversions

Table 6-5. Conversion Functions Basic Syntax Crystal Syntax CBool(number), CBool(currency) CBool(number), CBool(currency) CCur(number), CCur(string) CCur(number), CCur(string) CDbl(currency), CDbl(string), CDbl(boolean) CDbl(currency), CDbl(string), CDbl(boolean) CStr() CStr() CDate(string), CDate(year, month, day), CDate(DateTime) CDate(string), CDate(year, month, day), CDate(DateTime) CTime(string), CTime(hour, min, sec), CDate(DateTime) CTime(string), CTime(hour, min, sec), CDate(DateTime) CDateTime(string), CDateTime(date), CDateTime(date, time), CDateTime(year, month, day) CDateTime(string), CDateTime(date), CDateTime(date, time), […]

B. 8 Math Functions

Table 6-8. Math Functions Basic Syntax Crystal Syntax Abs(number) Abs(number) Ceiling(number, multiple) Ceiling(number, multiple) Fix(number, decimals) Truncate(number, decimals) Int(number), numerator \ denominator Int(number), numerator \ denominator Pi Pi Remainder(numerator, denominator), Remainder(numerator, denominator), numerator Mod denominator numerator Mod denominator Round(number, decimals),RoundUp(number, decimals) Round(number, decimals),RoundUp(number, decimals) Sgn(number) Sgn(number) Sqr(number), Exp(number), Log(number) Sqr(number), Exp(number), Log(number) Cos(number), Sin(number), Tan(number), […]

B.10 Date and Time Functions

Table 6-10. Date and Time Functions Basic Syntax Crystal Syntax CurrentDate, CurrentTime, CurrentDateTime CurrentDate, CurrentTime, CurrentDateTime DateSerial(year, month, day), DateTime(hour, minute, second) DateSerial(year, month, day), DateTime(hour, minute, second) DateAdd(interval, number, date) DateAdd(interval, number, date) DateDiff(interval, startdate, enddate, firstdayofweek) DateDiff(interval, startdate, enddate, firstdayofweek) DatePart(interval, date, firstdayofweek, firstweekofyear) DatePart(interval, date, firstdayofweek, firstweekofyear) MonthName(date, abbreviate) MonthName(date, abbreviate) Timer […]

1.01 Your First Report

Visual Studio .NET is the first Windows development environment that gives developers a fully integrated and robust reporting solution. Crystal Reports is now installed with Visual Studio so developers can write applications that have reports seamlessly integrated into them. Starting with Visual Basic 3.0, Crystal Reports was included with the language, but not part of […]

1.02 Windows Form Preview

Previewing with a Windows Form After creating the Employee List report in the previous section, you can preview it using either a Windows Form or an ASP.NET application. This section shows you how to preview it from a Windows Form. Previewing it with ASP.NET is covered in the following section. Previewing the report requires modifying […]

1.03 ASP.NET Preview

Previewing with ASP.NET Previewing a report with ASP.NET requires adding a CrystalReportViewer control to the web page and attaching it to a CrystalReportSource control. Create a new web project and open the default web page in design mode. Add a CrystalReportViewer control to it. The viewer is located in the Reporting tab near the end […]

1.04 Report Designer Overview

Examining the Report Designer Each report within your application is just like any other component that your application uses. It is listed in the Solution Explorer window as a class in your project. When you double-click it, it opens the report in design mode and you can make changes to it. Each report starts with […]

1.05 CrystalReportViewer Control

The CrystalReportViewer Control The CrystalReportViewer control is used on a form to display a report. You have to use this control when you want to preview and print a report. It is found in the form’s Toolbox as the last control listed. To access it, you have to use the down arrow to scroll down […]

1.06 Two-Pass Report Processing Model

Two-Pass Report Processing Model It’s quite possible that one of the most important parts of learning how to use Crystal Reports is understanding how Crystal Reports reads data and processes formulas while building a report. This is called the two-part report processing model and it is the reason why you can and can’t do certain […]

2.01 Steps for Creating a Report

Since Crystal Reports is fully integrated with Visual Studio .NET, you may be tempted to think that creating a new report is a simple matter that only takes a minute or two. While this is true after you have a little experience, creating a report the first time can be confusing if you don’t follow […]

2.02 Creating the Report Class

Creating a New Report To create and print a report, you need to create a new report for your project. Once It is created, you can modify it using the report designer and then call it from your application. Creating a new report is done two different ways. The first is to select the menu […]

2.03 Report Experts Explained

Using the Report Experts Depending upon your needs, creating reports can be a simple or complicated process. For example, it is very easy to print mailing labels from an address database or a form letter to a list of subscribers. On the other hand, it can be very complicated to write a report that uses […]

2.04 Data, Links and Fields Tab

The Data Page The Data tab is the first dialog box presented after you select which type of report you want. It lets you select the database and tables that store your data. The database can be a standard data source such as SQL Server or a non-standard data source such as an Excel spreadsheet. […]

2.05 Grouping, Summaries, Group Total, Chart Tabs

The Grouping Page Some reports have so much data on them that they can be a little hard to understand. When reports are dozens or even hundreds of pages long, you need to organize the information in a way that makes it easier to absorb the data in smaller pieces. You do this by creating […]

2.06 Record Selection, Report Style, Cross-Tab Tabs

The Record Selection Page Some reports need to only show a sub-set of all the data that is in a recordset. For example, a financial report may only show the corporate data for a single quarter or a range of quarters. To filter out certain data so that you limit how much information is shown […]

2.07 Setting the Designer’s Defaults

Setting the Designer’s Defaults The report designer is where you will spend all your time creating reports and modifying them. There are many different default settings that you can set that control how you interact with the report designer as well as controlling how you access and display data on a report. Knowing what the […]

2.08 Using the Report Objects

Using the Report Objects A report is very similar to a Windows form. Just like a form, the report is listed as a separate object under your project in the Solution Explorer window, there are various controls that can be added, and it is a class that has to be instantiated before using it. The […]

2.09 Adding, Resizing and Moving Report Objects

Adding, Resizing and Moving Report Objects The objects on a report work the same as the controls on a form. The objects that are listed in the toolbox can be added to the report by dragging and dropping them onto the proper section of the report. You can also double-click on them and they will […]

2.10 String Formatting

Formatting Strings String output can be formatted in a way similar to formatting a cell in a spreadsheet. You can modify its font and border. There are also formatting options that are specific to Crystal Reports. These options consist of suppressing the field, letting the width grow, rotating the text, paragraph specific formatting and hyperlinks. […]

2.11 Text Object, Field Object, Line Object, and Box Object

The Text Object The text object is used to display text, database fields, and special report fields. Each text object can display one of these or a combination of all three. After adding a text object to the report, click on it to edit it. When writing form letters, using the textbox object with a […]

2.12 Picture Object, Chart Object, and Hyperlink Object

The Picture Object The picture object is used for displaying the following image file formats: BMP, JPG, TIFF, and PNG. It doesn’t display GIF files. The formatting options are similar to the other controls in that it has the tabs Common, Border, and Hyperlink with similar functionality. There is a Picture tab, shown in Figure […]

2.13 Special Field Objects

The Special Field Object The Special Field object is used for printing all report-related information. For example, it can print the current page number or the total number of pages. This field was created as a catch-all for all the miscellaneous types of report information that you need. Table 2-3 lists the available fields and […]

3.01 Report Integration

After creating a report and formatting it to your satisfaction, the report needs to be integrated into your application. Understanding the best way to integrate reports into your application is essential for designing the best reporting solution. This makes chapter 3 one of the most important in the book. There are a lot of different […]

3.02 Integration Options

Summary of report integration options Whether you use the viewer control to preview reports or write the code to send them directory to the printer, there are many options for integrating reports into your application. They range from the simple drag-and-drop to the more complex coding techniques of working with the ReportDocument object model. The […]

3.03 Smart Task

Smart Tasks Visual Studio gives you a tool that makes programming applications easier. This tool is called a Smart Tasks panel. When you add a control to a form, a little arrow appears in the top-right corner of the control. Clicking on it triggers a panel to appear with a short menu list of common […]

3.04 Secure Databases

Connecting to Secure Databases If your report’s data source is a secure database, you need to take an additional step to pass the user credentials to the report. Otherwise, the user will always be prompted to enter their login information every time they preview the report. To connect to a secure data source, first make […]

3.05 Non-Embedded and Embedded Reports

Non-Embedded Reports When you save a .NET project, each report in the project is saved as a separate “.rpt” file. Although these files are saved in the same folder as your .NET application, they are independent of the project. In other words, you can copy them to another folder and use them in another project. […]

3.06 Previewing Reports in Windows

Previewing Reports in Windows Most applications give the user the ability to preview a report before printing it. With Crystal Reports, this is controlled by the CrystalReportViewer control. In fact, using the viewer is the only way to preview a report in your application. The viewer is found in the Toolbox at the bottom of […]

3.07 Previewing Reports in ASP.NET

Previewing Reports in ASP.NET The CrystalReportViewer control is the interface for previewing reports in an ASP.NET application. Adding a viewer control to a web page is as simple as dragging the control from the Toolbox onto the web page. After it is added to an ASP.NET page, it is represented by a simple rectangle. Figure […]

3.08 Differences with the Viewers

The ASP.NET viewer control has some interesting differences between it and the Windows viewer. It doesn’t have any tabs that run along the top. This is because it always displays new information on a new page. For example, if you drill-down into a new group in Windows, it creates a new tab to display the […]

3.09 Binding to the Web Viewer

Binding Reports to the Web Viewer After adding a viewer to the web page and setting its properties, the report needs to be bound to the viewer. As mentioned earlier, the easiest way to do this is by using the Smart Tasks panel. But if you wish, you can manually add a CrystalReportSource component to […]

4.01 Sorting

In Chapter 2, you saw how to build a report using the different report objects and tie it into a database. For creating simple reports this is all you need to know. But you will quickly find yourself developing reports that require more effort than listing records one by one. For reports that consist of […]

4.02 Grouping

Grouping Records When a report has a lot of pages, it is hard to quickly find information as well as get a general idea of what the report is telling you. Sorting the data helps you find a specific record, but it doesn’t give you high-level summary of what the data means. Grouping records lets […]

4.03 Top N Reports

Displaying Top N Reports An alternative to the standard grouping is to create reports that show the first or last set of records in a certain group. For example, rather than showing all the sales people for the company, you could show the 5 sales people that have the best sales for the month. Or […]

4.04 Hierarchical Reports

Displaying Hierarchical Reports A hierarchical report displays data to show relationships between records in the same table using a tree format. This is similar to having a self-join SQL statement that needs to join a table to itself using a common field. For example, when printing a supervisor list, you will show which employee reports […]

4.05 Drilling Down on Data

Drilling Down on Data Grouping data on a report gives you the added feature of letting you create drill-down reports. A drill-down report lets the user look at the detail records that make up a summary value. It takes a snapshot of the detail records that make up the summary and displays that snapshot on […]

4.06 Summarizing Data

Summarizing Data The purpose of grouping data is to make the report easier to read by creating categories that the detail records fit into. Within these categories you perform various summary functions on fields so that the user can get an overview of the information within the group without having to read all the data. […]

5.01 Adding and Inputting Parameters

As Crystal Reports has become increasingly advanced over the years, the number of ways to customize reports has also increased. However, passing data to a report hasn’t changed much. Parameters are still the preferred method of passing data to a report. This chapter explores how to create parameters, query the user for input, and use […]

5.02 Setting the Default Values

Setting the Default Values Default values give the user a list of values to choose from, which saves the user from the chore of memorizing all the available values that could be entered. Default values also restrict what can be entered. This prevents a user from making a typographical error. One drawback of using default […]

5.03 Entering Parameters when Running Reports

Entering Parameters when Running Reports When a report has parameters, the user is prompted to enter values when the report is loaded, but before it is printed. After the user enters the values, the report is shown in the viewer or sent to the printer. Figure 5-5 is the Enter Parameter Values dialog box. Figure […]

6.01 Selecting Records with the Select Expert

You’ve had the opportunity to create basic report formats, including sorting, grouping and adding parameters. This chapter takes that knowledge a little further by showing you how to add more customization to your report. This customization consists of filtering records, using report sections for advanced formatting techniques, and creating running totals. Once you are finished […]

6.02 Comparison and Logical Operators

Although most comparison operators only have a single dropdown box for entering values, there are a couple of exceptions. The is between and is not between comparisons give you two dropdown boxes. This lets you enter a beginning and ending range. The is between comparison is inclusive. The is not between comparison is exclusive. The […]

6.03 Selecting Records for Grouping

Selecting Records for Grouping Setting a filter on summary data isn’t done with the regular selection formula. Selecting records has the limitation that you can only set filters for raw data or basic formulas. You can’t set filters that operate on summary fields or on formulas built with summary fields. You also can’t use any […]

6.04 Formatting Sections

Sections Sections are used to determine where report objects will appear on a report. Each section has a different purpose and different rules that it follows to determine when and where it should appear on a report. For example, the Report Header section only appears at the top of the first page of a report. […]

6.05 Hiding and Suppressing Sections

Hiding and Suppressing Sections Hiding sections is used for drilling-down on detail records. As discussed in Chapter 4, you can design your report so that groups only display summary information. This presents the user with a much smaller report. If they are previewing the report with the CrystalReportViewer, they can look at the detail information […]

6.06 Forcing Page Breaks

Forcing a Page Break Page breaks are useful when you want groups to appear on their own pages. It is very common to want groups to appear by themselves so that data is listed separately from the other groups. An example is a report that has to be broken apart and distributed to multiple people. […]

6.07 Page Numbers, Keeping Sections Together

Resetting the Page Number Resetting a page number back to Page 1 makes the page appear as if it is the first page in the report. This is good to use in combination with forcing a page break after a section. When you distribute the pages of the report to different people, each person will […]

6.08 Suppressing Blank Sections, Underlaying Following Sections

Suppressing Blank Sections Printing sections that don’t have any data leaves blank rows in the report. This makes a report look unprofessional because of the gaps that seem to randomly occur. To fix this, set the option Suppress Blank Section. The report skips over any sections that don’t have any data and goes to the […]

6.09 Multiple Columns/Labels

Formatting with Multiple Columns The default layout of a report is designed so that each detail record uses the entire width of the page and each row is printed below the one before it. Sections aren’t designed to only use a partial page width. However, if you want to print mailing labels or a newspaper […]

6.10 Adding Multiple Sections

Adding Multiple Sections Throughout this book, when different parts of a report were discussed, they were referred to by their section name. For example, the part at the top of a group is called the Group Header section. However, reports also have Areas. By default, when you create a new report each area is composed […]

6.11 Handling Blank Sections

Example 6-1. Eliminating blank address lines. A common problem with printing addresses is that each one can have a different number of lines. Every address has a line allocated for the street address, but some addresses need a second line for other miscellaneous information. This could be an “Attention:” comment or the suite number. If […]

6.12 Suppressing Repeated Fields

Example 6-3. Suppressing sections for repeated fields. Individual report objects have a Suppress If Duplicated property which suppresses the object if its data is the same as the previous record. This prevents the report from duplicating the same information multiple times. But sections don’t have this property because Crystal Reports would have to analyze every […]

6.13 Swapping Sections, Alternating Background Color

Example 6-4. Swapping sections with each other. Dynamic formatting is implemented using multiple sections. They can be used so that they both have similar information, but they are formatted completely different. Only one section is printed at any given time and the other section is hidden. Set each section to print using the opposite logic […]

6.14 Running Totals

Running Totals Running totals are built-in fields that accumulate the total of another field. These fields save you the trouble of creating and maintaining a set of formulas that do the same thing. Since they are part of Crystal Reports, you have to do very little effort to use them in a report. A running […]

7.01 Writing Formulas

Formulas enable you to customize report output by analyzing the data in the report as it is being printed. There are two circumstances when you need to use formulas. The first is performing calculations based on the raw data. For example, when printing the monthly sales for a product, you can calculate the percent of […]

7.02 Using Formulas for Calculations

Using Formulas for Calculations A report gets the majority of its data from a data source. This could be a table in a database, an XML data feed or a proprietary data source. Data usually consists of raw data that doesn’t have extraneous information that can be derived by other means (e.g. calculations). It is […]

7.03 Dynamic Fomatting

Dynamic Formatting with Formulas Reports by their very nature are static. Although the printed data changes and the running totals are different every time, the report format stays the same. For example, if the first field in a column has a font of Arial and is black, every field in that column is also going […]

7.04 Adding Formulas

Adding Formulas When modifying a property in the designer, the changes you make to that property stay the same as the report runs. Most properties, but not all, can have a formula attached to them so that their value can be modified based upon other fields in the report. Clicking on the formula button next […]

7.05 Printing Checkboxes

Printing Checkboxes Displaying checkboxes on a report presents a new challenge. Checkboxes represent when a Boolean value is either true or false. But Crystal Reports doesn’t have a way to print checkboxes. There are other ways to display a Boolean constant. For example, you can display the words Yes/No or True/False by right-clicking on the […]

7.06 Default Attribute and Current Fields Values

Using the Default Attribute and Current Field Value The value assigned to a property in design mode is called the property’s default attribute. When assigning a formula to that property, the default value is overridden by what is in the formula. There are many times when a formula is only used to specify what happens […]

7.07 The Highlighting Expert

Use the Highlighting Expert? The Highlighting Expert is a simplified version of the Format Editor. It gives you a wizard interface for creating rules that modify the font and border of a field. Figure 7-10 shows an example of using the Highlighting Expert for drawing a double box border around a field when its value […]

7.08 Evaluation Time Defaults

Evaluation Time Defaults Chapter 1 discussed the Two-Pass Processing Model. Knowing how this model works is especially important when writing formulas. The type of formula determines when it is processed. Where you place a formula on a report and the functionality within that formula affects when the formula is evaluated and whether it returns the […]

7.09 .NET User Function Libraries (UFLs)

.NET User Function Library Crystal syntax gives you many built-in functions and even lets you write custom functions, but what if your project has an existing library of business logic that needs to be used in your reports? Will you need to rewrite these rules from scratch using Crystal syntax? Fortunately, the answer is no. […]

8.01 Basic Syntax

Formulas are used in Crystal Reports as a way to have enhanced control over a report as it prints. For example, if you want to have the rows on the report alternate colors, you can use a formula to change the background color of every other row. Another example would be using a field value […]

8.02 Formula Fundamentals

Formula Fundamentals This section on fundamentals of writing formulas covers Case Sensitivity, Writing Comments, Returning a Value, Using Data Fields, Declaring Variables, Simple Data Types, Array Data Types, and Range Data Types. Each topic has its own section. Case Sensitivity Basic syntax is not case sensitive. The variable FirstName is the same as the variable […]

8.02 Referencing Reports Fields

Referencing Report Fields Writing formulas requires referencing all types of data from your report and the databases that the report uses. The types of data that can be referenced consist of running total fields, the results of other formulas, and table fields. The syntax for referencing fields in a formula is to surround the field […]

8.03 Declaring Variables

Declaring Variables Declaring a variable follows the standard format of declaring the variable using the As keyword and then its data type. Dim var As datatype Crystal doesn’t reset its variables because of the way formulas and report sections work together. Formulas are usually added to sections of a report and a report section is […]

8.04 Simple Data Types and Null Values

Simple Data Types Basic syntax supports the standard simple data types that we expect in a language: Boolean, Number, Currency, String, DateTime, Date, and Time. Notice that rather than have a large number of numeric data types such as integer, double, etc., there is simply a single data type called Number. There is no need […]

8.05 Arrays

Array Data Types Arrays provide a means of storing a collection of data in a single variable and accessing each element of the array using an index. Unlike .NET arrays which are Base 0, an array in Basic syntax is Base 1. Thus, the first element is referenced using 1 as the index. The maximum […]

8.06 Range Data Types

Range Data Types The Range data type is a very useful data type that doesn’t exist in .NET. It allows you to store multiple values within a single variable. In .NET, if you want to be able to store a range of values that have a definite start and end, you have to declare two […]

8.07 If-Then, Select Case

Conditional structures provide you with a way of testing one or more variables to see if they are equal to a value or are within a range of values. If the test succeeds, a code block is executed. If the test fails, then a different code block is executed. Since there are many different circumstances […]

8.08 Conditional Functions

Conditional Functions Conditional functions let you evaluate different conditions and return a value based upon the result. These are very similar to the conditional structures If Then and Case because both allow you to evaluate different conditions and perform some action. However, conditional functions are different in that they can evaluate all the conditions in […]

8.09 IIF() Function

The IIF() Function The IIF() function is unique to Crystal reports. It is a shortcut for the standard If statement. Its purpose is to put both the True and False actions on the same line. It consists of three parameters. The first parameter is the test condition. If the test condition is True, the function […]

8.10 Choose() and Switch()

The Choose() Function The Choose() function returns a value chosen from a list of values. The value returned is determined by an index that is passed to the function. This function is like a shortcut for the If statement and the Select Case statement. You can use it when the range of possible values is […]

8.11 For Next Loop, While-Do Loops

Looping Structures Looping structures let you execute a block of code multiple times. The number of times this code block is executed depends upon the type of loop used and what happens within the code block. The looping structures covered are: For Next, While, and the various Do loops. For Next Loop The For Next […]

8.12 Conditional Expressions, XOR and IMP

Conditional Expressions When performing actions based upon how a condition evaluates, there are numerous ways to build the condition. For example, when writing an If statement, you can compare a field to a constant using a variety of relational operators and you can join multiple conditions using Boolean operators. This section shows you all the […]

9.01 String Functions

The Formula Editor in Crystal Reports gives you the ability to write very powerful functions. As you saw in Chapter 8, Crystal Reports lets you write functions to manipulate and analyze variables so that you can report on data specific to your business. In addition to writing your own formulas, Crystal Reports has dozens of […]

9.02 Manipulating Strings

Manipulating Strings It is common for a string to be modified before it is displayed on a report. This can consist of simple reformatting or even joining the different elements of an array into a single string. Basic syntax has many functions for manipulating string data. Table 9-4 shows the functions for manipulating strings. Descriptions […]

9.03 Converting Data Types

Converting Data Types Basic syntax is a type safe language that requires all constants and variables in the same formula to be of the same data type. It also requires you to pass constants and variables as parameters using the exact data type that the formula expects. Even though the data types in Basic syntax […]

9.04 Formatting Values

Formatting Values for Output When formatting output to be displayed in a report, the output is usually a combination of string data variables. As the last section stated, Basic syntax is a type safe language. As a result, you can’t concatenate strings with numbers or dates. To build an output string with a combination of […]

9.05 Math Functions

Math Functions Table 9-8. Math Functions Function Name Description Abs(number) Return the absolute value. Fix(number, decimals) Return a number with a specified number of significant digits. Int(number), numerator \ denominator Return the integer portion of a fractional number. Pi 3.14… Remainder(numerator, denominator), Return the remainder of dividing the numerator by the denominator. numerator Mod denominator […]

9.06 Random Numbers

Generating Random Numbers Generating random numbers using the Rnd() function. When you use random numbers, you normally want to tell the computer to generate a new random number sequence based upon some internal method (usually using the system clock). This still applies with Basic syntax. The Rnd() function has an optional parameter that lets you […]

9.07 Date and Time Functions

Date and Time Functions Table 9-10. Date and Time Functions Function Name Description CurrentDate, CurrentTime, CurrentDateTime Returns the current date and/or time. DateSerial(year, month, day), DateTime(hour, minute, second) Returns a date or time. DateAdd(interval, number, date) Increases the date by a certain interval. DateDiff(interval, startdate, enddate, firstdayofweek) Finds the difference between two dates. DatePart(interval, date, […]

9.08 DateAdd() Function

DateAdd( ) For adding and subtracting dates and times, the easiest function to use is the DateAdd() function. This is very similar to the functions AddDays(), AddMonths(), etc. found in the .NET DateTime class. Using the DateAdd() function requires passing a string representing the type of interval to modify, the number of units to add […]

9.09 DateDiff() Function

DateDiff( ) The DateDiff() function returns the difference between two dates or times. It can return the difference in different intervals. These intervals can be days, months, years or any of the intervals listed in Table 9-11. Be careful when using the DateDiff() function for calculating an interval other than the number of days. When […]

9.10 DatePart() Function

DatePart( ) The DatePart() function returns a number representing the part of the date that you specify using the interval parameter. These intervals are listed in Table 9-11. Pass the interval as the first parameter and the date as the second parameter. ‘Get the current quarter Formula = DatePart(“q”, CurrentDate) ‘Returns a number 1 – […]

9.11 MonthName(), WeekDayName() and WeekDay()

MonthName( ), WeekDayName( ) and WeekDay( ) Just like the DatePart() function, these functions are given a date value and they return part of the date. The difference is that these functions are more specialized than the DatePart() function. The MonthName() function is passed a number representing the month and it returns the name of […]

9.12 DateSerial() and TimeSerial() Functions

DateSerial( ) and TimeSerial( ) The DateSerial() and TimeSerial() functions can be used to create a date or time by passing the parts of the value as separate parameters to the function. The DateSerial() parameters are the year, month and day. The DateTime() parameters are the hour, minute, and seconds. In the simplest form, these […]

9.13 Timer Function

Timer The Timer function returns the number of seconds that have elapsed since midnight. This can be used for doing performance evaluations. Unfortunately, it is only significant to the nearest second. So it is only useful for analyzing reports that have lengthy run times. The following code demonstrates timing how long it takes a report […]

10.01 Choosing the Chart Type

Visualizing data can have a tremendous impact compared to just printing raw numerical data. Adding a chart to a report makes it possible for readers to quickly grasp the important relationships between data. Many times reports are used with proposals to sell a reader on an idea or plan. Adding a colorful chart can sell […]

10.02 Adding Charts

Adding a Chart There are two ways to add a chart to a report: with the Report Expert and with the Add Chart Expert. If you are creating a report and you know in advance that it will use a chart, you can use the Report Expert to build the chart while performing the initial […]

10.03 Selecting the Chart

Selecting a Chart with the Type Tab Use the Type tab to select the type of chart to display. A sample of each chart is shown in Table 10-2. The Chart Expert shows many examples of each chart to the right of the list. As you click on each chart type in the list, the […]

10.04 Data Points

Setting Data Points with the Data tab The Data tab is the primary interface for configuring the chart and it is fairly complex. It sets the location of the chart and the fields that determine the coordinates of each axis as well as map the data points. It also has three buttons for setting the […]

10.05 Evaluate Option

Setting the Evaluate Option The Evaluate option determines when a new element is shown on the chart. On a standard vertical chart, they would be the elements listed on the X-axis. For example, this determines when a new bar is drawn on a bar chart. There are three options to choose from and they are […]

10.06 Setting Show Value Fields

Setting the Show Value Fields The Show Value list determines the fields that are plotted on the chart. On a standard vertical chart, this is where an element is plotted along the Y-axis. For example, this determines how high a bar is drawn on a bar chart. For each field listed, another element is drawn […]

10.07 Field Requirements

Meeting the Minimum Field Requirements The Evaluate setting and Show Value setting work together to determine the X, Y and Z axis on each chart. Due to the fact that each type of chart can be unique in how it uses the fields in these settings to create the chart, it is important to know […]

10.08 Adding Group Charts

Adding Group Charts Below the Advanced button, on the Data tab of the Chart Expert, is the Group button. It is used to create a chart that shows each group and one of its summary fields. It can only appear within the Report Header/Footer. When you click on the button, the dialog interface changes to […]

10.09 Adding Cross-Tab Charts

Adding Cross-Tab Charts The Cross-Tab chart is similar to the Group chart because it takes one of the fields that the cross-tab object is grouped on and plots one of its summary fields. The related dialog box is shown in Figure 10-6. Figure 10-6. The cross-tab options of the Data tab. The On Change Of […]

10.10 Captions

Setting Captions with the Text tab Use the Text tab to set the captions that appear on the chart as well as their fonts. This tab is shown in Figure 10-7. It is only visible when the Type tab option to automatically set chart options is unchecked. Figure 10-7. The Text tab of the Chart […]

10.11 Using the Options Tab

Using the Options Tab The Options tab, shown in Figure 10-8, has a few miscellaneous options for customizing the layout of the chart. Like the Text tab, it is only visible when the Type tab option to automatically set chart options is unchecked. Figure 10-8. The Options tab of the Chart Expert. There are four […]

10.12 Using the Format Menus

Using the Format Menus Once a chart is finished and the Chart Expert is closed, two new menu items become available. By right-clicking on the chart object in design mode, you are given two formatting specific menu options: Format and Format Chart. The Format menu gives you the standard formatting dialog box that you’ve already […]

11.01 Understanding Cross-Tabs

Cross-tab reports are a powerful way to create summaries of data in a spreadsheet style format. They generate summary data in a grid where the rows and columns represent groups of data. This provides the user with a report format that is easy to read and uses a small footprint. Ask a programmer how he […]

11.02 Creating a Cross-Tab Object

Creating a Cross-Tab Object The name “Cross-Tab Report” is a little misleading. It makes it sound like the whole report only shows the cross-tab grid and that no other data is printed. This isn’t true. A cross-tab report refers to a report that has a cross-tab object in one of its sections. This object is […]

11.03 Adding Summary Fields

When you add a field to the Summarized Fields window, by default it assigns one of two different summary functions to the field. If the field is numeric, then the Sum() function is used. If the field is text, the Count() function is used. You can change the default summary function after it has been […]

11.04 Placing the Cross-Tab Object

Placing the Cross-Tab Object If you look back at the cross-tab report example from earlier in the chapter, you might realize that the cross-tab object as it appears in the report designer doesn’t look like the cross-tab grid as it appears when printed on the report. This is because the report designer’s cross-tab object is […]

11.05 Formatting

Formatting the Cross-Tab Grid As with every report object in Crystal Reports, the cross-tab object has many formatting options to make it look just the way you want. These changes can be categorized according to whether they affect the grid and its layout or whether they affect the individual fields within the grid. Since the […]

11.06 Managing the Columns

Managing the Columns One of the most interesting features of the cross-tab object is that it is dynamic. The number of columns changes according to the data being displayed. As an example, assume that you have a cross-tab report where the columns represent the historical sales figures per year. If a company has been in […]

12.01 Linking Subreports

A limitation of the standard report is that only a single view of the data can be displayed. Subreports are used to create multiple views of data on a single report. Rather than create one or more reports to present additional views of the data, subreports are used to present data that is independent of […]

12.02 Linking via a Data Field

Linking with a Data Field Linking subreports with a data field lets you filter the data in the subreport based upon the data that is in the main report. This is useful when you are printing data derived from tables that have a parent-child relationship. First let’s look at when a subreport isn’t the best […]

12.03 Linking via a Formula Field

Linking with a Formula Field Filtering records in a subreport is also done using a formula field. There are two benefits to using formula fields: they give you more flexibility because formulas can be customized to parse or join multiple data fields, and they can link non-indexed fields from PC databases. Being able to parse […]

12.04 Using Unlinked Subreports

Using Unlinked Subreports An unlinked subreport is used when you want to combine two or more reports onto one report and these reports don’t have any common data to create a relationship between them. The unlinked subreport is completely independent of the main report and the main report’s data doesn’t affect the subreport. Figure 12-2 […]

12.05 Adding a Subreport

Adding a Subreport A subreport is added to the main report in the same way that the other report objects are added: using the Insert menu item. Select the menu options Crystal Reports > Insert > Subreport. This gives you the outline of a subreport object attached to the mouse cursor and you move the […]

12.06 Linked with a Data Field

Linked with a Data Field Linking subreports with a data field lets you use a data field in the main report to filter the data in the subreport. The two fields must be of the same data type to link them together. If this is a PC database, both fields must have an index already […]

12.07 Linked with a Formula Field

Linked with a Formula Field Linking a subreport with a formula lets you have more creativity for how it is linked to the main report. You can add a formula to the main report to convert the data type of a data field so that it is compatible with the field in the subreport. You […]

12.08 Unlinked with a Formula

Unlinked Subreports An unlinked subreport doesn’t have a connection to the main report. It is totally independent. To create an unlinked subreport, do not set any properties on this dialog box. If you had already added fields to the listbox on the right, remove them and this will remove any links between the main report […]

12.09 Unlinked with Shared Variables

Unlinked with Shared Variables Shared variables can be used to pass data between the main report and the subreport. The difference between using a parameter field and a shared variable is that shared variables can be used to pass data in both directions. When using parameter fields, data can only be passed from the main […]

12.10 Editing the Subreport

Editing the Subreport Once the subreport object has been added to the main report, you will probably need to edit it. Depending upon the types of changes you want to make to the subreport, there are different ways of editing it. To edit the content of the subreport, from the main report either double-click on […]

12.11 On-Demand Subreports

Using On-Demand Subreports By default, subreports are run at the same time as the main report. When you view or print the main report, the subreport information is printed as well. This may result in a performance decrease because the subreport could require just as much time, if not more, to process as the main […]

13.01 Connecting to Databases

The backbone of every report is the data it prints. Large corporations merge data on servers into reports that consolidate and chart information from a dozen or more tables. Small businesses optimize their report distribution and expand their client base by providing their data in an XML format and letting companies from around the world […]

13.02 Implementing the Pull Model

Implementing the Pull Model The Report Expert uses the simplest form of the Pull Model to create reports. Within the Report Expert is the Database Expert, where the data source(s) that the report connects to and the tables that have the data are defined. The Report Expert generates a default report layout and builds, behind […]

13.03 Linking the Tables

Linking Tables Whenever there are two or more tables, they need to be linked so that Crystal Reports knows how they are related. For example, a pet store that wants to print a list of its products by Animal ID needs to build a report using an animal table and a product table. To match […]

13.04 Using Multiple Data Sources

Using Multiple Data Sources Complex reports often require printing data of different origins. This can happen when you have tables in a SQL Server database and they need to be linked to tables in an Oracle database. There can also be SQL Server databases on different servers on the network. Although Crystal Reports is capable […]

13.05 Secured Data Sources and Stored Procedures

Secured Databases Most databases have security implemented in them. They require valid user credentials before accessing the data. This means that you have to pass a User ID and Password to the database prior to printing the report. Crystal Reports handles security differently, depending upon how the report is bound and the database used. In […]

13.06 SQL Statements

Working with SQL Statements Reports connect to databases, which in turn, return a set of records. The portion of the report’s design that works with data is translated into a SQL statement that is syntactically valid for each specific data source. Crystal Reports gives you many ways to customize the SQL statement that is passed […]

13.07 Filtering Records

Filtering Records When printing records from one or more tables, you probably don’t need to print every single record. It is common to print only a subset of the original records. For example, rather than print every customer in the database, the report prints customers that have been added within the past thirty days. SQL […]

13.08 Grouping and Sorting

Record Grouping and Sorting When Crystal Reports generates a report that uses fields for grouping and sorting, it has to collect all the data within the client’s computer and process each record. It takes a lot of resources to organize and sort each record as well as perform any necessary summary calculations. This section elaborates […]

13.09 SQL Expressions

Using SQL Expressions SQL Expressions are report formulas that only use SQL compatible functions. As mentioned in the last section, many formulas written with Crystal syntax don’t have a SQL compliant equivalent. Thus, the report engine has to take the data returned from the server and process it on the client’s computer. SQL Expressions alleviate […]

13.10 Changing the Data Source

Changing the Data Source Many reports are very simple and all the tables come from the same data source. The tables that are used when you designed the report are the same tables that will be used when the application is put into production. But this isn’t always the case. It is common for the […]

13.11 Verifying and Re-mapping Fields

Verifying Changes to the Data Source If you make changes to a table and its fields using the management console of the database, this will impact the reports that use that table. Modifying a field’s data type can affect how the report formats the data. Changing a field’s name causes the report to lose its […]

13.12 Setting a Table Alias

Setting a Table’s Alias When fields are added to a report, they are referenced by a combination of their table name and their field name. During the course of designing a report and making updates to it, the fields can have the table renamed or the table can be replaced with a different table. If […]

13.13 Printing from MS Excel

Printing with MS Excel Files One of the data sources that you can print from is a MS Excel file. Crystal makes it simple to print using the worksheets within a spreadsheet file. It references each worksheet as if it were a table within the data source. However, there are a few quirks that you […]

14.01 Introducing the Report Objects

Part I of this book taught the details of how to use the Crystal Reports embedded designer to create professional reports. When a report is complete, its design and layout stays the same every time it was run. The data that it prints will change, but the report format is locked. With Crystal Reports .NET, […]

14.02 Steps for Using Reports

Basic Customization No matter what type of runtime customization you want to perform, customizing reports always starts with the same premise: declare and instantiate a ReportDocument object, modify its properties, and print or preview it. These steps are discussed in more detail next. As discussed in Chapter 3, Windows applications allow you to use either […]

14.03 Base Windows Code Templates

Listing 14-1. Windows client, embedded report, Crystal viewer [VB.NET] Dim myReport As New CrystalReport1 CrystalReportViewer1.ReportSource = myReport [C#] CrystalReport1 myReport = new CrystalReport1; CrystalReportViewer1.ReportSource = myReport; In this code, the following assumptions are made: there is an embedded report called CrystalReport1, and that a viewer control has been added to the current form. The report […]

14.04 Printing to the Printer

Sending Reports Directly to the Printer If you want to send the report directly to the printer without previewing it, call the PrintToPrinter() methodWhen you call this method, the report is sent directly to the printer without any user interaction. This is useful when generating reports in a batch format or if you are creating […]

14.05 Where to Put Your Code

Locating the Report Code The previous code listings only show you the steps for previewing/printing the reports in a Windows form. But they don’t show you where you should put the code in your form. You can put this code anywhere in the form and it will work fine. For example, if you want to […]

14.06 Basic ASP.NET Code Templates

Printing from ASP.NET Websites Printing reports within an ASP.NET application requires a slightly different coding template than the previous listings. Reports shown on an ASP.NET page are unique in that each time the user moves to a new report page, the web page gets reloaded. If a report is resource intensive, this can slow performance […]

14.07 Disposing of Report Objects

Disposing of the Report’s Memory Web applications that produce a high volume of reports frequently have a problem with running out of memory. This is because reports require a lot of memory to process them as well as store the actual report object in memory. Properly disposing of a report’s memory footprint on the server […]

14.08 ReportDocument Object Model

The ReportDocument Object The ReportDocument class is the base class for all reports. Its properties give an application the ability to thoroughly examine all the report objects. Many of these properties, but not all of them, have write capabilities so that you can modify their values. Each report is a class that inherits from the […]

14.09 Summary Information Class

Retrieving Summary Information Every report has a variety of summary information saved with it. This information is set by the report designer and it usually consists of things such as the report author, the report title and report comments. This information is set during design mode by right-clicking on the report and selecting the menu […]

14.10 Report Option Properties

Setting the Report Options The ReportOptions class only has a few properties that can be set. They are listed in Table 14-3. This information is set during design mode by selecting the menu options Crystal Reports > Design > Default Settings and selecting the Reporting tab. These properties were discussed in Chapter 2. Table 14-3. […]

14.11 Modifying Print Options

Modifying the Printing Options The PrintOptions class stores the options for how a report is sent to the printer. This can consist of the destination printer, the paper orientation or the page margins. This is normally set during design mode. While the majority of an application’s reports will use the same settings, you can override […]

14.12 Other Report Classes

Connecting to the Data Sources The Database class is used for examining the tables used in a report and their relationships with each other. It’s also used for setting the login information before opening the report. This class is described in Chapter 17. Exporting Reports If your application only sends reports to the printer, it […]

14.13 Programming the CrystalReportViewer

CrystalReportViewer Object Model From the user’s perspective, the CrystalReportViewer control is only used to preview reports. But, from the programmer’s perspective, it offers much more. The CrystalReportViewer object model, shown in Figure 14-x, has properties and methods that give you the ability to modify the report object, change the look and feel of the viewer, […]

14.14 Event Handling

Responding to Events Report classes are built with events that let you respond to the actions that the user is doing as they preview a report. Your application can subscribe to these events and be alerted when they occur. Since a user can only preview a report using the CrystalReportViewer, the events are written for […]

14.15 Runtime Report Rendering Events

Runtime Report Rendering Events The ASP.NET viewer has events that let you track the progress of the report’s rendering process. This is useful when your application needs to know the exact moment a report is finished rendering. For example, you could write an entry to a report log file upon completion or queue up another […]

14.16 ViewInfo Class

Crystal Reports also gives you a class called ViewInfo for deriving more information about the report’s status. It lets you find out if the user is viewing a page in the main page, a subreport, or a drill down view. You can also determine the current page number and the last page number (if it […]

14.17 Exception Handling

Handling Exceptions Crystal Reports gives you the ability to handle any errors that might occur while printing and previewing. The benefit to handling the error yourself is that you can customize the error handling process. For example, you could write the error to a log file or you can gracefully exit the process without throwing […]

15.01 Changing Record Selection

This chapter builds on the basic report customization shown in Chapter 14. It focuses on working with the DataDefinition and ReportDefinition classes. The DataDefinition class controls Record selection, Grouping and Sorting, and Parameters/Formulas.  The ReportDefinition class handles the functionality for report sections, grouping/sorting and running totals. With respect to the number of objects contained […]

15.02 Mapping the ReportDefinition Classes

Mapping the ReportDefinition Classes The ReportDefinition class manages everything printed on a report. It has collection classes for storing each area, section, and object shown on the report. You can map out every detail about your report. Leaning how to map these collections teaches you how they are organized. This is necessary for learning how […]

15.03 Code Listings for Mapping the Report Classes

The sample program is a Windows program that uses a listbox, lstMapping, for the output. Each property is printed as a string in the listbox. Indentation is used to offset the object from its properties so that it is easier to read. Notice that all the parameters and variables are declared using the full namespace. […]

15.04 Programming the ReportDefinition Objects

Programming the ReportDefinition Objects The ReportDefinition classes are used for modifying all the report objects. With every object, you can modify the formatting properties to change its appearance. Some of the objects, such as the TextObject and FieldObject, can have their content modified. This isn’t the case for every report object. This section shows you […]

15.05 ReportObject Properties

Modifying the Report Object Properties The Area class and Section class have similar properties to each other. You can enable or disable formatting properties such as suppressing the object, keeping the object on the same page, and printing it at the bottom of the page. The difference between the two is that every section has […]

15.06 Grouping and Sorting Classes

Grouping and Sorting Data The Crystal Reports object model lets you examine how a report is sorted, grouped and what type of data is summarized. You can change many of these properties prior to running the report. Since the sorting and grouping features of a report are so closely related, it makes sense that their […]

15.07 Code Listings for Grouping and Sorting Objects

Mapping the Grouping and Sorting Objects To learn how to use the different sorting and grouping classes, let’s start with some generic procedures that traverse the collections in a report and print out the various properties. The code isn’t very complicated because it primarily consists of For Each loops to traverse the collection objects and […]

15.08 Modifying the Grouping and Sorting Objects

Programming the Grouping and Sorting Objects The sorting and grouping objects in a report can be modified during runtime. You can use a form to have the user enter criteria to modify the default properties of a report and your program can change the report to meet these criteria. Modifying the objects during runtime is […]

15.09 Summary Field Objects

Understanding the Summary Field Classes The summary field classes are referenced with the SummaryField property of the DataDefinition class. Figure 15-13. The Summary Field classes. The SummaryField property is a collection of SummaryFieldDefinition objects. Each object contains all the information about each summary field: the field, the summary operation, etc. Unfortunately, it is missing one […]

15.10 Subreport Modification

Modifying Subreports Modifying the properties of a subreport is the same as modifying the properties of any report. Throughout this book there is code for modifying report objects such as the fields, groups and formatting, and there is code for setting login information. Every line of the code samples can be used on a subreport […]

16.01 Introduction to Formulas and Parameters

There is a bug in Crystal Reports that will wipe out any changes you make to parameters or formulas IF you make them prior to calling the SetDataSource() method of the ReportDocument object. To ensure that your parameter/formula changes are saved with your report, you need to make sure your code comes AFTER calling the […]

16.02 Modifying Formulas

Modifying Formulas To override a formula, pass the formula name to the FormulaFields collection and assign the new formula to the Text property. The tricky part is knowing the proper way to assign data to the formula. Formulas are always stored internally as a string and the syntax for storing each data type to the […]

16.03 Modifying the Current Values in a Parameter

Modifying the Current Values in a Parameter A problem with parameters is that the user interface for prompting the user to enter a value isn’t very exciting. Crystal Reports uses its own dialog box to prompt the user and it is generic and bland. Fortunately, Crystal Reports .NET lets you override a parameter’s default behavior. […]

16.04 Parameter Data Types

Parameters have many different types of data that can be assigned to them. A parameter can hold a discrete value (a number, string, etc) or it can hold more complex values such as range values, multiple values, and a combination of discrete and range values. Assigning data to a parameter becomes increasingly complex as you […]

16.05 The Parameter Classes

Learning the Parameter Classes Up to this point in the chapter, all the work you’ve done with parameters used the SetParameterValue() method. This method is useful because it is easy to use and it covers over 90% of the work that most people will do with parameters. If the previous sections showed you everything you […]

16.06 Default Values and Current Values

Defining Default Values and Current Values Before you can use the parameter classes to pass values to the parameter objects, you need to understand how values are classified. When a report with parameters loads, the user is shown a dialog box to enter values for each parameter. The dialog box frequently shows a pre-populated list […]

16.07 Programming the ReportDocument Class

Programming with the ReportDocument Class The ReportDocument class manages every report object in the report. It has a collection class that is fully populated with every report parameter. Each parameter object can be assigned a value prior to printing the report. To set a parameter’s value, first get a reference to parameter field object. In […]

16.08 Programming the CrystalReportViewer Control

Programming the CrystalReportViewer Control As mentioned earlier in this chapter, the viewer control should only be used to modify the display properties of the viewer and shouldn’t be relied upon to make report changes. However, the viewer does have this functionality built into it, so we’ll cover it here so that you know how to […]

16.09 Subreports

Modifying Subreport Parameters As you saw earlier in this chapter, if you want to modify the parameters in a subreport, you can pass the subreport name to the SetParameterValue() method. Of course, this method is only available with the ReportDocument class. If you want complete access to all the properties of a subreport’s parameters, you […]

17.01 Introduction To Dynamic Data Sources

Chapter 13 showed you how to connect to data sources using the Visual Studio IDE. This consisted of using the report wizards to select the data source, specify the fields to print, and work with SQL statements. This chapter focuses on showing you how to log on to secure data sources and change the data […]

17.02 Logging on to Databases

Logging On To Databases Simplified Crystal Reports is designed so that the user ID you entered at design time is saved with the report file, but not the password. If you are printing from a non-secure data source, this doesn’t affect you. If the data source is secure, you are prompted with a dialog box […]

17.03 Database Object Model

The Database Object Model The ReportDocument class, shown in Figure 17-1, manages the connections to your report’s data sources. It gives you runtime access to the methods and properties to dynamically manipulate and optimize the connections. Figure 17-1. The Database class diagram. This diagram shows the ReportDocument class and the two primary properties that work […]

17.04 Changing the Data Source

Programmatically Changing the Data Source Some programs require changing the data source of a table while the application is running. This can include changing the server name or database name. Although this isn’t common, it does happen. An example is an application that wants to use the same report for multiple data sources. For example, […]

17.05 Logging On with Viewer Control

Changing the Data Source with the Viewer Control Changing the data sourcusing the viewer control uses the same classes as the ReportDocument class, but it does so in a slightly different manner. Rather than having a collection object that stores a reference to every table in the report, the viewer stores a reference to each […]

17.06 Parameters and Stored Procedures

Parameters and Stored Procedures A common hurdle that developers have is figuring out how to print reports connected to stored procedures using parameters. It really isn’t hard once you understand how it works. Reports that use stored procedures as their data source are no different than reports that use any other data source. When you […]

17.07 Set NoCount On

Set NoCount On Although this chapter assumes you are already familiar with stored procedures, there is one statement that doesn’t get much attention. I think it is very important to be aware of it. SET NOCOUNT ON The Set NoCount On statement prevents extraneous messages from being output by SQL Server. By default, SQL Server […]

17.08 Subreport Data Sources

Working with Subreports As mentioned earlier in the chapter, when you use the SetDatabaseLogin() method to pass security credentials to a report, it logs into the main report’s database as well as the database use by the subreports. Thus, you don’t have to do anything extra to open a report that also uses subreports. If […]

17.09 The Push Model

Implementing the Push Model Reports can use either the Pull Model or the Push Model for retrieving data. The Pull Model links to a data source and retrieves the data automatically. The Push Model, discussed in the remainder of this chapter, is based upon generating reports from a manually populated data soThe data source can […]

17.10 DataSets, DataTables, DataView and XML

Reporting From the DataSet, DataTable, DataView and XML For purposes of simplifying this discussion, when referring to the any of the above data types, I’ll refer to them as only a dataset object. When there are any differences, I’ll make a note of it. There are three steps for reporting from a dataset. The first […]

17.11 DataSet Schema File

Define the Dataset Schema File When printing from any data source, Crystal Reports needs to know the tables and fields that it is printing from. When using a standard database, it can query the database to get this information. But when reporting from a dataset it doesn’t have this luxury. Instead, it uses a schema […]

17.12 Manual DataSets with the IDE

Creating a DataSet Manually with the IDE The previous method of building a dataset assumed that you have a table that can be dragged and dropped onto the designer. There are times you want to create a new table without having an existing data source. This could be a report based on user input or […]

17.13 ADO.NET Schema Files

Creating a Dataset File with ADO.NET Methods The last way to create a dataset file is to use the methods of the ADO.NET classes. The ADO.NET namespace is designed to make working with XML files almost effortless. It converts a dataset to and from its XML schema. Use the WriteXMLSchema() method to save the schema […]

17.14 Binding to the Schema File

Build the Report Using the Dataset Schema File For a report to use a dataset as its data source, it needs to know how the data is structured. It uses the schema file to determine the structure.The previous section showed three different ways to create the dataset schema file. The way that you created the […]

17.15 Binding the DataSet

Bind the DataSet to the report The last step in using the Push Model is writing the code to link the fully populated dataset to the report. The ReportDocument class has a SetDataSource() method that takes a populated dataset object and uses it as the data source for the report. After calling this method the […]

17.16 Object Collections and LINQ

Reporting from an Object Collection One of the data types that the SetDataSource() method accepts is System.Collections.IEnumberable. This lets you print data that is stored within an object collection. For example, it’s easy to report from an ArrayList because it implements the IEnumerable interface. You can even create a custom collection class as a data […]

17.17 DataReaders

Reporting from the DataReader Many people process data using the DataReader class and they want to run reports from it as well. Crystal Reports gives you the ability to do this. The DataReader class is another one of the many data types that can be passed to the SetDataSource() method. Using the DataReader as a […]

17.18 DLLs and DataSets

Reporting from DataSets in a DLL Crystal Reports also gives you the ability to report from DataSets that are within a separate DLL. In a multi-tier architecture, this lets you build reports directly from the data layer without having to write extra code to encapsulate the dataset in a local object variable. The steps for […]

17.19 Caching Data

Caching Data If you are building an ASP.NET website, it is important to optimize the web server’s memory. Reports can be quite large, and when you have many users on the site then they can consume a lot of the server’s resources. You can optimize reports by caching them. This saves them to memory so […]

17.20 Subreports and DataSets

Working with Subreports If a subreport uses the Push Model, you have to pass a populated dataset to the subreport. Just like the main report, this is accomplished using the SetDataSource() method and passing it the dataset object (or any of the applicable data types). The code for working with subreports is in Listing 17-5.

17.21 Dynamic Images

Printing Dynamic Images I’ve received many email requests from people wanting to know how to print dynamic images on a report. If you’ve used previous versions of Crystal Reports with VB6, you know that there is a method that lets you load a new image onto the report for every record printed. Many people want […]

18.01 Web Services

Creating reports as Report Web Services (RWS) enables you to deliver reports using SOAP via an HTTP protocol. There are no issues with worrying about the report being blocked by security or network constraints. There are two parts to viewing reports as a web service. The first part is to build the web service application […]

18.02 Consuming the Report Service

Consuming the Report Service The second part is creating an application to consume the report’s web service. To create an application that consumes the web service, open a new instance of the Visual Studio IDE and create a new Windows application (or an ASP.NET application). Add a CrystalReportViewer control to your form and bind it […]

18.03 Client Side Modifications

Making Client Side Runtime Modifications Report web services can have a minor number of modifications made to them during runtime. As you learned in Chapter 14, the most flexible way to make runtime report modifications is by using the ReportDocument class. It is far more robust than the viewer control which only has a few […]

18.04 Server Side Modifications

Making Server Side Report Modifications The last section showed how the client is limited to using the viewer control for making runtime modifications. This doesn’t give you many options for making runtime modifications. But that doesn’t mean that it is impossible to make changes using the ReportDocument class. You just have to do it on […]

19.01 Exporting Reports

Crystal Reports gives you two ways of delivering reports. The first way is exporting them to different formats and file types. For example, you could export reports as PDF or MS Word files. You could also export them as email attachments. The second way of delivering reports is by deploying them with your application. This […]

19.02 Export with the Viewer

Exporting with the Viewer The easiest way to export a report is to have the users do it themselves by using the report viewer. The viewer has a built-in export button that lets the user export the current report to the file format of their choice. Figure 19-1. The Export button on the Windows Report […]

19.03 Using Programming Code

Exporting with Programming Code Using the built-in export feature of the Crystal Reports viewer is nice, but as in most things related to programming, what you get in ease of use you give up in functionality. To get the most functionality for exporting a report, you have to write the programming code yourself. It is […]

19.04 Quick Exporting to Disk

Easy Exporting To Disk There is one method that makes exporting easy: ExportToDisk(). Without a doubt, the ExportToDisk() method is the most popular way of exporting a report because you are only required to specify the format type and the filename. The following code shows an example or saving a report as a PDF file. […]

19.05 Advanced Techniques

Advanced Exporting When you want to do more than a simple export to disk, use the Export() method. It has many classes and properties for customizing the export process. Consequently, having additional classes and properties makes this method more complicated to learn and use. This chapter gives you many examples that make it easy to […]

19.06 Exporting to PDF, RTF, and Word

Exporting to PDF, RTF, and Word Documents The process for exporting to PDF, RTF, and MS Word shares the same classes for all three. In fact, the code is identical except for the property that sets the export format type, which specifies which of the three types to use. Exporting a report to PDF, RTF […]

19.07 Exporting to Excel

Exporting to Excel Exporting a report to an Excel spreadsheet gives you options to work with the report areas and how the columns are formatted. The columns can be formatted so that they have headers and that they are all the same width. Table 19-5 shows the properties used for setting the Excel formatting. Table […]

19.08 Exporting to HTML

Exporting to HTML HTML output is inherently different from exporting to the other file formats. HTML files are meant to be viewed in a web browser and this can impose certain requirements on how you present the data to the user. You have the option of displaying the entire report in a single browser window […]

19.09 Exporting to Email

Exporting to Email Exporting a report to an email creates the report as a separate file and attaches it to an email message. This email message is automatically sent out to the recipient. Table 19-7 shows the properties for exporting to email. Each of these properties relates to the typical settings you find when sending […]

19.10 Exporting to Exchange Folders

Exporting to Exchange Folders Crystal Reports lets you export a report to an Exchange folder. Table 19-8 shows the properties that need to be set for this to work. Table 19-8. ExchangeFolderDestinationOptions Properties. Property Description DestinationType The export destination type FolderPath The path of the Exchange folder Password The Exchange password Profile The user profile […]

19.11 Printing via the Browser

Printing with ASP.NET The Crystal Reports web viewer control gives you a print button to send your reports directly to the printer. But, there are times when you want to view them in a different format, or save them to a separate file. For example, many people like to view reports as a PDF file […]

19.12 Exporting to a New Browser Window

Exporting to a New Browser Window I frequently see people on the forum asking how to export reports to a new browser window. This keeps the report output separate from your web application’s browser. The reason that this is important is because users have a tendency to close the browser window when finished reading a […]

19.13 Deploying Reports

Deploying Reports After working hard to build your reporting solution and test it on your computer, you will certainly want to deploy it to the end users computer. Although Microsoft advertises that .NET installations are no more difficult than an “XCOPY” deployment, this is only true if you use 100% .NET managed components. If your […]

19.14 Deploying an ASP.NET Application

Deploying an ASP.NET Application Deploying a web application is very similar to deploying a Windows application. As far as Crystal Reports is concerned, the primary difference is that the project is going to be installed on a web server. When deploying to a web server for the first time, make sure that the ASPNET user […]

19.15 ClickOnce Deployment

ClickOnce Deployment ClickOnce deployment lets users install your application via a link on your website. You get the benefit of not having to install your application on multiple computers across your company. You copy the ClickOnce deployment package to a designated web page on the company web server and link to that page. Updating your […]

19.16 Mobile Devices

Printing for Mobile Devices Mobile devices are becoming prevalent in our corporate lives and personal lives. With the faster data connections available and better browser interfaces, more and more people are becoming reliant on them as a primary source of information. In the corporate world, executives want timely business data and sales people want instant […]

20.01 Upgrading to Crystal Reports 2008

If you’ve gotten this far in the book, you are very familiar with all the ins and outs of using Crystal Reports Basic. You also know that although Crystal Reports Basic can create powerful reporting solutions, it has features removed from it that users of the standalone Crystal Reports product expect. This includes robust reporting […]

20.02 Programming with the RAS

Programming with the RAS The Report Application Server (RAS) is the recommended tool for developing enterprise caliber reporting solutions. It can communicate with enterprise software to deliver a high performance, highly scalable reporting solution, or it can be integrated into a Windows thick-client application to utilize advance reporting techniques. RAS also gives you the ability […]

20.03 ReportClientDocument Overview

RAS ReportClientDocument Overview Crystal Reports .NET references reports using the ReportDocument class. RAS references reports using the ReportClientDocument class. These two objects are designed to be used in conjunction with each other. A report can be referenced with either object model, or both simultaneously, and all changes are applied correctly. They are not mutually exclusive. […]

20.04 Opening a Report

Opening a Report How you open a report is dependent upon how you installed RAS. We will create code templates that open a report and return a reference to it that you can use in your program. But before we do that, let’s look at the code that will control this process by declaring the […]

20.05 Understanding the RAS Classes

Understanding the RAS Classes Using the RAS SDK requires having an understanding of a variety of report classes and their methods. But the hardest part can be figuring out where to find them within the object model. Looking at the help files can create even more confusion because none of the examples have the class […]

20.06 Adding Database Fields

Adding Database Fields Since the primary purpose of a report is to display information from a data source to the user, it’s no surpise that the most common task that people want to do is add new fields to their report. RAS makes it easy to do this, but it’s not a trivial process. Adding […]

20.07 Selecting a Field

Selecting a Field from a Data Source The report’s data source points to all the tables and fields that can be printed. Even if a field isn’t displayed on the report, it will still be in the data source if the report’s query specifies it to be available. The RAS object model has methods within […]

20.08 Adding the FontClass

Adding a FontClass to a Report Object Every report object that displays data, whether it is dynamic or static information, uses a FontColor object to store the font related information. Instantiating a FontClass object and assigning properties to it is very trivial. The only unusual part is that it breaks out the font properties into […]

20.09 Selecting Areas and Sections

Selecting a Report Area and Section As you learned earlier in the book, a report consists of areas and within each area there can be one or more sections. Each report object has to be located within a specific section. When using the RAS SDK to dynamically add a report object, you have to specify […]

20.10 Add the Database Field to the Report

Add Database Field to the Report The code to add the database field to the report creates a new field object and uses the objects created in the previous code listings to set its properties. To try and keep the method as simple as possible, the FontClass object and Section object must be created prior […]

20.11 Demonstrate Adding a Database Field

Demonstrate Adding a Database Field to the Report Finally, we get to see how everything works by demonstating the code which creates all the necessary objects and calls the AddDatabaseField() method to put the object on the report. This code uses the Form_Load() event so that report is built immediately when the form opens. Of […]

20.12 Adding a Special Field

Adding a Special Field to the Report Crystal Reports has special fields that give you more in-depth information about a report. Some of these include Page Number, Page N of M, Print Time, etc. The complete list is found in the Field Explorer window in the Special Fields node. Adding a special field to a […]

20.13 Adding Formulas and Parameters

Adding Formulas and Parameters After going through the examples for adding database fields and special fields to a report, you should be an expert at how the code works to add objects to a report. Of course, you will most likely want to add formulas and parameters to your report. So let’s just look at […]

20.14 Changing the Record Selection Filter

Changing the Record Selection Filter The record selection filter tells Crystal Reports which records you want to include in the report’s output. Any records not matching the the record selection filter are excluded. The RAS lets you change the filter with a single statement: RecordFilterController.SetFormulaText(). To change the grouping formula, use the GroupFilterController.SetFormulaText() method. The […]

20.15 Modifying Report Parameters

Modifying Report Parameters Parameters in RAS use a totally different architecture than parameters in .NET. The RAS uses a ParameterFieldController to manage a report’s parameters. The ParameterFieldController is found in the DataDefController namespace. Modifying a parameter requires you getting a copy of the existing parameter object. Make changes to this copy and then tell the […]

20.16 Modifying Report Parameters Code

Listing 20-13 is a generic method for modifying a discrete parameter in a report. Pass it the parameter name, the parameter value, and the report object that you are working with. If you remember from the description of Listing 20-1 which shows how to load and preview a report, prior to setting the ReportSource property […]

20.17 Modifying Formulas

Modifying Formulas The concepts that apply to modifying parameters are the same concepts for modifying formulas. RAS uses a similar object model for making changes to a formula. Rather than use the ParameterFieldController class, you use the FormulaFieldController. Listing 20-15 is the complete code listing. Listing 20-15. Modifying the value of a formula. [VB.NET] Public […]

20.18 Saving Report Changes

Saving Your Report Changes Crystal Reports lets you save the changes you made to a report to a new file. Call the ReportDocument.Save() method and pass it a fully qualified filename.

0. 1 Preface

With the release of Visual Studio .NET, Microsoft gave programmers the first powerful report writing tool that is completely integrated into the development environment. Crystal Reports has been included with previous versions of Visual Studio in the past, but never with this degree of integration. With this book, programmers can now build a powerful reporting […]

1.01 Creating Your First Report

Visual Studio .NET is the first Windows development environment that gives developers a fully integrated and robust reporting solution. Crystal Reports is now installed with Visual Studio so developers can write applications that have reports seamlessly integrated into them. (Perhaps make this next sentence a footnote?)Starting with Visual Basic 3.0, Crystal Reports was included with […]

1.02 Previewing with a Windows Form

Previewing with a Windows Form After creating the Employee List report in the previous section, you can preview it using either a Windows Form or and ASP.NET application. This section shows you how to preview it from a Windows Form. ASP.NET is covered in the next section. Previewing the report requires modifying the form. When […]

1.03 Previewing with a Windows Form cont.

Add a ReportDocument component to the web page by double-clicking on the ReportDocument component. The ReportDocument component is listed in the Components section of the Toolbox. As mention in the previous section, when you add a ReportDocument component to your form, it automatically displays the Choose a ReportDocument dialog box. This lets you select which […]

1.04 Examining the Report Designer

Examining the Report Designer Each report within your application is just like any other component that your application uses. It is listed in the Solution Explorer window as a class in your project. When you double-click it, it opens the report in design mode and you can make changes to it. Each report starts with […]

1.05 The CrystalReportViewer Control

The CrystalReportViewer Control The CrystalReportViewer control is used on a form to display a report. You have to use this control when you want to preview and print a report. It is found in the form’s Toolbox as the last control listed. To access it, you have to use the down arrow to scroll down […]

1.06 Two-Pass Report Processing

Two-Pass Report Processing Crystal Reports processes reports in two stages. This is called the Two-Pass Report Processing Model. The first pass creates the primary data to be printed. During the second pass this data is further processed to finalize it for printing. The first pass is used to read individual records one at a time […]

2.01 Creating a Report

Since Crystal Reports is fully integrated with Visual Studio .NET, you may be tempted to think that creating a new report is a simple matter that only takes a minute or two. While this is true after you have a little experience, creating a report the first time can be confusing if you don’t follow […]

2.02 Creating a New Report Class

Creating a New Report Class To create and print a report, you need to create a new report class for your project. Once the class is created, you can modify it using the report designer and then call it from your application. Creating a new report class is done two different ways. The first is […]

2.03 Using the Report Experts

Using the Report Experts Depending upon your needs, creating reports can be a simple or complicated process. For example, it is very easy to print mailing labels from an address database or a form letter to a list of subscribers. On the other hand, it can be very complicated to write a report that uses […]

2.04 Data Tab, Links Tab, Fields Tab

Using the Tabbed Dialog Boxes Each report expert uses a combination of different tabbed dialog boxes to question you about how to build your report. Each expert presents a slightly different combination of these tabs. This section describes how to use each tab and explains any aspects of it that may not be obvious. The […]

2.05 Group Tab, Total Tab, Top N Tab, Chart Tab

The Group Tab Some reports have so much data on them that they can be a little hard to understand. When reports are dozens or even hundreds of pages long, you need to organize the information in a way that makes it easier to absorb the data in smaller pieces. You do this by creating […]

2.06 Select Tab, Style Tab, Form Letter Tab, Form Tab

The Select Tab Some reports need to only show a sub-set of all the data that is in a recordset. For example, a financial report may only show the corporate data for a single quarter or a range of quarters. To filter out certain data so that you limit how much information is shown on […]

2.07 Cross-Tab Tab, Cusomize Style Tab, Drill Tab

The Cross-Tab and Customize Style Tabs These tabs are only available when using the Cross-Tab Expert. They let you modify the cross-tab data for what appears in the rows and columns. Modifying these tabs is very involved and is explained in detail in Chapter 11. The Drill Tab The Drill tab is only available when […]

2.08 Setting the Designer’s Defaults

Setting the Designer’s Defaults The report designer is where you will spend all your time creating reports and modifying them. There are many different default settings that you can set that control how you interact with the report designer as well as controlling how you access and display data on a report. Knowing what the […]

2.09 Using the Report Options

Using the Report Objects A report is very similar to a Windows form. Just like a form, the report is listed as a separate object under your project in the Solution Explorer window, there are various controls that can be added, and it is a class that has to be instantiated before using it. The […]

2.10 Formatting Strings

Formatting Strings String output can be formatted in a way similar to formatting a cell in a spreadsheet. You can modify its font and border. There are also formatting options that are specific to Crystal Reports. These options consist of suppressing the field, letting the width grow, rotating the text, paragraph specific formatting and hyperlinks. […]

2.11 Text Object, Field Object, Line Object, Box Object

The Text Object The text object is used to display text, database fields, and special report fields. Each text object can display one of these or a combination of all three. After adding a text object to the report, click on it to edit it. When writing form letters, using the textbox object with a […]

2.12 Picture Object, Chart Object, Hyperlink Object

The Picture Object The picture object is used for displaying the following image file formats: BMP, JPG, TIFF, and PNG. It doesn’t display GIF files. The formatting options are similar to the other controls in that it has the tabs Common, Border, and Hyperlink with similar functionality. There is a Picture tab, shown in Figure […]

2.13 Special Field Object

The Special Field Object The Special Field object is used for printing all report-related information. For example, it can print the current page number or the total number of pages. Since there isn’t a way for you to calculate this information yourself, this field was created as a catch-all for all the miscellaneous types of […]

3.01 Integrating Reports

After creating a report and formatting it to your satisfaction, the report needs to be integrated into the application. There are many ways to do this which makes chapter 3 one of the more complicated to understand, and yet one of the most important in the book. Understanding the best way to integrate reports into […]

3.02 Code for Adding a Report

To start coding, declare and instantiate an object variable from the ReportDocument class. The ReportDocument class has all the properties and methods necessary for loading and printing a report. Although I didn’t mention it before, if you use the viewer control to preview a report, the ReportDocument class is still used to load and print […]

3.03 PrintToPrinter() Method

The PrintToPrinter() method needs to be explained in more detail because the parameters passed to this method aren’t intuitive. The parameters of the PrintToPrinter() method are listed in Table 3-1. Table 3-1. PrintToPrinter() parameters. Parameter Description nCopies The number of copies to print. Collated Set to True to collate the pages. startPageN The first page […]

3.04 Strongly-Typed and Untyped Reports

Strongly-Typed When you create a report, .NET automatically creates a report class within the current project. Just like all the other classes in a project, the report is instantiated by its class name. Reports that are referenced by their class name are called Strongly-Typed reports. These reports are compiled into the applications executable file. Untyped […]

3.05 Previewing Reports with the CrystalReportViewer

Windows Development Each of the integration options presented in the previous section have a specific way that they must be implemented to work properly. This section discusses the details of each option and walks you through the steps to implement them in a Windows application. Previewing Reports with the CrystalReportViewer Most applications give the user […]

3.06 CrystalReportViewer Properties and Methods

Table 3-3. CrystalReportViewer Properties Property Description DisplayBackgroundEdge A background edge creates a border around the edge of the report page in preview mode. Setting this to False makes the edge of the page flush against the viewer’s window. DisplayGroupTree Toggles the Group Tree window on and off. DisplayToolbar Toggles the toolbar on and off. EnableDrillDown […]

3.07 Windows – Binding Reports to the Viewer

Binding Reports to the Viewer The viewer needs to be told which report to display. This is called binding the report to the viewer. The property used for binding is the ReportSource property. There are three ways to set the ReportSource property: using the ReportDocument component, passing it an Untyped report filename, and passing it […]

3.08 Printing Directly to the Printer

Printing Reports Directly to the Printer If you decide not to user the viewer control for previewing reports, you will have to write all the code for loading and printing reports. The following sections show you examples of how to instantiate report objects and send their output to the printer. At this point in the […]

3.09 ASP.NET Development

ASP.NET Development The CrystalReportViewer control is the interface for previewing reports in an ASP.NET application. Adding a viewer control to a web page is as simple as dragging the control from the Toolbox onto the web page. After it is added to an ASP.NET page, it is represented by a simple rectangle. Figure 3-2. The […]

3.10 Windows Viewer vs. Web Viewer

Table 3-5. Comparing the property names of the two viewer controls. Windows Viewer Web Viewer DisplayGroupTree DisplayGroupTree N/A DisplayPage DisplayToolbar DisplayToolbar Dock BestFitPage EnableDrillDown EnableDrillDown N/A DrilldownTarget N/A HasDrillUpButton ReportSource (DataBindings) | ReportSource SelectionFormula SelectionFormula ShowCloseButton N/A ShowExportButton N/A ShowGotoPageButton HasGotoPageButton ShowGroupTreeButton N/A ShowPageNavigationButtons HasPageNavigationButtons ShowPrintButton N/A ShowRefreshButton HasRefreshButton ShowTextSearchButton HasSearchButton ShowZoomButton HasZoomFactorList N/A HyperlinkTarget […]

3.11 Binding Reports to the Web Viewer

Binding Reports to the Web Viewer After adding a viewer to the web page and setting its properties, the report needs to be bound to the viewer. This can be done with any of the three options listed earlier in the chapter: using the ReportDocument component, using Untyped reports, and using Strongly-Typed reports. Using the […]

3.12 Using Untyped Reports with the Viewer

Using Untyped Reports with the Viewer Untyped reports are referenced by their filename and they are external to the application’s executable code. Open an Untyped report by passing the filename to the ReportSource property of the viewer. In this example, I set the ReportSource property in the Load() event of the page. I also pass […]

3.13 Using Strongly-Typed Reports with the Viewer

Using Strongly-Typed Reports with the Viewer Strongly-Typed reports are referenced by passing an instance of the report class to ReportSource property of the viewer. In this example I pass a new instance of the Employee_List class to the ReportSource property. For your application you would pass the class name of the report you want to […]

4.01 Sorting Records

In Chapter 2 you saw how to build a report using the different report objects and tie it into a database. For creating simple reports this is all you need to know. But you will quickly find yourself developing reports that require more effort than listing records one by one. For reports that consist of […]

4.02 Grouping Records

Grouping Records When a report has a lot of pages, it is hard to quickly find information as well as get a general idea of what the report is telling you. Sorting the data helps you find a specific record, but it doesn’t give you high-level summary of what the data means. Grouping records lets […]

4.03 Adding and Customizing Groups

Adding and Customizing Groups Adding and customizing groups is very similar to working with the other sections of a report. Add a new group by right-clicking on the report and selecting Insert | Group. This displays the dialog box shown in Figure 4-3. Figure 4-3. The Insert Group dialog box. The Common tab has two […]

4.04 Sorting in a Specified Order

Sorting In a Specified Order The second dropdown box at the top selects the sorting order of how the groups are listed. With one exception, the sorting options are what you would expect and don’t need any explanation. You can choose a sort order that is Ascending, Descending, Original Order (no sorting) or Specified Order. […]

4.05 Customizing the Group Name

Customizing the Group Name In most circumstances, the group name that is displayed on the report is the current value. For example, if you are grouping by country, then the group name is the country name. If you don’t want to display the current value as the group name, you have the option of displaying […]

4.06 Organizing the Group

Organizing the Group The remaining two checkboxes effect how the group data is displayed on the report. The first checkbox controls whether the report should try to keep the entire group on the same page. The second checkbox controls whether the group header should be repeated on each page. Setting the Keep Group Together checkbox […]

4.07 Sorting Group Data

Sorting the Group Data After creating the groups and running a test report, you may notice that although your groups are fine, the individual rows within the group are out of order. If this happens, it is because you still need to add sort fields to your report. Telling the report how to group data […]

4.08 Top N Reports

Displaying Top N Reports An alternative to the standard grouping is to create reports that show the first or last set of records in a certain group. For example, rather than showing all the sales people for the company, you could show the 5 sales people that have the best sales for the month. Or […]

4.09 Hierarchical Reports

Displaying Hierarchical Reports A hierarchical report displays data to show relationships between records in the same table using a tree format. This is similar to having a self-join SQL statement that needs to join a table to itself using a common field. For example, this can be used to show employees that are subordinate to […]

4.10 Drilling Down on Data

Drilling Down on Data Grouping data on a report gives you the added feature of letting you create drill-down reports. A drill-down report lets the user look at the detail records that make up a summary value. It takes a snapshot of the detail records that make up the summary and displays that snapshot on […]

4.11 Summarizing Data

Summarizing Data The purpose of grouping data is to make the report easier to read by creating categories that the detail records fit into. Within these categories you perform various summary functions on fields so that the user can get an overview of the information within the group without having to read all the data. […]

4.12 Summarizing in a Group

To summarize a field within the groups, right-click on the field that you want to summarize. The pop-up menu gives you the option of inserting a subtotal, a grand total or a summary. Each of these menu options gives you a different dialog box. Inserting a subtotal brings up the dialog box in Figure 4-10. […]

5.01 Inputting Parameters

As Crystal Reports has become increasingly advanced over the years, the number of ways to customize reports has also increased. However, passing data to a report hasn’t changed much. Parameters are still the preferred method of passing data to a report. This chapter explores how to create parameters, query the user for input, and use […]

5.02 Adding Parameters

Adding Parameters Parameters are another type of report object. The steps to add and modify them are similar to what you’ve already been doing. Add a parameter by clicking on the Field Explorer tab (on the left side of the IDE by default) and right-clicking on Parameter Fields. Then, select the New menu option. Clicking […]

5.03 Parameter Field Options

The option to allow multiple values is used with discrete values and range values. For discrete values, the user can enter multiple single values and they are each treated individually. For range values, the user can enter multiple sets of ranges and each range is treated separately from the other ranges entered. A parameter can […]

5.04 Setting the Default Values

Setting the Default Values Default values give the user a list of values to choose from, which saves the user from the chore of memorizing all the available values that could be entered. Default values also restrict what can be entered. This prevents a user from making a typographical error. One drawback of using default […]

5.05 Entering Parameters when Running Reports

Entering Parameters when Running Reports When a report has parameters, the user is prompted to enter values when the report is loaded, but before it is printed. After the user enters the values, the report is shown in the viewer or sent to the printer. Figure 5-5 is the Enter Parameter Values dialog box. Figure […]

6.01 Using the Select Expert

You’ve had the opportunity to create basic report formats, including sorting, grouping and adding parameters. This chapter takes that knowledge a little further by showing you how to add more customization to your report. This customization consists of filtering records, using report sections for advanced formatting techniques, and creating running totals. Once you are finished […]

6.02 Is Between and Is One Of operators

Although most comparison operators only have a single dropdown box for entering values, there are a couple of exceptions. The is between and is not between comparisons give you two dropdown boxes. This lets you enter a beginning and ending range. The is between comparison is inclusive. The is not between comparison is exclusive. The […]

6.03 Boolean AND and OR operators

From what you have seen so far, the Select Expert is a very helpful tool for selecting one or more fields using the basic comparison operators. This is probably adequate for many of the reports you write. But what about the other reports where you need to build more complex filtering criteria? For those reports, […]

6.04 Selecting Records for Grouping

Selecting Records for Grouping Setting a filter on summary data isn’t done with the regular selection formula. Selecting records has the limitation that you can only set filters for raw data or basic formulas. You can’t set filters that operate on summary fields or on formulas built with summary fields. You also can’t use any […]

6.05 Report Sections Formatting Them

Sections Sections are used to determine where report objects will appear on a report. Each section has a different purpose and different rules that it follows to determine when and where it should appear on a report. For example, the Report Header section only appears at the top of the first page of a report. […]

6.06 Hiding and Suppressing Sections, Printing at Bottom of a Page

Hiding and Suppressing Sections Hiding sections is used for drilling-down on detail records. As discussed in Chapter 4, you can design your report so that groups only display summary information. This presents the user with a much smaller report. If they are previewing the report with the CrystalReportViewer, then they can look at the detail […]

6.07 Page Breaks

Forcing a Page Break Page breaks are useful when you want groups to appear on their own pages. It is very common to want groups to appear by themselves so that data is listed separately from the other groups. An example is a report that has to be broken apart and distributed to multiple people. […]

6.08 Page Numbers, Keeping Sections Together

Resetting the Page Number Resetting a page number back to Page 1 makes the page appear as if it is the first page in the report. This is good to use in combination with forcing a page break after a section. When you distribute the pages of the report to different people, each person will […]

6.09 Suppressing Blank Sections, Underlaying Sections

Suppressing Blank Sections Printing sections that don’t have any data leaves blank rows in the report. This makes a report look unprofessional because of the gaps that seem to randomly occur. To fix this, set the option Suppress Blank Section. The report skips over any sections that don’t have any data and goes to the […]

6.10 Multiple Columns, Labels

Formatting with Multiple Columns The default layout of a report is designed so that each detail record uses the entire width of the page and each row is printed below the one before it. Sections aren’t designed to only use a partial page width. However, if you want to print mailing labels or a newspaper […]

6.11 Multiple Sections

Adding Multiple Sections Throughout this book, when different parts of a report were discussed, they were referred to by their section name. For example, when adding an object displayed in the header of a group, the portion of the report would be called the Group Header section. However, reports also have Areas. By default, when […]

6.12 Fixing Blank Sections

Example 6-1. Eliminating blank address lines. A common problem with printing addresses is that each one can have a different number of lines. Every address has a line allocated for the street address, but some addresses need a second line for other miscellaneous information. This could be an “Attention:” comment or the suite number. If […]

6.13 Non-Blank Sections, Repeated Fields

Example 6-2. Adding non-blank sections. If you can use sections to suppress blank lines, you can do just the opposite: use sections to show special information. For example, you may want a report to only print a section for unique circumstances. There are a multitude of examples on how to use this feature. An employee […]

6.14 Alternating Background Color

Example 6-5. Alternating the background color. A common reason for alternating the background color of sections is to make the report easier to read. It can be hard to visually move your eyes across a report and stay on the same row. To make this easier to do, reports often alternate the background color of […]

6.15 Running Totals

Running Totals Running totals are built-in fields that accumulate the total of another field. These fields save you the trouble of creating and maintaining a set of formulas that do the same thing. Since they are part of Crystal Reports, you have to do very little effort to use them in a report. A running […]

6.16 Running Totals vs. Summary Fields

Running totals have similar functionality to summary fields, and they can also be duplicated with formula fields. This can cause some confusion as to when you should use a running total, a summary field, or a custom formula field. Each of these options has its unique characteristics. Summary fields are useful for summarizing data outside […]

7.01 Introducing Formulas

Formulas enable you to customize report output by analyzing the data in the report as it is being printed. There are two circumstances when you need to use formulas. The first is performing calculations based on the raw data. For example, when printing the monthly sales for a product you can calculate the percent of […]

7.02 The Formula Editor

Writing Formulas with the Formula Editor The Formula Editor shown in Figure 7-1 is where you create and edit formulas. It consists of four separate windows. The three windows along the top of the dialog box present the entire set of tools to write a formula. Within these three windows is every available field, syntax […]

7.03 Using Formulas for Calculations

Using Formulas for Calculations A report gets the majority of its data from a data source. This could be a table in a database, an XML data feed or a proprietary data source. Data usually consists of raw data that doesn’t have extraneous information that can be derived by other means (e.g. calculations). It is […]

7.04 Dynamic Formatting

Dynamic Formatting with Formulas Reports by their very nature are static. Although the printed data changes and the running totals are different every time, the report format stays the same. For example, if the first field in a column has a font of Arial and is black, every field in that column is also going […]

7.05 Adding Formulas

Adding Formulas When modifying a property in the designer, the changes you make to that property stay the same as the report runs. Most properties, but not all, can have a formula attached to them so that their value can be modified based upon other fields in the report. Clicking on the formula button next […]

7.06 Printing Checkboxes

Printing Checkboxes There are many ways to display a Boolean constant. You can display the words Yes/No or True/False. Right-click on the field and select Format. The dialog box displays a drop-down box that lets you select how you want the data to be displayed (Yes/No, True/False, etc.). You can also display Boolean values as […]

7.07 DefaultAttribute and CurrentFieldValue

Using the Default Attribute and Current Field Value The value assigned to a property in design mode is called the property’s default attribute. When assigning a formula to that property, the default value is overridden by what is in the formula. There are many times when a formula is only used to specify what happens […]

7.08 The Highlighting Expert

Use the Highlighting Expert? The Highlighting Expert is a simplified version of the Format Editor. It gives you a wizard interface for creating rules that modify the font and border of a field. Figure 7-11 shows an example of using the Highlighting Expert for drawing a double box border around a field when its value […]

7.09 Evaluation Time Defaluts

Evaluation Time Defaults Chapter 1 discussed the Two-Pass Processing Model. Knowing how this model works is especially important when writing formulas. The type of formula determines when it is processed. Where you place a formula on a report and the functionality within that formula affects when the formula is evaluated and whether it returns the […]

7.10 Fixing Evaluation Time Problems

If a formula isn’t returning the expected results, there is a good chance it’s because of when the formula is being evaluated. If you are trying to debug a formula, then you have to take many things into consideration: does the formula only use variables; should the formula be put in a different section; is […]

7.11 Example of Fixing Evaluation Time Problems

To illustrate the importance of using the proper keyword in a formula, let’s look at a final example that shows you each stage of writing a formula and how the output is affected by the evaluation time keyword. The example report is a customer report that uses the Xtreme.mdb database. The report has the first […]

8.01 Introducing Basic Syntax

Formulas are used in Crystal Reports as a way to have enhanced control over a report as it prints. For example, if you want to have the rows on the report alternate colors then you can use a formula to change the background color of every other row. Another example would be using a field […]

8.02 Case Sensitivity, Writing Comments, Line Terminators

Formula Fundamentals This section on fundamentals of writing formulas covers Case Sensitivity, Writing Comments, Returning a Value, Using Data Fields, Declaring Variables, Simple Data Types, Array Data Types, and Range Data Types. Each topic has its own section. Case Sensitivity Basic syntax is not case sensitive. The variable FirstName is the same as the variable […]

8.03 Returning a Value, Referencing Fields

Returning a Value Formulas are always used to return a value. The data types of these return values can be Number, Currency, String, Boolean, Date, Time and DateTime. You cannot return data types of range and array. Formulas return values by assigning the value to the Formula variable. Formulas have to always return a value. […]

8.04 Declaring Variables

Declaring Variables Declaring a variable follows the standard format of declaring the variable using the As keyword and then its data type. Dim var As dataype Crystal syntax lists the variable scope and data type before the variable name. Local datatype var The first time a formula is called, all of the variables are automatically […]

8.05 Simple Data Types

Simple Data Types Basic syntax supports the standard simple data types that we expect in a language: Boolean, Number, Currency, String, DateTime, Date, and Time. Notice that rather than have a large number of numeric data types such as integer, double, etc., there is simply a single data type called Number. There is no need […]

8.06 Arrays

Array Data Types Arrays provide a means of storing a collection of data in a single variable and accessing each element of the array using an index. Unlike .NET arrays which are Base 0, an array in Basic syntax is Base 1. Thus, the first element is referenced using 1 as the index. The maximum […]

8.07 Ranges

Range Data Types The Range data type is a very useful data type that doesn’t exist in .NET. It allows you to store multiple values within a single variable. In .NET, if you want to be able to store a range of values that have a definite start and end, then you have to declare […]

8.08 If Then, Select Case

Conditional Structures Conditional structures provide you with a way of testing one or more variables to see if they are equal to a value or are within a range of values. If the test succeeds, then a code block is executed. If the test fails, then a different code block is executed. Since there are […]

8.09 Conditional Functions

Conditional Functions Conditional functions let you evaluate different conditions and return a value based upon the result. These are very similar to the conditional structures If Then and Case because both allow you to evaluate different conditions and perform some action. However, conditional functions are different in they can evaluate all the conditions in one […]

8.10 IIF()

The IIF() Function The IIF() function is unique to Crystal reports. It is a shortcut for the standard If statement. Its purpose is to put both the True and False actions on the same line. It consists of three parameters. The first parameter is the test condition. If the test condition is True, then the […]

8.11 Switch

The Switch() Function The Switch() function is also like a shortcut for the If statement and the Select Case statement. The parameters are grouped in pairs. The first parameter is an expression to test and the second parameter is a result value that is returned if the expression is true. The syntax for the Switch() […]

8.12 For Next Loop

Looping Structures Looping structures let you execute a block of code multiple times. The number of times this code block is executed depends upon the type of loop used and what happens within the code block. The looping structures covered are: For Next, While, and the various Do loops. For Next Loop The For Next […]

8.13 While and Do Loops

While and Do Loops The While and Do loops all follow the standard VB .NET syntax. The While block is terminated with a Wend statement. The Do loops are terminated with a Loop statement. The While keyword is used to continue looping as long as the condition evaluates to True. The Until keyword is used […]

8.14 Conditional Expressions XOR and IMP

Conditional Expressions When performing actions based upon how a condition evaluates, there are numerous ways build the condition. For example, when writing an If statement, you can compare a field to a constant using a variety of relational operators and you can join multiple conditions using Boolean operators. This section shows you all the ways […]

9.01 Introducing Formulas

The Formula Editor in Crystal Reports gives you the ability to write very powerful functions. As you saw in Chapter 8, Crystal Reports lets you write functions to manipulate and analyze variables so that you can report on data specific to your business. In addition to writing your own formulas, Crystal Reports has dozens of […]

9.02 Analyzing Strings

String Functions The ability to modify and concatenate strings is a powerful feature of many programming languages, and Basic syntax doesn’t disappoint. This section breaks out the different categories of string functions and summarizes how they work. The categories are: Analyzing a String, Parsing Strings, and Manipulating Strings. Throughout this section, many functions are listed […]

9.03 Manipulating Strings

Manipulating Strings It is common for a string to be modified before it is displayed on a report. This can consist of simple reformatting or even joining the different elements of an array into a single string. Basic syntax has many functions for manipulating string data. Table 9-4 shows the functions for manipulating strings. Descriptions […]

9.04 Converting Data Types

Converting Data Types Basic syntax is a type safe language that requires all constants and variables in the same formula to be of the same data type. It also requires you to pass constants and variables as parameters using the exact data type that the formula expects. Even though the data types in Basic syntax […]

9.05 Fomatting Values

Formatting Values for Output When formatting output to be displayed in a report, the output is usually a combination of string data variables. As the last section stated, Basic syntax is a type safe language. As a result, you can’t concatenate strings with numbers or dates. To build an output string with a combination of […]

9.06 Math Functions

Math Functions Table 9-8. Math Functions Function Name Description Abs(number) Return the absolute value. Fix(number, decimals) Return a number with a specified number of significant digits. Int(number), numerator \ denominator Return the integer portion of a fractional number. Pi 3.14… Remainder(numerator, denominator), Return the remainder of dividing the numerator by the denominator. numerator Mod denominator […]

9.07 Random Numbers

Generating Random Numbers Generating random numbers using the Rnd() function. When you use random numbers, you normally want to tell the computer to generate a new random number sequence based upon some internal method (usually using the system clock). This still applies with Basic syntax. The Rnd() function has an optional parameter that lets tell […]

9.08 Date and Time Functions

Date and Time Functions Table 9-10. Date and Time Functions Function Name Description CurrentDate, CurrentTime, CurrentDateTime Returns the current date and/or time. DateSerial(year, month, day), DateTime(hour, minute, second) Returns a date or time. DateAdd(interval, number, date) Increases the date by a certain interval. DateDiff(interval, startdate, enddate, firstdayofweek) Find the difference between two dates. DatePart(interval, date, […]

9.08 DateAdd()

DateAdd( ) For adding and subtracting dates and times, the easiest function to use is the DateAdd() function. This is very similar to the functions AddDays(), AddMonths(), etc. found in the .NET DateTime class. Using the DateAdd() function requires passing a string representing the type of interval to modify, the number of units to add […]

9.09 DateDiff()

DateDiff( ) The DateDiff() function returns the difference between two dates or times. It can return the difference in different intervals. These intervals can be days, months, years or any of the intervals listed in Table 9-11. Be careful when using the DateDiff() function for calculating an interval other than the number of days. When […]

9.10 DatePart()

DatePart( ) The DatePart() function returns a number representing the part of the date that you specify using the interval parameter. These intervals are listed in Table 9-11. Pass the interval as the first parameter and the date as the second parameter. ‘Get the current quarter Formula = DatePart(“q”, CurrentDate) ‘Returns a number 1 – […]

9.11 MonthName(), WeekDayName(), WeekDay()

MonthName( ), WeekDayName( ) and WeekDay( ) Just like the DatePart() function, these functions are given a date value and they return part of the date. The difference is that these functions are more specialized than the DatePart() function. The MonthName() function is passed a number representing the month and it returns the name of […]

9.12 DateSerial(), TimeSerial()

DateSerial( ) and TimeSerial( ) The DateSerial() and TimeSerial() functions can be used to create a date or time by passing the parts of the value as separate parameters to the function. The DateSerial() parameters are the year, month and day. The DateTime() parameters are the hour, minute, and seconds. In the simplest form, these […]

9.13 Timer

Timer The Timer function returns the number of seconds that have elapsed since midnight. This can be used for doing performance evaluations. Unfortunately, it is only significant to the nearest second. So it is only useful for analyzing reports that have lengthy run times. The following code demonstrates timing how long it takes a report […]

10.01 Chart Types

Visualizing data can have a tremendous impact compared to just printing raw numerical data. Adding a chart to a report makes it possible for readers to quickly grasp the important relationships between data. Many times reports are used with proposals to sell a reader on an idea or plan. Adding a colorful chart can sell […]

10.02 Adding a Chart

Adding a Chart There are two ways to add a chart to a report: with the Report Expert and with the Add Chart Expert. If you are creating a report and you know in advance that it will use a chart, you can use the Report Expert to build the chart while performing the initial […]

10.03 Selecting a Chart

Selecting a Chart with the Type Tab Use the Type tab to select the type of chart to display. A sample of each chart is shown in Table 10-2. The Chart Expert shows many examples of each chart to the right of the list. As you click on each chart type in the list, the […]

10.04 Setting Data Points

Setting Data Points with the Data tab The Data tab is the primary interface for configuring the chart and it is fairly complex. It sets the location of the chart, and the fields that determine the coordinates of each axis as well as map the data points. It also has three buttons for setting the […]

10.05 Setting the Evaluate Option

Setting the Evaluate Option The Evaluate option determines when a new element is shown on the chart. On a standard vertical chart, this would be the elements listed on the X-axis. For example, this determines when a new bar is drawn on a bar chart. There are three options to choose from and they are […]

10.06 Setting the Show Value Fields

Setting the Show Value Fields The Show Value list determines the fields that are plotted on the chart. On a standard vertical chart, this is where an element is plotted along the Y-axis. For example, this determines how high a bar is drawn on a bar chart. For each field listed, another element is drawn […]

10.07 Minimum Field Requirements

Meeting the Minimum Field Requirements The Evaluate setting and Show Value setting work together to determine the X, Y and Z axis on each chart. Due to the fact that each type of chart can be unique in how it uses the fields in these setting to create the chart, it is important to know […]

10.08 Group Charts

Adding Group Charts Below the Advanced button, on the Data tab of the Chart Expert, is the Group button. It is used to create a chart that shows each group and one of its summary fields. It can only appear within the Report Header/Footer. When you click on the button, the dialog interface changes to […]

10.09 Cross-Tab Charts

Adding Cross-Tab Charts The Cross-Tab chart is similar to the Group chart because it takes one of the fields that the cross-tab object is grouped on and plots one of its summary fields. The related dialog box is shown in Figure 10-6. Figure 10-6. The cross-tab options of the Data tab. The On Change Of […]

10.10 Setting Captions

Setting Captions with the Text tab Use the Text tab to set the captions that appear on the chart as well as their fonts. This tab is shown in Figure 10-7. It is only visible when the Type tab option to automatically set chart options is unchecked. Figure 10-7. The Text tab of the Chart […]

10.11 The Options Tab

Using the Options Tab The Options tab, shown in Figure 10-8, has a few miscellaneous options for customizing the layout of the chart. Like the Text tab, it is only visible when the Type tab option to automatically set chart options is unchecked. Figure 10-8. The Options tab of the Chart Expert. There are four […]

10.12 Format Menus

Using the Format Menus Once a chart is finished and the Chart Expert is closed, two new menu items become available. By right-clicking on the chart object in design mode, you are given two formatting specific menu options: Format and Format Chart. The Format menu gives you the standard formatting dialog box that you’ve already […]

11.01 Introducing Cross-Tabs

Cross-tab reports are a powerful way to create summaries of data in a spreadsheet style format. They generate summary data in a grid where the rows and columns represent groups of data. This provides the user with a report format that is easy to read and uses a small footprint. Ask a programmer how he […]

11.02 Understanding Cross-Tabs

Understanding Cross-Tab Reports Cross-tab reports are a way of reformatting a report that groups data into a grid format. This grid format is very similar to the way a spreadsheet represents data. It lets the user visually analyze the data in a way that makes it easy to compare values in one group against the […]

11.03 Creating a Cross-Tab Object

Creating a Cross-Tab Object The name “Cross-Tab Report” is a little misleading. It makes it sound like the whole report only shows the cross-tab grid and that no other data is printed. This isn’t true. A cross-tab report refers to a report that has a cross-tab object in one of its sections. This object is […]

11.04 Summary Functions

When you add a field to the Summarized Fields window, by default it assigns one of two different summary functions to the field. If the field is numeric, then the Sum() function is used. If the field is text, then the Count() function is used. You can change the default summary function after it has […]

11.05 Sub-Groups

Adding additional grouping fields for either the rows or columns creates a sub-group format. This is very similar to a standard report that uses multiple groups. The first field becomes the outermost group and the remaining fields are grouped based upon the subset of data. If you preview the report and find that the fields […]

11.06 Placing the Cross-Tab Object

Placing the Cross-Tab Object If you look back at the cross-tab report example from earlier in the chapter, you might realize that the cross-tab object as it appears in the report designer doesn’t look like the cross-tab grid as it appears when printed on the report. This is because the report designer shows the cross-tab […]

11.07 Formatting

Formatting the Cross-Tab Grid As with every report object in Crystal Reports, the cross-tab object has many formatting options to make it look just the way you want. These changes can be categorized according to whether they affect the grid and its layout or whether they affect the individual fields within the grid. Since the […]

11.08 Style Properties

Formatting the Style Properties The grid has some unique formatting properties that don’t appear with other objects. These are called the Style properties. Right-click on the cross-tab object and select Cross-Tab Expert. The Cross-Tab tab is shown by default and this has already been discussed earlier. Click on the Style tab, the second tab, to […]

11.09 Managing the Columns

Managing the Columns One of the most interesting features of the cross-tab object is that it is dynamic. The number of columns changes according to the data being displayed. As an example, assume that you have a cross-tab report where the columns represent the historical sales figures per year. If a company has been in […]

12.01 Linking Options

Subreports are used to create multiple views of data on a single report. A limitation of the standard report is that only a single view of the data can be displayed. Rather than create one or more reports to present additional views of the data, subreports are used to present data that is independent of […]

12.02 Linking with a Data Field

Linking with a Data Field Linking subreports with a data field lets you filter the data in the subreport based upon the data that is in the main report. This is useful when you are printing data derived from tables that have a parent-child relationship. First let’s look at when a subreport isn’t the best […]

12.03 Linking with a Formula Field

Linking with a Formula Field Filtering records in a subreport is also done using a formula field. There are two benefits to using formula fields: they give you more flexibility because formulas can be customized to parse or join multiple data fields, and they can link non-indexed fields from PC databases. Being able to parse […]

12.04 Unlinked Subreports

Using Unlinked Subreports An unlinked subreport is used when you want to combine two or more reports onto one report and these reports don’t have any common data to create a relationship between them. The unlinked subreport is completely independent of the main report and the main report’s data doesn’t affect the subreport. Figure 12-2 […]

12.05 Formula Fields Without Linking and Shared Variables

Using Formula Fields without Linking Formula fields can also be used by a subreport without linking them. This is used when you want to pass data to a subreport without filtering the data. For example, the main report can pass a string to the subreport so that the string gets printed in the subreport’s header. […]

12.06 Adding a Subreport

Adding a Subreport A subreport is added to the main report in the same way that the other report objects are added: using the Insert menu item. Right-click on the main report and select Insert | Subreport. This gives you the outline of a subreport object attached to the mouse cursor and you move the […]

12.07 Linked with a Data Field

Linked with a Data Field Linking subreports with a data field lets you use a data field in the main report to filter the data in the subreport. The two fields must be of the same data type to link them together. If this is a PC database, then both fields must have an index […]

12.08 Linked with a Formula Field

Linked with a Formula Field Linking a subreport with a formula lets you have more creativity for how it is linked to the main report. You can add a formula to the main report to convert the data type of a data field so that it is compatible with the field in the subreport. You […]

12.09 Unlinked Subreports with a Formula

Unlinked Subreports An unlinked subreport doesn’t have a connection to the main report. It is totally independent. To create an unlinked subreport, do not set any properties on this dialog box. If you had already added fields to the listbox on the right, then remove them and this will remove any links between the main […]

12.10 Unlinked with Shared Variables

Unlinked with Shared Variables Shared variables can be used to pass data between the main report and the subreport. The difference between using a parameter field and a shared variable is that shared variables can be used to pass data in both directions. When using parameter fields, data can only be passed from the main […]

12.11 Editing the Subreport

Editing the Subreport Once the subreport object has been added to the main report, you will probably need to edit it. Depending upon the types of changes you want to make to the subreport, there are different ways of editing it. To edit the content of the subreport, from the main report either double-click on […]

12.12 Editing Example

To illustrate how to do this in your own reports, the customer sales report that has been used throughout this chapter is modified so that it won’t show the subreport if there are no credits for a customer. As shown in Figure 12-7, customer Spokes ‘N Wheels has no credits listed, but the subreport still […]

12.13 On-Demand Subreports

Using On-Demand Subreports By default, subreports are run at the same time as the main report. When you view or print the main report, the subreport information is printed as well. This may result in a performance decrease because the subreport could require just as much time, if not more, to process as the main […]

13.01 Introducing Data Connectivity

The backbone of every report is the data it prints. Large corporations merge data on servers into reports that consolidate and chart information from a dozen or more tables. Small businesses optimize their report distribution and expand their client base by providing their data in an XML format and letting companies from around the world […]

13.02 Pull Model

Implementing the Pull Model The Report Expert uses the simplest form of the Pull Model to create reports. Within the Report Expert is the Database Expert, where the data source(s) that the report connects to and the tables that have the data are defined. The Report Expert, generates a default report layout and builds, behind […]

13.03 Linking Tables

Linking Tables Whenever there are two or more tables, they need to be linked so that Crystal Reports knows how they are related. For example, a pet store that wants to print a list of its products by Animal Id needs to build a report using an animal table and a product table. To match […]

13.04 Modifying Links

The default links are not set in stone. You are free do delete or add more, according to your needs. To delete a link, simply click on it (to select it); then click on the button labeled Delete Link. You can also just press the Delete key after selecting it. To add a new link, […]

13.05 Multiple Data Sources

Using Multiple Data Sources Complex reports often require printing data of different origins. This can happen when you have tables in a SQL Server database and they need to be linked to tables in an Oracle database. There can also be SQL Server databases on different servers on the network. Although Crystal Reports is capable […]

13.06 Secured Databases

Secured Databases Most databases have security implemented in them. They require valid user credentials before accessing the data. This means that you have to pass a User Id and Password to the database prior to printing the report. Crystal Reports handles security differently, depending upon how the report is bound and the database used. In […]

13.07 Stored Procedures

Connecting with Stored Procedures As mentioned earlier in the chapter, stored procedures can be used as a data source just like a table. Crystal Reports can open a stored procedure, retrieve the data and print it. The one thing that can make a stored procedure unique is if it has input parameters. For a stored […]

13.08 SQL Statements

Working with SQL Statements Reports connect to databases which in turn return a set of records. The portion of the report’s design that works with data is translated into a SQL statement that is syntactically valid for each specific data source. Crystal Reports gives you many ways to customize the SQL statement that is passed […]

13.09 Filtering Records

Filtering Records When printing records from one or more tables, you probably don’t need to print every single record. It is common to print only a subset of the original records. For example, rather than print every customer in the database, the report print customers that have been added within the past thirty days. SQL […]

13.10 Boolean Operators

When a formula uses multiple conditions, they can be joined with either the AND operator or the OR operator. Each affects performance differently. To understand how this works, you have to understand how each operator is used. When two or more conditions are combined using the AND operator, Crystal looks at each condition independently. If […]

13.11 Grouping and Sorting

Record Grouping and Sorting When Crystal Reports generates a report that uses fields for grouping and sorting, it has to collect all the data within the client’s computer and process each record. It takes a lot of resources to organize and sort each record as well as perform any necessary summary calculations. This section elaborates […]

13.12 SQL Expressions

Using SQL Expressions SQL Expressions are report formulas that only use SQL compatible functions. As mentioned in the last section, many formulas written with Crystal syntax don’t have a SQL compliant equivalent. Thus, the report engine has to take the data returned from the server and process it on the client’s computer. SQL Expressions alleviate […]

13.13 Changing the Data Souree

Changing the Data Source Many reports are very simple and all the tables come from the same data source. The tables that are used when you designed the report are the same tables that will be used when the application is put into production. But this isn’t always the case. It is common for the […]

13.14 Set Location

Set Location Dialog Box The Set Location dialog box, shown in Figure 13-8, is used to change the data source of an existing table. You might need to do this when the reporting requirements change, or when you find that, after having used one data source for coding and testing, you have to change to […]

13.15 Verifying the Data Source

Verifying Changes to the Data Source If you make changes to a table and its fields using the management console of the database, this will impact the reports that use that table. Modifying a field’s data type can affect how the report formats the data. Changing a field’s name causes the report to lose its […]

13.16 Re-Mapping Fields

Re-mapping Fields When replacing one table with another table, it is possible that one or more of the fields on the report won’t have exact matches in the new table. If the fields in a table are renamed or deleted, this can affect the existing fields on your report. When this happens, Crystal Reports gives […]

13.17 Table Aliases

Setting a Table’s Alias When fields are added to a report, they are referenced by a combination of their table name and their field name. During the course of designing a report and making updates to it, the fields can have the table renamed or the table can be replaced with a different table. If […]

13.18 Set Alias Menu Option

To manually change the alias of a table, right-click on the report and select Database | Set Alias. This opens the Database Expert dialog box with the current connections shown in the right-most window. Click on the table that you want to assign an alias to and press the F2 key. This puts the name […]

13.19 MS Excel Files

Printing with MS Excel Files One of the data sources that you can print from is a MS Excel file. Crystal makes it simple to print using the worksheets within a spreadsheet file. It references each worksheet as if it were a table within the data source. However, there are a few quirks that you […]

14.01 Introducing Programming Reports

Advanced report designers aren’t satisfied with using the built-in functions to customize reports. They want to integrate their .NET applications with Crystal Reports during runtime. Part II shows you how to seamlessly integrate your reports into .NET with runtime customization of report objects and modifying parameters. Dynamic data connections let you connect to virtually any […]

14.02 The Report Object Models

Part I of this book taught the details of designing reports by laying out the report objects in the different report sections and connecting to data sources. When the report was finished its design and layout stayed the same every time it was run. The data that it prints will change, but the report format […]

14.03 Basic Customization

Basic Customization No matter what type of runtime customization you want to perform, there are three basic steps that you need to know. Customizing reports always starts with the same premise: declare and instantiate a ReportDocument object modify its properties, and print or preview it. Chapter 3 gave a thorough explanation of the different ways […]

14.04 ASP.NET Template

ASP.NET Template Printing reports within an ASP.NET application requires a slightly different template than the Windows template shown in Listing 14-1. Reports shown on an ASP.NET page are unique in that each time the user moves to a new report page, the web page gets reloaded. If a report is resource intensive, then this can […]

14.05 ReportDocument Object

The ReportDocument Object The ReportDocument class is the base class for all reports. Its properties give an application the ability to thoroughly examine all the report objects. Many of these properties, but not all of them, have write capabilities so that you can modify their values. Each report is a class that inherits from the […]

14.06 Retrieving Summary Information

Retrieving Summary Information Every report has a variety of summary information saved with it. This information is set by the report designer and it usually consists of things such as the report author, the report title and report comments. This information is set during design mode by right-clicking on the report and selecting Report | […]

14.07 Setting the Report Options

Setting the Report Options The ReportOptions class only has a few properties that can be set. They are listed in Table 14-2. This information is set during design mode by right-clicking on the report and selecting Designer | Default Settings and selecting the Reporting tab. These properties were discussed in Chapter 2. Table 14-2. Properties […]

14.08 Exporting Reports

Exporting Reports If your application only sends reports to the printer, it is lacking the functionality to make your data accessible to a variety of applications. Crystal Reports lets you export a report in many different formats so that different applications can read the data. For example, you can export report data to an Excel […]

14.09 ReportDefinition Classes

Referencing and Formatting the Report Objects The ReportDefinition class is responsible for maintaining the collections of the basic report objects. These objects consist of the Areas collection, Sections collection and the ReportObjects class. Each Section class contains the report objects that are within that section. You can modify the formatting properties of any of these […]

14.10 Changing Report Objects

Changing Report Objects Every report can have many types of fields that are used to generate the report, but don’t have to appear directly on the report. Some examples are grouping fields, parameter fields, and formula fields. Even though these fields may not be shown directly on the report, they are updateable during runtime and […]

14.11 CrystalReportViewer Object Model

CrystalReportViewer Object Model Previewing reports is done with the CrystalReportViewer control. The viewer can be used as an alternative to the ReportDocument class for modifying reports during runtime. It is a lightweight control and only exposes a few properties. You can use it when you only need want to perform basic tasks. The viewer exposes […]

14.12 Responding to Events

Responding to Events Report classes are built with events that let you respond to the actions that the user is doing as they preview a report. Your application can subscribe to these events and be alerted when they occur. The events that can be subscribed to are primarily associated with the actions that a user […]

14.13 Handling Exceptions

Handling Exceptions Crystal Reports gives you the ability to handle any errors that might occur while printing and previewing. The benefit to handling the error yourself is that you can customize the error handling process. For example, you could write the error to a log file or you can gracefully exit the process without throwing […]

14.14 C# Code Listings

C# Code Listings The C# code listings are equivalent to the VB.NET code listings. Listing 14-1. A template for modifying reports. private void Form1_Load(object sender, System.EventArgs e) { CrystalReport1 MyReport = new CrystalReport1(); //Call all report modification code here. //For illustration purposes, I’m calling a generice method that changes the report title. //The code for […]

15.01 Modifying the Record Selection

This chapter builds on the basic report customization shown in Chapter 14. It focuses on working with the DataDefinition and ReportDefinition classes. The DataDefinition class controls Record selection, Grouping and Sorting, and Parameters/Formulas.  The ReportDefinition class handles the functionality for report sections, grouping/sorting and running totals. With respect to the number of objects contained […]

15.02 The ReportDefinition Classes

Mapping the ReportDefinition Classes The ReportDefinition class manages everything printed on a report. It has collection classes for storing each area, section, and object shown on the report. You can map out every detail about your report. Leaning how to map these collections teaches you how they are organized. This is necessary for learning how […]

15.03 Code for Mapping the Report Classes

The sample program is a Windows program that uses a listbox, lstMapping, for the output. Each property is printed as a string in the listbox. Indentation is used to offset the object from its properties so that it is easier to read. Notice that all the parameters and variables are declared using the full namespace. […]

15.04 ReportDefinition Objects

Programming ReportDefinition Objects The ReportDefinition classes are used for modifying all the report objects. With every object, you can modify the formatting properties to change its appearance. Some of the objects such as the TextObject and FieldObject, can have their content modified. This isn’t the case for every report object. This section shows you how […]

15.05 Modifying the Report Object Properties

Modifying the Report Object Properties The Area class and Section class have similar properties to each other. You can enable or disable formatting properties such as suppressing the object, keeping the object on the same page, and printing it at the bottom of the page. The difference between the two is that every section has […]

15.06 Grouping and Sorting Data

Grouping and Sorting Data The Crystal Reports object model lets you examine how a report is sorted, grouped and what type of data is summarized. You can change many of these properties prior to running the report. Since the sorting and grouping features of a report are so closely related, it makes sense that their […]

15.07 Mapping the Grouping and Sorting Objects

Mapping the Grouping and Sorting Objects To learn how to use the different sorting and grouping classes, let’s start with some generic procedures that traverse the collections in a report and print out the various properties. The code isn’t very complicated because it primarily consists of For Each loops to traverse the collection objects and […]

15.08 Programming the Grouping and Sorting Objects

Programming the Grouping and Sorting Objects The sorting and grouping objects in a report can be modified during runtime. You can use a form to have the user enter criteria to modify the default properties of a report and your program can change the report to meet these criteria. Modifying the objects during runtime is […]

15.09 Understanding the Summary Field Classes

Understanding the Summary Field Classes The summary field classes are referenced with the SummaryField property of the DataDefinition class. Figure 15-13. The Summary Field classes. The SummaryField property is a collection of SummaryFieldDefinition objects. Each object contains all the information about each summary field: the field, the summary operation, etc. Unfortunately, it is missing one […]

15.10 Modifying Subreports

Modifying Subreports Modifying the properties of a subreport is the same as modifying the properties of any report. Throughout this book there is code for modifying report objects such as the fields, groups and formatting, and there is code for setting login information. Every line of the code samples can be used on a subreport […]

15.11 C# Code Listings

C# Code Listings The C# code listings are equivalent to the VB.NET code listings. Listing 15-1. Changing the selection formula with the ReportDocument object. CrystalReport1 MyReport = new CrystalReport1(); MyReport.DataDefinition.RecordSelectionFormula = “{Orders.Order Date} in Date (1996, 02, 19) to Date (1996, 03, 28)”; crystalReportViewer1.ReportSource = MyReport; Listing 15-2. Changing the selection formula with the viewer […]

16.01 Introducing Parameters

Using parameters are a critical part of report writing. They are the only way for a user to send input to a report prior to it printing. For each parameter, the user is prompted with a separate dialog box to enter either discrete values or a range of values. The problem with parameters is that […]

16.02 Using Formulas

Replacing Parameters with Formulas Before getting into a discussion of how to modify parameters, you first need to know that parameters are very complicated to work with. Changing them involves a lot of steps and it is easy to make a mistake. You also have to make sure you have the parameter name and data […]

16.03 The Parameter Classes

Understanding the Parameter Classes The parameter object model is designed to give you complete control over almost every property of the parameter. The majority of the class properties have write access. This lets you modify parameters during runtime so that you can set the current value and the user isn’t prompted to enter it via […]

16.04 Parameter Class Approaches

The parameter classes are designed with two approaches in mind. The first approach is that you are using the CrystalReportViewer control to preview the report. The second approach is modifying parameters with the ReportDocument object. Both approaches can be used to modify the current value or the default values. But they do so in different […]

16.05 Mapping the Parameter Classes

Mapping the Parameter Classes To get an overall understanding of how the parameter classes are organized, mapping out the parameters is a good way to start. This section shows you the code to print out all the report parameters and their properties. The code to print out the parameters is very simple. The classes are […]

16.06 Programming the Parameter Classes

Programming the Parameter Classes Before you can use the parameter classes to pass values to the parameter objects, you need to understand how values are classified. When a report with parameters loads, the user is shown a dialog box to enter values for each parameter. The dialog box frequently shows a pre-populated list of values […]

16.07 The ReportDocument Class

Programming with the ReportDocument Class The ReportDocument class manages every report object in the report. It has a collection class that is fully populated with every report parameter. Each parameter object can be assigned a value prior to printing the report. To set a parameter’s value, first get a reference to parameter field object. In […]

16.08 The CrystalReportViewer Control

Programming the CrystalReportViewer Control Programming with the viewer has some similarities to programming with the report document, but there differences. From a high level, the viewer is similar to the report object because you have to get a reference to the parameter object and assign a value to its CurrentValues collection. From an implementation standpoint, […]

16.09 Modifying Subreport Parameters

Modifying Subreport Parameters Modifying the parameters of a subreport is the same as modifying any report object within a subreport. The code from Chapter 15 that references and instantiates subreport objects works with parameters as well. Using the code in Chapter 15 as a basis to modify subreport parameters works great. But it can only […]

16.10 Simplifying Parameters

Simplifying Parameters If you found that programming parameters was difficult to understand, you are not alone. There are a lot of steps to learn and it doesn’t help that the ReportDocument object is programmed differently than the viewer control. To try and simplify this, I wrote some generic methods that can be added to your […]

16.11 C# Code Listings

C# Code Listings The C# code listings are equivalent to the VB.NET code listings. Listing 16-1. Mapping the parameters in the ReportDocument class. CrystalReport1 MyReport = new CrystalReport1(); MapParameterFieldDefinitions(MyReport.DataDefinition.ParameterFields, 0); … public void MapParameterFieldDefinitions( CrystalDecisions.CrystalReports.Engine.ParameterFieldDefinitions ParameterFieldDefinitions, int Indent) { foreach(CrystalDecisions.CrystalReports.Engine.ParameterFieldDefinition ParameterFieldDefinition in ParameterFieldDefinitions) { MapParameterFieldDefinition(ParameterFieldDefinition, Indent); } } public void MapParameterFieldDefinition( CrystalDecisions.CrystalReports.Engine.ParameterFieldDefinition ParameterFieldDefinition, int Indent) […]

17.01 Dynamic Data Sources

Connecting to databases during runtime gives a reporting solution great flexibility for using a single report to print with different data sources and dynamic queries. Reports can dynamically print data from different servers, different types of databases, and customized queries. To be honest, this chapter has a lot of meat on its bones. There are […]

17.02 The Push Model

Implementing the Push Model Reports can either use the Pull Model or the Push Model for retrieving data. The Pull Model links to a data source and retrieves the data automatically. This was discussed in Chapter 13. The Push Model is based upon generating reports from a manually populated dataset. It requires you to create […]

17.03 Define the Dataset Schema File

Define the Dataset Schema File When designing a report using the Report Expert you assign it an existing data source and it uses this information to determine which fields are available. When you are using the Push Model you don’t have this luxury. Instead, you have to create a dataset schema file that defines the […]

17.04 Create Dataset with the IDE

Using the IDE to Create a Dataset File The Visual Studio IDE makes it easy to create a dataset file. Right-click on the menu Project | Add New Item. The Add New Item dialog box lists Dataset File as an option. Click on this option, give it an appropriate file name and click OK. The […]

17.05 Create Dataset Manually with the IDE

Creating a DataSet Manually with the IDE The last method of building a dataset assumed that you have a table that can be dragged and dropped onto the designer. There are times you want to build without an existing data source. This could be a report based on user input or internal calculations. No worries, […]

17.06 Create Dataset with ADO.NET

Creating a Dataset File with ADO.NET Methods The last way to create a dataset file is to use the methods of the ADO.NET classes. The ADO.NET namespace is designed to make working with XML files almost effortless. It converts XML schema to and from the internal representation. Use the WriteXMLSchema() method to save the schema […]

17.07 Build the Report from the Data set

Build the Report Using the Dataset File For a report to use the Push Model, it has to be designed using a dataset as the data source. The previous section showed three different ways to create the dataset schema file. The way that you created the dataset determines how it links to the report. If […]

17.08 Populate the Dataset Object

Populate theDataSet Object At this point in the process, you’ve created the proper dataset files and designed the report to use the datasets. If you run the report nothing is printed. This is because the datasets don’t have any data in them. You have to manually populate the datasets prior to opening the report. Populating […]

17.09 MS Access to Dataset

MS Access Connecting to an MS Access database requires using the OLEDB provider in the .NET Framework. The critical parts of the code are specifying the connection string and the SQL statement. The SQL statement can be any valid SQL statement that MS Access accepts. The connection string uses the standard tags, but notice that […]

17.10 SQL Server to Dataset

SQL Server Connect to SQL Server using the SqlClient provider in the .NET Framework. Notice that the SQL statement in Listing 17-4 joins the Customers table and the Orders table by the CustomerId. This is much faster than creating two dataset objects and having the report join them on the client machine. Listing 17-4. Populating […]

17.11 XML to Dataset

XML file Printing from an XML file is very easy. The DataSet object has a ReadXML() method that loads XML data into the dataset object. In this example, the ReadXML() method is passed a fully qualified filename. Listing 17-5. Populating a dataset with an XML file. Private Sub FillDataset(ByVal myDataSet As DataSet, ByVal myFileName As […]

17.12 Manually Populate the Dataset

Manually Populated Crystal Reports doesn’t have to print from an existing data source. It’s easy to report on data that is dynamically created as your application runs. There are a couple different ways where this can be useful. The first is reports based upon user input. For example, an application can have a user enter […]

17.13 DataGrid Control

DataGrid Control Similar to printing a manually populated dataset is printing the contents of a DataGrid control. You can have a datagrid on a form and give the user the option to send the datagrid contents to a report. The user modifies the datagrid and sends its values to a report without having to save […]

17.14 DataView Object

DataView Object The DataView object is useful for taking an existing Dataset object and filtering or sorting its records. You have three options for doing this. The first is to create a new dataset and populate it with the filtered/sorted data. This isn’t a very good idea because the server has to process the request […]

17.15 Binding the Dataset

Bind the Dataset to the report The last step in using the Push Model is writing the code to link the fully populated dataset to the report. The ReportDocument class has a SetDataSource() method that takes a populated DataSet object and uses it as the data source for the report. After calling this method the […]

17.16 The Database Class

The Database Class The ReportDocument class, shown in Figure 17-5, manages the connections to your report’s data sources. It gives you runtime access to the methods and properties to dynamically manipulate and optimize the connections. Figure 17-5. The Database class diagram. This diagram shows the ReportDocument class and the two properties that work with data […]

17.17 Logging on to Secure Data Sources

Logging on to Secure Data Sources Using secure data sources with your report can be an initial source of confusion. There are a lot of types of data sources and just as many ways to connect to them. This is a lot of information comprehend. Crystal Reports is designed so that the user Id you […]

17.18 Connecting with the ReportDocument

Logging On with the ReportDocument The ReportDocument object has a Tables collection that stores each table in a report. The program loops through each table in this collection and sets the logon properties for it. Listing 17-11 shows how to loop through each data source and set these properties. It sets different login credentials based […]

17.19 Connecting with the Viewer

Logging on with the Viewer Control Setting the login credentials using the viewer control uses the same objects as the ReportDocument objects. It does so in a slightly different manner because the viewer control doesn’t have any knowledge of the tables in a report. You can’t loop through the Tables collection because the viewer doesn’t […]

17.20 Parameters and Stored Procedures

Parameters and Stored Procedures A common hurdle that developers have is figuring out how to print reports connected to stored procedures using parameters. But it really isn’t hard once you understand how it works. Reports that use stored procedures as their data source are no different than reports that use any other data source. When […]

17.21 Set NoCount On

Set NoCount On Although this chapter assumes you already familiar with stored procedures, there is one statement that doesn’t get much attention. I think it is very important to be aware of it. SET NOCOUNT ON The Set NoCount On statement prevents extraneous messages from being output by SQL Server. By default, Sql Server outputs […]

17.22 Changing the Data Source

Programmatically Changing the Data Source Some programs require changing the data source of a table while the application is running. Although this isn’t common, it does happen. A common scenario is an application that wants to use the same report for multiple data sources. For example, a sales application could offer the user two reports: […]

17.23 Changing with the ReportDocument Class

Using the ReportDocument Object Listing 17-14 shows how to change the data source of a table as well set the user credentials. This is very similar to Listing 17-10 which only demonstrated how to set the logon credentials. In that listing, different logon credentials were applied depending upon the table name. In this listing, all […]

17.24 Each Table has a Different Data Source

Setting each Table to a Different Data Source If you are changing all tables to the same server and database, then the code in Listing 17-11 works fine. But if the individual tables are being changed to different servers or databases, then the report might now work properly. The way a report is designed determines […]

17.25 Working with Subreports

Working with Subreports When you open a report and set its data connectivity properties, you have to do the same with any subreports that are used. Subreports have the same data connectivity issues that standard reports have. You have do things such as log on to one or more data sources and change the record […]

17.26 Dynamic Images

Printing Dynamic Images I’ve received many email requests from people wanting to know how to print dynamic images on a report. If you’ve used previous versions of Crystal Reports with VB6, you know that there is a method that lets you load a new image onto the report for every record printed. Many people want […]

17.27 C# Code Listings

C# Code Listings The C# code listings are equivalent to the VB.NET code listings. Listing 17-2. Creating an XML schema file from a dataset object. //Write a populated dataset object to an XML schema file //The XmlFileName should have “.ds” file extension public void CreateXMLFile(string XmlFileName, DataSet XmlDataSet) { XmlDataSet.WriteXmlSchema(XmlFileName); } Listing 17-3. Populating a […]

18.01 Introducing Web Services

Creating reports as Report Web Services (RWS) enables you to deliver reports using SOAP via an HTTP protocol. There are no issues with worrying about the report being blocked by security or network constraints. There are two parts to viewing reports as a web service. The first part is to build the web service application […]

18.02 Consuming Web Services

The second part is creating an application to consume the web service. To create an application that consumes the web service, open a new instance of the Visual Studio IDE and create a new Windows application (or an ASP.NET application). Right-click on the project and select Add Web Reference. You are prompted to enter the […]

18.03 Client Side Runtime Modifications

Making Client Side Runtime Modifications Report web services can have a minor number of modifications made to them during runtime. As you learned in Chapter 14, the most flexible way to make runtime report modifications is by using the ReportDocument class. It is far more robust than the viewer control which only has a few […]

18.04 Server Side Report Modifications

Making Server Side Report Modifications The last section showed how the client is limited to using the viewer control for making runtime modifications. This doesn’t give you many options for making runtime modifications. But that doesn’t mean that it is impossible to make changes using the ReportDocument class. You just have to do it on […]

18.05 C# Code Listings

C# Code Listings The C# code listings are equivalent to the VB.NET code listings. Listing 18-1. Consuming a project’s web service private void Form1_Load(object sender, System.EventArgs e) { crystalReportViewer1.ReportSource = new localhost.EmployeeListService(); } Listing 18-2. Preview a report using the web service URL. private void Form1_Load(object sender, System.EventArgs e) { crystalReportViewer1.ReportSource = “http://localhost/VBWebService/EmployeeListService.asmx”; } Listing […]

19.02 Exporting with the Viewer

Exporting with the Viewer The easiest way to export a report is to have the users do it themselves by using the Windows Report Viewer. The viewer has a built-in export button that lets the user export the current report to the file format of their choice. Figure 19-1. The Export button on the Windows […]

19.03 Exporting with Programming Code

Exporting with Programming Code Using the built-in export feature of the Windows viewer is nice, but as in most things related to programming, what you get in ease of use you give up in functionality. To get the most functionality for exporting a report, you have to write the programming code yourself. It is common […]

19.04 Easy Exporting to Disk

Easy Exporting To Disk There is one method that makes exporting easy: ExportToDisk(). Without a doubt, the ExportToDisk() method is the most popular way of exporting a report because you are only required to specify the format type and the filename. The following code shows an example saving a report as a PDF file. You […]

19.05 Advanced Exporting

Advanced Exporting When you want to do more than a simple export to disk, use the Export() method. It has many classes and properties for customizing the export process. Consequently, having additional classes and properties makes this method more complicated to learn and use. This chapter shows plenty of examples so that it is easy […]

19.06 Exporting to Disk

Exporting to a Disk Setting the destination type to disk is simple because there are only a couple properties. You have to instantiate a DiskFileDestinationOptions object and assign the filename to it. As you can see in Table 19-4, this is the only property available. Table 19-4. DiskFileDestinationOptions Properties. Property Description DiskFileName The filename used […]

19.07 Exporting to Email

Exporting to Email Exporting a report to an email creates the report as a separate file and attaches it to an email message. This email message is automatically sent out to the recipient. Table 19-5 shows the properties for exporting to email. Each of these properties relates to the typical settings you find when sending […]

19.08 Exporting to Exchange

Exporting to Exchange Folder Destination Crystal Reports lets you export a report to an Exchange folder. Table 19-6 shows the properties that need to be set for this to work. Table 19-6. ExchangeFolderDestinationOptions Properties. Property Description DestinationType The export destination type FolderPath The path of the Exchange folder Password The Exchange password Profile The user […]

19.09 Exporting to PDF, RTF, Word

Formatting for PDF, RTF, and Word Documents Exporting a report to PDF, RTF or Word gives you the option to specify the page range. You can set the starting and ending page or simply print out the entire report. Note that all pages must be consecutively numbered. For example, you can’t export the first few […]

19.10 Exporting to Excel

Formatting for Excel Exporting a report to an Excel spreadsheet gives you options to work with the report areas and how the columns are formatted. The columns can be formatted so that they have headers and that they are all the same width. Table 19-8 shows the properties used for setting the Excel formatting. Table […]

19.11 HTML

Formatting for HTML HTML output is inherently different from exporting to the other file formats. HTML files are meant to be viewed in a web browser and this can impose certain requirements on how you present the data to the user. You have the option of displaying the entire report in a single browser window […]

19.12 Deploying Reports

Deploying Reports Have you written a .NET application and deployed it successfully? If so, there is a good chance that you found out it wasn’t as easy as you expected. Microsoft advertises that .NET installations are simple “XCOPY” deployments. That sounds good in advertisements, but it doesn’t always work that way. Some of the problems […]

19.13 Create a Deployment Package

Create a Crystal Reports Deployment Package Crystal Reports is not part of the .NET Framework. It is a tool added to the Visual Studio IDE. Consequently, an application that uses Crystal Reports can’t be copied to a computer that has the .NET Framework installed. You have to manually add the Crystal Reports components to the […]

19.14 Deploying to a Windows Application

Deploying a Windows Application Create a new project with the Visual Studio IDE and select Setup and Deployment Projects. Click on the template Setup Project and enter a name for the project. Click the OK button. Figure 19-6. New Windows deployment project. In the Solutions Explorer window, right click on the deployment project and select […]

19.15 Deploying to an ASP.NET Application

Deploying an ASP.NET Application Deploying a web application is very similar to deploying a Windows application. As far as Crystal Reports is concerned, there is no difference in which merge modules you have to add. To show a different way to create a deployment project, this example walks you through the steps of adding a […]

19.16 Printing with ASP.NET

Printing with ASP.NET Printing a report from a browser gives you a less than professional report. The browser can’t differentiate between what is part of the report and the other HTML objects shown on the web page (e.g. the navigation buttons). It prints everything. Unfortunately, the web version of the viewer control doesn’t have a […]

19.17 C# Code Listings

C# Code Listings The C# code listings are equivalent to the VB.NET code listings. Listing 19-1. Export to a PDF disk file Dim myReport As New CrystalReport1() ‘Export the report to the destination type ReportExport.PDF SetDestinationDisk(myReport, “C:\ReportExport.PDF”) ‘Set the format to be PDF and only export pages 1-3 SetFormatPdfRtfWord(myReport, False, 1, 3) ‘Perform the export […]