In the next few pages, we are going to walk through the steps to create an International Sales report in an existing Windows Application using the Standard Expert - you can open the solution and project that is available in the code download for this example (C:\CrystalReports\Chapter02\SalesReport_Basic\chapter2.sln) or create your own.
When creating a report from scratch within Crystal Reports.NET, we have a number of experts that can be used to guide us through the report development process. In this section, we will look at how to use these experts to get started - once you have finished working through the expert, you will have a report you can further develop using the integrated designer. Over time, as the interface becomes familiar, you may choose to start with a blank report and build it up, piece-by-piece, but most developers use the expert to at least get them started.
To start with, create a new Visual Basic .NET project. Select Windows Application from the dialog, call the project SalesReport, and put it in the folder C:\CrystalReports\Chapter02.
Adding a new report to your application is as simple as selecting Project | Add New Item and selecting Crystal Report from the list of available templates. Enter international_sales.rpt into the name field and click on Open to insert the new report into your application. A separate tab will now appear for the report and allow you to use the integrated Report Designer to create your report, but before you can do this you will see the Crystal Report Gallery window.
The first decision you will need to make about your report is to select how it will be created. In this instance, we are actually going to use one of the Report Experts to walk us through the report design, but there are other options as well, as shown in the following table.
Method
Description
Using the Report ExpertThere are a number of different Report Experts available to walk you through step by step to create a report. This is the most popular method even for experienced report developers, as it provides a starting point for further report development.
As a Blank ReportFor experienced developers who are familiar with the Report Designer and want to build a report up piece by piece. This is often a good way to produce a report that is leaner, and has fewer overheads built into it. When this is selected the Experts aren't available.
From an Existing ReportIf you have an existing report that will serve as the basis for the report you wish to create, you can use this method to import your existing report. Again, when this is selected the Experts aren't available.
If you decide to use a Report Expert, there are seven different experts to choose from. All of the experts share some steps in common - for example, in all of them you will be prompted for the data source for your report, as well as what fields will appear.
As we go through this chapter, we will be looking at all of the major steps involved in creating a report using the experts, but for now here is an overview of the different types of experts available:
Standard - The Standard Expert is used most often and is the most generic. You can use the Standard Expert to create a columnar report, which include features such as grouping, sorting, and summaries. This expert also includes the ability to add charts, apply a number of predefined styles, and filter records, and advanced analysis features like TopN, BottomN (for example, Top 10 or Bottom 10 reports).
Form Letter - By combining text objects and database fields, Crystal Reports.NET can be used to create form letters and statements. This expert guides you through creating a report and placement of database fields and text within the report.
Form - To support reports that are designed for a specific paper form, Crystal Reports.NET can use a scanned form or graphic as a guide for your report. Using the Form Expert, you will be able to underlay an image behind your report to correctly place fields on the form. From that point, you can either leave the image in place and print the form and fields on blank paper or delete the image and print the report directly onto your forms.
Cross-Tab - Cross-tabs within Crystal Reports.NET look similar to a spreadsheet, with columns and rows of summarized data. Using the Cross-Tab expert, you can create a report that will feature a cross-tab object in the report header.
Subreport - Subreports are reports that are inserted into a main report. Subreports can be unrelated or parameters can be passed between the main and subreports to determine the content to display.
Mail Label - Crystal Reports.NET supports multi-column reports and this functionality makes mailing labels possible. It includes a number of mailing labels pre-defined for common label stock from Avery and other suppliers (but only in Letter sizes), or you can create your own custom label size using this expert.
Drill-Down - The concept behind drill-down is that you display a summary in your report and users can drill-down into the summary to see the details that make it up.
For this chapter we will look at the Standard Report Expert, so click on OK with Standard selected in the listbox in the Crystal Report Gallery window.
The first step in any of the experts is to select your data source. There are five different types of data sources we can use as the basis of our report.
Which of these data sources you select as the basis of your report will depend on your own particular needs and the data sources available to you.
Data Source
Description
Project DataCrystal Reports.NET can leverage the ADO.NET Framework and report directly from datasets that appear in your application. For more information on how Crystal Reports.NET can be used with ADO.NET data, please see Chapter 6: Working with .NET Data.
OLE DB (ADO)This folder is for data sources that can be accessed through OLE DB, including SQL Server, Oracle, and Microsoft Jet 3.51/4.00 accessible data sources (Access, Excel, Paradox, Dbase, and so on).
ODBC (RDO)This folder is for data sources that can be accessed through an ODBC-compliant driver (which is just about every other data source). In addition to reporting from tables, views, stored procedures, and so on, Crystal Reports.NET will also allow you to enter a SQL command to serve as the basis for your report.
Database FilesThis folder includes a number of file-type database formats, including Access, Excel, XML, and Crystal Field Definition files (TTX), as used with previous versions of Crystal Reports and bound reporting.
More Data SourcesThese include reporting directly from XML files, Access/Excel through DAO, and Crystal Field Definition Files (TTX). Important
When reporting from an ADO.NET data source, the underlying XML file (with the .XSD extension) is used to determine the details of the dataset such as fields, or lengths. Since this XML contains only the definition of the data and not the data itself, you won't be able to browse data from within the Report Designer when using this data source.
There is also a bug in the first release of Visual Studio .NET - when your report uses an ADO.NET DataSet, the string lengths in the underlying XML schema file are ignored and appear to be 65,534 characters in length. This issue is tracked by Crystal Decisions and should be resolved in future service packs.
To select the table we are going to use in our international sales report, double-click the node for ODBC (RDO), which will open the dialog shown below:
Select the Xtreme Sample Database and click Finish. This data source should now appear in the Data tab of the Report Expert.
Click the plus icon next to Tables, locate the Customer table, and double-click on it to insert this table into your report.
If you select multiple tables to appear in your report, a new tab will appear for Links, allowing you to specify how these tables or views are joined together. By default, Crystal Reports.NET will attempt to make these links for you, based on the primary keys, field lengths, and names. You can clear the links that have been provided for you by clicking the Clear Links button on the right-hand side of the Links dialog (or by highlighting each and clicking the delete button), and re-draw them by dragging one field on top of another.
By default, Crystal Reports.NET will join these tables with an Equal join, but you can change the join type by right-clicking on top of the link and selecting Link Options.... If you have added a second table, delete it now, because we will only use a single table in this example; to do this click on the Data tab, select the second table you added from the Tables in Report column, and finally click on the Delete Table button.
Now you have selected the data source for your report, the next step in the Standard Expert is to select the fields that will appear in your report. To do this, click on the Fields tab.
To select a field, highlight the field and then use the arrows to move it from the left-hand list to the right. The fields will be listed using the notation TableName.FieldName and if you are unsure of a field's contents or type, you can use the Browse button to browse the contents of the field. There is also a Find button for finding a particular field in long field lists.
When you look at your report's design a little later, the fields you have selected here will be inserted into the report's detail section and a separate line will be printed for each record in the dataset. You can change the order these fields are inserted using the up and down arrows at the top right-hand corner of the dialog - they are inserted from left to right on your report, from the top of the list of fields that appear here.
For this report, we are inserting:
Customer.Customer Name
Customer.Contact Last Name
Customer.Contact Title
Customer.E-mail
Customer.Phone
With each field, a corresponding field heading will be added to your report and placed in the page header. You can use the textbox marked Column Heading to change the default heading (usually the field name) or you can edit the headings later in the Report Designer.
Having selected the data source for your report and these five fields, you could click the Finish button to work with your report in the Report Designer, but we are going to push on and have a look at grouping and sorting the data.
After you have selected the fields that will appear on your report, you can choose which fields will be used for sorting and grouping. Click on the Group tab and use the same concept as before, moving the field from the left-hand list to the right to select it for grouping.
This dialog can be confusing, as developers will select a group field and then preview their report, only to find that the data is sorted, but no group has been inserted. In order to create groups of records, you must specify a field in this dialog and in the next dialog we discuss (the Total tab), and select some summary to appear for each group.
The fields we are selecting for the grouping in our report are:
Customer.Country
Customer.Region
Customer.City
You can change the order of the fields you have selected using the up and down arrows in the upper right-hand corner of the dialog, and select a sort order from the drop-down list below.
There are four options available for sort order, including:
Sort Order
Description
AscendingFor ordering the data from A-Z, 1-9, etc.
DescendingFor ordering the data from Z-A, 9-1, etc.
OriginalIf your dataset is already sorted, this option will leave the data in its original sort order
SpecifiedUsed for creating your own custom groups and setting some criteria - any records that meet the criteria would belong to the specified group If you select in specified order, a second drop-down list and other items appear in the window.
This is called specified grouping and allows you to create named groups, and specify some criteria for the group. For example, if you were working with a database field that contained a Country field, you could create a new group by clicking on the New Named Group button, giving it the name of Asia Pacific, and then specifying the countries that are considered to be in the Asia Pacific group (Australia, New Zealand, and the Philippines, among others).
When setting up named groups, you are limited to the standard Crystal record-selection operators (equal to, not equal to, is one of, etc.) and can only reference the field you are grouping.
You also have the option of dealing with the other records that fall outside of your grouping criteria by clicking on the Others box in the Standard Report Expert window:
While specified grouping is very useful for re-ordering records, it can be monotonous if you have to use it continually to create the same specified groups in multiple reports. The best practice for specified grouping is to use it only when necessary - if you find you are creating the same groups over and over again, you may want to consider putting this logic into a stored procedure, or creating another lookup table in your database that will determine how your grouping is broken up.
For this example, we are not going to use specified grouping, so with your group set as Customer.Country, Customer.Region, and Customer.City in ascending order, the next step is to insert the summaries you want to see within your report.
Click on the Total tab in the Standard Report Expert window to add summary information to the report. Summary fields are calculated fields within a Crystal Report, that do not require a formula to be written. Encapsulating the most popular requests for calculations, like sums, and averages, summary fields can be used to rapidly develop reports without a lot of repetitive coding.
To insert a summary field, you will need to select a database field (in this case, Customer.Last Year's Sales) and then choose a summary type from the drop-down list shown below. We have chosen sum, and have also checked the two boxes, Percentage of, and Add Grand Totals.
There are over 20 summary operators available for use, but unfortunately the documentation that ships with Crystal Reports.NET does not include a list of them or how they can be used, so a list of the most popular has been included here. Keep in mind that the summary fields available depend on the type of field you have selected, for example, you can't calculate the average of a string field.
Summary Function
Description
SumCalculates a sum of all items
AverageCalculates the standard un-weighted average for all items
MaximumReturns the maximum value
MinimumReturns the minimum value
CountCalculates the total number of all items
Sample VarianceCalculates the statistical sample variance of a specific sample of items
Sample Standard DeviationCalculates the statistical standard deviation of a specific sample of items
Population VarianceCalculates the statistical variance of the entire population of items
Population Standard DeviationCalculates the statistical standard deviation of the entire population of items
Distinct CountCalculates the number of distinct items (for instance, if two different instances of "David" appeared in the list, it would only be counted once)
CorrelationCalculates a measure of the relation between two or more items
CovarianceCalculates a measure of the variance between two or more items
Weighted AverageCalculates an average, weighted by the number of times an item appears
MedianCalculates the statistical median of all items
Nth PercentileCalculates the Nth percentile (where N is pre-defined number)
Nth LargestReturns the Nth largest item (where N is pre-defined number)
Nth SmallestReturns the Nth smallest item (where N is pre-defined number)
ModeCalculates the statistical mode of all items
Nth Most FrequentReturns the Nth most frequent item (where N is a pre-defined number) Also available in the formula language are the corresponding functions to these summary fields, so you can also use them in complex formulas with branching and control structures if required. Keep in mind that their use as a summary field will be limited to only a few options or parameters passed to each.
In addition to simple summaries (which we have just looked at) and formulas (which are coming up in Chapter 7), we also have the ability to add a number of analysis features to our report, to help highlight information that may be important or otherwise missed.
On the TopN tab of the Report Expert, we have three options for adding a bit of analysis to our report:
Analysis Type
Description
TopNOrders your report groups according to a summary field, where you enter a number (N and are presented with the TopN groups, in order from the largest to smallest (for instance, you could create a top 10 report based on last year's sales, to show your best customers). You can also use the options presented to discard the other records or place them in their own group.
BottomNWill order your report groups according to a summary field, where you enter a number (N and are presented with the BottomN groups (for instance, you could create a bottom 10 report, based on last year's sales to show your worst customers). Similar to TopN, you can use the options presented to group or discard the other records.
Sort AllWill order your report groups according to a summary field, either ascending or descending, or by top or bottom percentage depending on the options you set. Keep in mind that all of these analysis options will be applied throughout your report, and will apply to any graphs or charts you might insert in the next step of the Report Expert.
For charting and graphing functionality, Crystal Reports.NET relies on a graphing engine created by 3-D graphics. In the Report Experts (as well as the designer itself) you can add a number of different graph types to your report through the Chart page.
To create a chart for your report, select the Chart tab, select a chart type and then select where the data will come from. The most common type of chart is a Group Chart, which requires that both a group and a summary field be inserted into your report. If you would like more information on the different types of graphs, look at section on Advanced Report Design Techniques later in this chapter.
When reporting from a number of different tables, the chances are you don't want to see all of the data in your report. Crystal Reports.NET follows the tradition set in previous versions of the retail product, and has it's own Record Selection Formula that dictates what records are returned to the report, so click on the Select tab to use it.
The Record Selection Formula is written using Crystal Reports.NET's own proprietary formula language, which in turn is translated to standard SQL and submitted to the database. When there is a feature that can't be translated to SQL, Crystal retrieves all of the records and uses it's own Report Engine to apply the formula and filter the records.
When working with the Select tab in the Report Expert, the options are identical to those you will find in the Select Expert, which is used inside the Report Designer for Record Selection.
There are a number of basic operators available, including:
Is equal to
Is not equal to
Is less than
Is less than or equal to
Is greater than
Is greater than or equal to
Is one of - for building a list of items, such as Is one of Australia, UK, New Zealand, and is similar to the In operator in SQL
Is between
Is not between
Is like - for wildcard searches where an asterisk represents many characters (for instance, * Zealand), and a question mark represents single characters (????Zealand)
Is starting with - for strings that start with a phrase entered
Remember that this record selection is written to a formula and then translated into the SQL statement - we will look at some more advanced record selection a little later in this chapter and again in Chapter 7, which deals with formulas and logic. For our purposes, we are not going to set any record selection on the report we are creating - we want all of the records to be returned from the sample database, so make sure nothing is in the Select Fields, and move on to the next tab, Style.
Finally, the last step of the Report Expert involves selecting a particular style for your report and adding a title. There are ten different pre-defined styles to select from and, no, you cannot add your own style to the list! You can also add a report title, in this case International Sales Report, which will be stored in the report file's Summary Information and a Report Title field will be added to your report's design.
The final step in the Standard Expert is to click the Finish button, which will open your report inside the Report Designer within Visual Studio .NET.
Important
Make sure that you get into the habit of immediately saving your report by clicking the Save or Save All icons within the Visual Studio .NET IDE. While the Report Designer does have an undo feature, it is always nice to have a saved copy in case anything should go wrong. At this point, you probably want to have a look at how your report will appear when it is printed. Running the report will only open a blank form though; unfortunately, the designer does not include an integrated preview so to preview your report, you will need to add the Crystal Reports Viewer to a form, and then specify the Report Source to be the report you have just created.
Open Form1.vb in design mode. Select the form and change the Title in the properties window to International Sales Report, so the user can then see what the report is about if they view the report on a computer. Finally, change the size field so it reads 700,500.
From the Windows Forms section of the Toolbox, drag and drop the CrystalReportViewer onto the form - without this, any reports cannot be seen. If you can't see the Toolbox, click on View | Toolbox to make it appear (the keyboard shortcut is Ctrl+Alt+X).
Position the CrystalReportViewer in the top left corner of the form, and drag it's bottom right-hand corner diagonally down, resizing the viewer to fill the whole form. Now when your report is displayed, it is nice and clear. Next, change the anchor property to Top, Bottom, Left, Right so if the form is resized, the CrystalReportViewer, and more importantly, your report inside it, will be resized to match.
From the Components section of the Toolbox, drag and drop the ReportDocument icon onto the form. This opens up a dialog from which you can choose the report to be opened in the form. Select the report we have just created, displayed as SalesReport.international_sales, and click on OK.
The international_sales.rpt file is now shown in a shaded area at the bottom of the form designer. This shows that the report has been added; and many more reports can be too, using the same method we used here. However, for this example we just want to display the report we have just created, so we will move on.
Finally, we want the report to open in the form when it loads, so double-click on the CrystalReportViewer to open the code designer. The load procedure will be created because of this action, so all you need to do is tell the form where to find the report. Add the shaded line in the following example to your code:
Public Class Form1 Inherits System.Windows.Forms.Form … 'Windows Form Designer Generated Code … Private Sub CrystalReportViewer1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CrystalReportViewer1.Load CrystalReportViewer1.ReportSource = New international_sales() End Sub End Class
That's it! Your basic sales report is complete, and all you have to do is click on the small blue start icon in Visual Studio .NET to see it in action.
The report is indexed by country, region, and city. Select one of these from the tree-view on the left of the window to jump to that information. As you can see from this example, Crystal Reports.NET can present a huge amount of data in a clear and organized manner.
Now you have stepped through the many stages of the Report Expert, your report should match the SalesReport_Basic that is available in the code download. With a basic report using an expert out of the way, it is time to take a look at the Report Designer itself. Make sure you save the Sales Report, as we will use this International Sales Report as the foundation for some later examples.
