لطفا منتظر باشید ...
Customizing Reports Using the Report Engine
To get started, we will need to create a new project within our solution. To create a new project, select File | New | Project and in this instance, create a Windows Application and call the project engine_basic because, in the following sections, we are going to be looking at some basic Report Engine functionality.
We need a report object to work with. To add the sample reports to your project, select Project | Add Existing Item and select the folder where you unzipped the sample project. You will also need to change the file extension from VB Code Files to All files to see the report files we will be using in this chapter, including the first report, CrystalReports\Chapter08\employee_listing.rpt.
With a report to work with, we now need to reference the Report Engine so we can use it in our application. This reference may well add itself when you add the report to the project, but if not, or you are working with one of your custom reports, this will have to be added in Visual Studio .NET. To add a reference, select Project | Add Reference to open the dialog shown below:
Highlight the CrystalDecisions.CrystalReports.Engine namespace and click the Select button and then the OK button to add the reference. The reference to the Crystal Report Engine should now appear in your project in the Solutions Explorer, under the References folder - we are now ready to go.
Getting Started
When working with the Crystal Reports Engine namespace, there is one class you will use most. The ReportDocument class is used to represent the report itself and contains all of the properties, methods, and events that allow you to define, load, export, and print your report.
So before we go any further, we need to start building our sample application form and then use the ReportDocument class to load a report to use during the course of our discussion about the Crystal Report Engine.
Building the Sample Application Form
We need to draw the Crystal Report Viewer on the bottom of our form - as we work through the different ways to use the Report Engine, we''ll use the viewer on the form to view the resulting report.
We''ve also disabled the DisplayGroupTree property in the Properties window for now, to streamline presentation of the methods being used in this chapter. You can leave this enabled or disabled, as you like.
Loading a Report
Before we load the report, we need to have a report added it to our solution, which you did earlier when you added the employee_listing.rpt file.
To add the report to your form, switch to the Design view of the default form that was created with your project (Form1). In the toolbox on the left-hand side of the form designer, there is a tab marked Components and within that tab there should be a ReportDocument component that you can drag onto your form.
When you drag the ReportDocument component onto your form, it should open a second dialog and allow you to select a report document from a drop-down list - the employee listing report should appear here. Once you have selected a report, another section will appear underneath your form, showing the components you are using, and ReportDocument1 should be here.
With that out of the way, we are ready to declare and load our report for use with the Report Engine. Double-click your form to show the code view and locate the code for the Form_Load event - it is here that we are going to declare our report:
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim myReport As New employee_listing()
CrystalReportViewer1.ReportSource = myReport
myReport.Load()
End Sub
That is all there is to it - once you have loaded a report, you have access to all of the properties, methods, and events associated with the ReportDocument object model. For example, if you wanted to extract the title of your report from the SummaryInfo (in the Report Designer, right-click and select Report | Summary Info to set this information) you could simply peek into the ReportDocument object model to grab the SummaryInfo collection (of which the ReportTitle is a member) by inserting this line of code at the end of the Form_Load event above:
MsgBox(myReport.SummaryInfo.ReportTitle.ToString())
There are actually five members of the SummaryInfo class:
Property
Description
KeywordsInReport
For returning or setting keywords
ReportAuthor
For returning or setting the report author
ReportComments
For returning or setting the report comments
ReportSubject
For returning or setting the report subject
ReportTitle
For returning or setting the report title
Note
All of these properties can be viewed or set at run time, but remember, to actually write to the report file with these settings in place, you would need to use the SaveAs method, discussed a little later in this chapter.
Using the Initialization Event
Now in our example above, when we loaded the report, we had no way of knowing whether or not the report had been loaded. Luckily for us, there is an initialization event that is fired whenever a report is loaded.
We can use this initialization event to actually tell us when the report was loaded:
Private Sub report_InitReport(ByVal sender As Object, ByVal myEvent As
System.EventArgs) Handles employee_listing1.InitReport
MsgBox("Report loaded fine")
End Sub
This will save us time later when we try to troubleshoot our applications - if we know the report has been loaded safely, that is one less thing to check when problems occur.
Printing and Exporting
Within the Crystal Reports Engine, there are a number of different ways you can produce report output, even without the Crystal Report Viewer. This functionality provides an easy way for you to print directly from your application or print batches of reports without any user intervention.
We are going to start looking at this type of functionality with a simple print application, building on the sample application we are working with.
Printing Your Report
To print your report from your application, the ReportDocument class provides a simple PrintToPrinter method that can be used to print the report to the default printer. This method requires four parameters:
Number of copies
Collatation flag (Boolean)
Start Page
End Page
So, adding to the form we were working with earlier, we could create a command button that would print your report to your default printer, as shown overleaf:
Call the button Print_Button and set the text as Print Report (Direct). Now, double-click on the button to open the code behind it.
In this case, we are going to print one copy of pages 1 to 999 from our report to your default printer - if you double-click the command button you have added to the form, you can add the method call to the code view, and it would look like this:
Private Sub Print_Button_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Print_Button.Click
Dim myReport As New employee_listing()
myReport.PrintToPrinter(1, True, 1, 999)
End Sub
Note
In this case, we used 999, assuming that that would be a good upper limit for the number of pages in your report.
If you need a little more control over the print process, there is also a PrintOptions class that comes in handy, allowing you to set the printer name to another printer (besides the default) and the number of copies, among other things. This class includes the following properties:
Property
Description
PageContentHeight
Returns the height of the pages content in twips.
PageContentWidth
Returns the width of the pages content in twips.
PageMargins
Returns or sets the page margins collection (including topmargin, bottomMargin, leftMargin, rightMargin).
PaperOrientation
Returns or sets the current printer paper orientation. Options are DefaultPaperOrientation (from the printer), Landscape, and Portrait.
PaperSize
Returns or sets the current paper size. Supports 42 different sizes, including PaperA4, PaperLegal, PaperLetter, etc.
PaperSource
Returns or sets the current paper source. Supports 13 different paper trays, including Auto (for the printer''s automatic selection), Manual, Lower, Middle, Upper, etc.
PrinterDuplex
Returns or sets the current printer duplex option. Supports options for Default, Horizontal, Simplex, Vertical.
PrinterName
Returns or sets the printer name used by the report.
For a complete list of members in the PaperSize class, you can search the Visual Studio.NET Combined Help File for "PaperSize Enumeration" - for PaperSource, search for "PaperSource Enumeration".
Note
A lot of the printer-specific features (PaperSource, PrinterDuplex) will depend on your printer''s capabilities and you may spend some time trying to figure out which members correspond with the different features on your printer. Usually, a printer will include a technical specification that will include this information but if you can''t find it (or work it out) check the manufacturer''s web site.
So to put some of the options of the PrintOptions class together, and print a report duplex to a specific printer on A4 paper with a new set of margins, the code behind our command button would look something like this:
myReport.PrintOptions.PrinterName = "
myReport.PrintOptions.PaperSize =
CrystalDecisions.[Shared].PaperSize.PaperA4
myReport.PrintOptions.PrinterDuplex =
CrystalDecisions.[Shared].PrinterDuplex.Default
Dim myMargins = myReport.PrintOptions.PageMargins
myMargins.topMargin = 10
myMargins.bottomMargin = 10
myMargins.leftMargin = 10
myMargins.rightMargin = 10
myReport.PrintOptions.ApplyPageMargins(myMargins)
myReport.PrintToPrinter(1, True, 1, 999)
If the PrinterName string is empty, the default printer is selected.
Note
If you do change the page margins, you will need to use the PrintOptions class''s ApplyPageMargins method to apply your changes.
If you are migrating your code from Visual Basic 6.0, keep in mind that the Crystal Report Engine in Visual Studio .NET no longer supports the SelectPrinter method that was so handy in previous versions (it would pop up the standard Select Printer dialog for you). To use this functionality within Crystal Reports.NET, you will need to open the Select Printer dialog yourself and get the name of the printer and then set the PrinterName property of the PrintOptions class.
Exporting Your Report
In addition to printing your report without the viewer, you can also export your report without having to use the export button available on the Crystal Report viewer.
Within the ReportDocument methods, there is a method called Export, which can be used to export directly from your application. Unlike the PrintReport method, which would just print the report to the default printer, there are a number of properties that need to be set before you can actually call the Export method.
Here is a rundown of all of the properties and objects that are related to the ExportOption class:
Property
Description
DestinationOptions
Returns or sets the DestinationOptions object, including DiskFileDestinationOptions, ExchangeFolderDestinationOptions, and MicrosoftMailDestinationOptions
ExportDestinationType
Returns or sets the export destination type
ExportFormatType
Returns or sets the export format type
FormatOptions
Returns or sets the FormatOptions object, including ExcelFormatOptions, HTMLFormatOptions, and PdfRtfWordFormatOptions
So in another example, we could add another button to our form to export the report, as shown:
Name the button Export_Button and change the Text property to Export Report.
The code behind the button sets all of the properties and collections of information required. It then uses the ExportReport method to export our report. The first thing we need to do in our code is actually set up some variables to hold the different property collections that we will be setting, including properties for the ExportOptions, DiskFileDestinationOptions, and FormatTypeOptions.
Private Sub Export_Button_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Export_Button.Click
Dim myReport As New employee_listing()
myReport.Load()
Dim myExportOptions As New CrystalDecisions.Shared.ExportOptions()
Dim myDiskFileDestinationOptions As New
CrystalDecisions.Shared.DiskFileDestinationOptions()
Dim myFormatTypeOptions As New
CrystalDecisions.Shared.PdfRtfWordFormatOptions()
With some variables created, we now need to select where our exported file is going to be saved and what format it is going to be available in:
myDiskFileDestinationOptions.DiskFileName =
"C:\CrystalReports\Chapter08\test.pdf"
myExportOptions = myReport.ExportOptions
With myExportOptions
.ExportDestinationType =
CrystalDecisions.Shared.ExportDestinationType.DiskFile
.ExportFormatType =
CrystalDecisions.Shared.ExportFormatType.PortableDocFormat
.DestinationOptions = myDiskFileDestinationOptions
.FormatOptions = myFormatTypeOptions
End With
Finally, we call the Export method to actually export our report:
myReport.Export()
MsgBox("Your report has been exported in PDF format and saved to
C:\CrystalReports\Chapter08\test.pdf")
When faced with a number of different property collection for destinations, format types and such, it can get a bit messy trying to figure out which combination of properties you need (for example, to export a report to an Exchange folder, in RTF format, but only the first two pages).
There are actually six export formats available for Crystal Reports.NET:
Adobe Acrobat (.pdf)
Crystal Reports within Visual Studio .NET, Crystal Reports 9.0 (.rpt)
HTML 3.2 and 4.0 (l)
Microsoft Excel (.xls)
Microsoft Rich Text (.rtf)
Microsoft Word (.doc)
and two destinations for the exported report:
Disk file
Microsoft Exchange public folders
Important
For more information on the relationship between the objects involved in exporting, as well as the classes and members associated with each of the export formats and destinations, search the Visual Studio.NET Combined Help Collection using the keywords CRYSTAL REPORT EXPORT.
Some of the classes and members that are used with the Crystal Reports Engine are actually part of a CrystalDecisions.Shared namespace, which is shared between the Windows Forms Viewer, Web Forms Viewer, and the Crystal Reports Engine to reduce duplication in these namespaces.
Working with Databases
The strength of Crystal Reports.NET is its ability to extract information from a database (or other data source) and present the information in a report that users can view or print, so it stands to reason that most of your reports will be based on some database or data source within your application.
The Crystal Reports Engine provides a set of tools for working with databases, giving us the flexibility to change the database login information, location, and other features at run time, through the properties and methods associated with the Database object.
There are two classes associated with Database. They are Tables and Links. The Tables class contains all of the tables that are used in your report and the Links class contains all of the links between these tables as created within the report. Using these two classes, you can set the login information for your database, retrieve or change the location of tables, or change the table linking, among other functions.
We will start looking at these classes with one of the most common developer tasks - specifying the database connection information for your report. If you have established database security, you will want to pass the appropriate user name and password for the user who is viewing the report, and the following section will guide you through how this is done.
Logging on to a Database
When creating a report using Crystal Reports.NET, you can include data from multiple data sources in your report. While this feature makes for information-rich reports and can eliminate the need for multiple reports, it does pose a problem when customizing the report at run time.
It would be impossible to set one set of database credentials for all of the data sources in a report, so the Report Engine object model caters for these multiple data sources by allowing you to set the connection information for individual tables that appear in your report through the Tables class. This class has the following members:
Property
Description
Count
Returns the number of Table objects in the collection
Item
Returns the Table object at the specified index or with the specified name
Each Table object in the Tables collection has the following properties:
Property
Description
Fields
Returns the DatabaseFieldDefinitions collection (which we''ll look at a little later in this chapter)
Location
Returns or sets the location of the database table
LogOnInfo
Returns the TableLogOnInfo object
Name
Returns the alias name for the database table used in the report
Now, at this point, you are probably wondering how the TableLogOnInfo actually gets set - there is a method associated with this class, ApplyLogOnInfo, that is used to apply any changes to the database login information for a table.
For collecting and setting the properties relating to TableLogonInfo and connection information, the CrystalDecisions.Shared namespace has a ConnectionInfo class that has the following properties:
Property
Description
DatabaseName
Returns or sets the name of the database
Password
Returns or sets the password for logging on to the data source
ServerName
Returns or sets the name of the server or ODBC data source where the database is located
UserID
Returns or sets a user name for logging on to the data source
We looked briefly at these properties and methods in Chapter 3, but we didn''t tackle looping through the database. We''ll look at that now.
Drag another button onto your Form, and call it Database_Button. Change the Text property to Northwind Report. We''ll create a new Form with this button, so right-click on the project name, select Add | Add New Item.... and then out of the dialog box that pops up, select Windows Form. The default name will be Form2.vb, which is as good as any.
Double-click on our new button, and insert the following code:
Private Sub Database_Button_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Database_Button.Click
Dim Form2 As New Form2()
Form2.Show()
End Sub
Now, drag a CrystalReportViewer onto Form2 in the Design mode, and right-click on the project to Add | Add Existing Item..., Browse to C:\CrystalReports\Chapter08\worldsales_northwind.rpt (this location will vary depending on where you have downloaded the sample code to) and add this report to the project.
Next, drag a ReportDocument component onto the Form and when the dialog box opens, select engine_basic.worldsales_northwind.
The next step is to add some additional code to set our ConnectionInfo class.
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim myReport As New worldsales_northwind()
CrystalReportViewer1.ReportSource = myReport
myReport.Load()
Dim myDBConnectionInfo As New CrystalDecisions.Shared.ConnectionInfo()
With myDBConnectionInfo()
.ServerName = "localhost"
.DatabaseName = "Northwind"
.UserID = "sa"
.Password = "
End With
Note
If you are using a secured Microsoft Access, Paradox, or other PC-type database, the same method can be used, except the .ServerName and .DatabaseName are left blank.
Then we can apply this ConnectionInfo by looping through all of the tables that appear in our report:
Dim myTableLogOnInfo As New CrystalDecisions.Shared.TableLogOnInfo()
Dim myDatabase = myReport.Database
Dim myTables = myDatabase.Tables
Dim myTable As CrystalDecisions.CrystalReports.Engine.Table
For Each myTable In myTables
myTableLogOnInfo = myTable.LogOnInfo
myTableLogOnInfo.ConnectionInfo = myDBConnectionInfo
myTable.ApplyLogOnInfo(myTableLogOnInfo)
Next
End Sub
In this instance, we are looping through the tables using the table object - you can also loop through the tables through the item and the table name or index.
For instance:
myReport.Database.Tables.Item(i).ApplyLogOnInfo())
But it''s up to you.
Setting a Table Location
Another handy trick that the Report Engine provides is the ability to set the location for tables that appear in our report. (This is the equivalent of going into the Report Designer, right-clicking, and selecting Database | Set Location.)
This can be useful for occasions when you have to put historical data into another table or want to separate out data in different tables for different business units, but the structure of the "source" and "target" table have to be the same, or you will get errors when the report is run.
When working with the location of a table, the Location property will both return and set where the table resides.
The example below demonstrates how the location of a table in a report could be changed to point to a "current" employee table.
We will build this example now. In your project, right-click on the project name and select Add | Add New Item... and choose Windows Form. The default name should be Form3.vb. Click OK. Drag a button onto the Design view of Form1.vb and call the button Location_Button and change the Text property to Set Database Location. Double-click on this button and insert the following code:
Private Sub Location_Button_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Location_Button.Click
Dim Form3 As New Form3()
Form3.Show()
End Sub
We shall use employee_listing.rpt to demonstrate the point. This report is already attached to the project, so we do not need to add it. However, what we do need to do is to go into our Xtreme database and create a copy of the Employee table in Access. This copy should be named Employee_Current. Add a few more employee rows onto the end of the table, just so that the information is slightly different, and save it.
Important
There are several versions of Xtreme supplied from various sources, including the ones included with both Crystal Enterprise and Microsoft Visual Studio .NET. Make sure that the version you alter and the data source the report is referencing are the same!
The next thing to do is prepare Form3.vb. In the Design view of this Form, drag on a CrystalReportViewer and a ReportDocument component. When the dialog box for the ReportDocument comes up, select engine_basic.employee_listing.
All that remains is to insert the following code:
Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
MsgBox("Note: To make this sample work, open the Xtreme sample database in
Access and copy the Employee table to Employee_Current and change some
values. You should see these changes when you view the report,
indicating the set location worked correctly")
Dim myReport As New employee_listing()
CrystalReportViewer1.ReportSource = myReport
myReport.Load()
Dim myDatabase = myReport.Database
Dim myConnectionInfo As New CrystalDecisions.Shared.ConnectionInfo()
Dim myTableLogonInfo As New CrystalDecisions.Shared.TableLogOnInfo()
Dim myTables = myDatabase.Tables
Dim myTable As CrystalDecisions.CrystalReports.Engine.Table
For Each myTable In myTables
MsgBox("Before: " & myTable.Location.ToString())
If myTable.Location.ToString() = "Employee" Then
myTable.Location = "Employee_Current"
End If
myTable.ApplyLogOnInfo(myTableLogonInfo)
MsgBox("After: " & myTable.Location.ToString())
Next
CrystalReportViewer1.ReportSource = myReport
CrystalReportViewer1.Refresh()
End Sub
We''re good to go. Run the application and click on the Set Database Location button. Various message boxes should appear, advising you on the changes in the location, as the tables cycle through the For... loop. Eventually the report will load, showing the changes you have made:
You could also use this feature to point to a table that resides on a completely different database platform (from SQL Server to Oracle for example), as long as the table definitions are compatible.
Note
If you want to ensure that your report has the most recent instance of the data you are reporting from, prior to your export you can use the Refresh method to refresh your report against the database.
Setting the Join Type
For reports that are based on more than one table, Crystal Reports.NET has a visual linking tool that allows you to specify the links or joins between these tables:
Note
To see this dialog, open the Report Designer, right-click on your report and select Database | Visual Linking Expert....
When working with these tables and links at run time, it can be confusing when working with all of the different elements involved, so we''ll break it down.
Similarly with Tables, there is a TableLink object that is contained in a TableLinks collection, which has one TableLink object for every link that appears in your report.
Keep in mind that tables can have multiple links between them - for example, you may have only two tables, but there may be three key fields that are linked together between those two tables.
A TableLink has the following properties:
Property
Description
DestinationFields
Returns a reference to table link destination DatabaseFieldDefinitions collection
DestinationTable
Returns a reference to the table link destination Table object
JoinType
Returns a summary of the linking used by the table
SourceFields
Returns a reference to table link source
SourceTable Table
Returns a reference to the table link source Table object
So to determine the tables and database fields used in linking our tables together, we can loop through all of the links used in our report. We''ll look at how we do this now.
Drag another button onto Form1 in the Design view, and name it Links_Button. Change the Text property to Show Links. Double-click on the button and add the following code:
Private Sub Links_Button_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Links_Button.Click
Dim myReport As New employee_listing()
myReport.Load()
Dim myDatabase = myReport.Database
Dim myTables = myDatabase.Tables
Dim myTable As CrystalDecisions.CrystalReports.Engine.Table
Dim myLinks = myDatabase.Links
Dim myLink As CrystalDecisions.CrystalReports.Engine.TableLink
For Each myLink In myLinks
MsgBox("Destination Table: " & myLink.DestinationTable.Name.ToString &
"." & myLink.DestinationFields.Item(1).Name.ToString())
MsgBox("Source Table: " & myLink.SourceTable.Name.ToString & "." &
myLink.SourceFields.Item(1).Name.ToString)
MsgBox("Join Type: " & myLink.JoinType.ToString)
Next
Compile and run. The message boxes should now appear, one after the other, bearing the name of the source and target links and also the join type.
Keep in mind that these properties are read-only - you will not be able to set the table linking using these properties. If you do want to change the database linking that is used, you may want to consider pushing the data into the report using a dataset.
Pushing Data Into a Report
Earlier in our discussion of the different ways you could deploy a report in Chapter 1, we looked at "Push" and "Pull" type reports. Up until this point, we have been working exclusively with "Pull" reports, where we pull the information from the database and display it in our report.
For "Push" reports, you actually create the report the same way, except that when the report is run, you can "Push" a dataset to the report, as we did in Chapter 6. This works in a similar manner to actually setting the data source for an individual table, but instead of setting the property equal to another table, we are going to set it equal to another data source - in the example below, we are using our sample report that we have been working with, but instead of data from the Xtreme sample database, we are actually connecting to the Northwind database on SQL Server to get the data we need.
Dim query = "select * from Customer"
Dim MyOleConn As New System.Data.OleDb.OleDbConnection(conn)
Dim MyOleAdapter As New System.Data.OleDb.OleDbDataAdapter()
Dim MyDataSet As Data.DataSet
MyOleAdapter.SelectCommand = New System.Data.OleDb.OleDbCommand(query, MyOleConn)
MyOleAdapter.Fill(MyDataSet, "Customer")
myReport.Database.Tables.Item("Customer").SetDataSource(MyDataSet)
So instead of actually changing the links and tables that are used within the report, we are actually just pushing another set of data into those structures. As you work with this feature, you are going to pick up some tricks along the way and one of the most handy tricks is to use a SQL command as the basis of your report - this makes pushing data into the report easier, as a report based on SQL command treats the resulting data as if it were one big table.
From your application, you can then get a dataset that matches the fields in your SQL command and then push the data into that one table (instead of having to loop through multiple tables).
Working with Report Options
Another basic task when working with data sources and Crystal Reports is the setting of some generic database options for your report, which are set using the ReportOptions class that relates to the report you are working with - some of these options correspond to the options available in the report designer when you select Designer | Default Settings, but a few (like EnableSaveDataWithReport) are not available in the Report Designer, only through the object model.
Property
Description
EnableSaveDataWithReport
Returns or sets the Boolean option to automatically save database data with a report
EnableSavePreviewPicture
Returns or sets the Boolean option to save a thumbnail picture of a report
EnableSaveSummariesWithReport
Returns or sets the Boolean option to save the data summaries you create with the report
EnableUseDummyData
Returns or sets the Boolean option to use dummy data when viewing the report at design time. Dummy data is used when there is no data saved with the report.
A common use of these types of properties is for saving a report with data to send to other users. These properties can be used in conjunction with the Refresh and SaveAs methods to save a report with data that can be distributed to other users.
To test this, just alter the code in Print_Button code in Form1 as follows:
End With
myReport.Export()
myReport.ReportOptions.EnableSaveDataWithReport = True
myReport.Refresh()
myReport.SaveAs("c:\CrystalReports\Chapter08\saved.rpt",
CryatalDecisions.[Shared].ReportFileFormat.VSNetFileFormat)
MsgBox("Your report has been exported in PDF format and saved to
C:\CrystalReports\Chapter08\test.pdf and your original report has been
saved to C:\CrystalReports\Chapter08\saved.rpt")
End Sub
Even if the user doesn''t have Crystal Reports or Crystal Reports.NET, a simple viewer application created with Visual Studio.NET is all you need to view the report in its native format (or if you export to PDF, the Acrobat viewer) - the code for a sample viewer is included in the code samples for this chapter.
Setting Report Record Selection
When working with Crystal Reports, you will probably want to use record selection to filter the records that are returned. This record selection formula translates to the WHERE clause in the SQL statement that is generated by Crystal Reports.
You can see the record selection formula for a report by right-clicking on your report and selecting Report | Edit Selection Formula | Records. This will open the formula editor and allow you to edit your record selection formula.
Note
The record selection formula within Crystal Reports is written using Crystal Syntax, so you may want to review the section on Crystal Syntax in the previous chapter.
You can retrieve the report''s record selection and set it using the same property, as we saw in the examples in Chapter 3:
myReport.RecordSelectionFormula = "(Employee_Addresses.Country} = ''USA''"
Whenever the report is run, this record selection formula will be applied and the report filtered using the formula specified.
Note
You may look through the object model trying to find where to set the SQL statement that Crystal generates. At this point, your only two options for working with the SQL are to set the record selection (using the method just discussed) which will set the WHERE clause, or creating your own dataset using your own SQL statement and then "pushing" the data into the report.
Working with Areas and Sections
Another often-used class is the ReportDefinition class, which is used to retrieve all of the areas, sections, and report objects shown in your report. An Area within the context of a Crystal Report corresponds to the types of sections we talked about earlier in the book in Chapter 2. There seven different types of areas, including:
Detail
GroupFooter
GroupHeader
PageFooter
PageReader
ReportFooter
ReportHeader
You may remember from Chapter 2 that all of these different types of areas can also have multiple sections within them (Details A, Details B, etc.), as shown below:
All of the areas within your report are held within the Areas collection of the ReportDefinition, which can be accessed through the name of the area or by the number. For example, if you wanted to work with the report header area, you could access it using:
myReport.ReportDefinition.Areas("ReportHeader")
likewise, you could access it using its item number as well:
myReport.ReportDefinition.Areas(1)
So, to start looking at how we can control these areas and sections at run time, we will take a look at areas first.
Formatting Areas
To get a feel for some of the formatting options that are available for sections, open the employee_listing report in the Report Designer, right-click on a section heading and select Format Section...:
Most of the formatting options shown in this dialog can be directly read or set using the properties associated with the AreaFormat class, including:
Property
Description
EnableHideForDrillDown
Returns or sets hide for drill down option
EnableKeepTogether
Returns or sets the keep area together option
EnableNewPageAfter
Returns or sets the new page after option
EnableNewPageBefore
Returns or sets the new page before option
EnablePrintAtBottomOfPage
Returns or sets the print at bottom of page option
EnableResetPageNumberAfter
Returns or sets the reset page number after option
EnableSuppress
Returns or sets the area visibility
So to format our report so that we are suppressing the page header, the code would look something like this:
myReport.ReportDefinition.Areas.Item(1).AreaFormat.EnableSuppress = True
Keep in mind that any properties we set for an area also apply for all of the sections within that area (for example, any formatting applied to the "Report Header" would also apply to "Report Header A", "Report Header B", etc.)
Formatting Sections
For sections within an area, we also have a number of properties within a SectionFormat class that can control an individual section''s appearance, including:
Property
Description
BackGroundColor
Returns or sets the background color of the object using System.Drawing.Color
EnableKeepTogether
Returns or sets the option that indicates whether to keep the entire section on the same page if it is split into two pages
EnableNewPageAfter
Returns or sets the new page after options
EnableNewPageBefore
Returns or sets the new page before option
EnablePrintAtBottomOfPage
Returns or sets the print at bottom of page option
EnableResetPageNumberAfter
Returns or sets the reset page number after option
EnableSuppress
Returns or sets the area visibility
EnableSuppressIfBlank
Returns or sets the option that indicates whether to suppress the current section if it is blank
EnableUnderlaySection
Returns or sets the underlay following section option
All of these properties work just like their counterparts within the Area class. The only one that is not Boolean is the BackGroundColor property, which is set using the System.Drawing.Color palette. If you haven''t used this palette before, you may want to review the constants for the different colors available by searching the combined help on "System.Drawing.Color" and looking through its members.
So to illustrate the use of these properties, we could change the color of our page header and also suppress the page footer for our report. To accomplish this, pull another button onto your form (the final one!) and name it Format_Button. Change the Text property to Format Header Color. In the code behind this button, insert the following:
Private Sub Format_Button_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Format_Button.Click
Dim myReport As New worldsales_northwind()
myReport.Load()
myReport.ReportDefinition.Sections.Item(1).SectionFormat.BackgroundColor =
System.Drawing.Color.AliceBlue
CrystalReportViewerl.ReportSource = myReport
End Sub
Compile and run, and the result should resemble the screenshot below:
Note
If you are unsure of what number a particular section is, open the Report Designer and the section numbers will appear on the horizontal divider that marks the top of the section.
Working with Report Objects
Within the sections of your report, there are a number of report objects - you are probably already familiar with these objects, as they are the fields, graphs, and cross-tab objects, (among others) that appear on your report.
These are:
BlobFieldObject
BoxObject
ChartObject
CrossTabObject
FieldObject
LineObject
MapObject
OlapGridObject
PictureObject
SubreportObject
TextObject
Each of the particular object types within your report has its own unique formatting properties, and may also share common formatting options with other types of objects as well.
To determine what type of object you are working with (and subsequently understand what options are available for each type), you can use the Kind property of the ReportObject to determine the ObjectType:
If section.ReportObjects(1).Kind = ReportObjectKind.FieldObject Then
MsgBox("The first object is a Field Object")
End If
To get started with looking at ReportObjects, we are going to look at the most common type, FieldObjects.
Formatting Common Field Objects
The main content on a Crystal Report is usually a number of fields that have been inserted and shown on your report. These could be database fields, formula fields, or parameter fields and are used to display the data returned by the report.
When working with these fields at run time, there are two different areas in which we can control the field - the content of the field and the format of the field. As most fields share some of the same formatting options, we will look at the formatting first and then break down by field type to see how we can change the content of these fields.
To start with, fields are contained within the ReportDefinition object and can be referenced by either the field name or by an item number:
myReport.ReportDefinition.ReportObjects.Item)"Field1")
or:
myReport.ReportDefinition.ReportObjects.Item(1)
You may be tempted to refer to these fields by their name within Crystal Reports (ReportTitle) but keep in mind that you can add a field to your report multiple times, so in Crystal Reports.NET whenever you add a field to your report, a unique number and name are assigned to that field.
You can see the name of the field by looking at its properties within the Crystal Report Designer, as shown below:
If your development follows a set naming convention, you also can change the name of the field to something other than the default Field1, Field2, etc. Keep in mind that there is no way to change the default naming convention, so you may find changing all of the names to be a bit tedious.
To reference the formatting options for a FieldObject, we need to access common field properties by referencing the FieldObject class, which has the following basic members:
Property
Description
Border
Returns the Border object
Color
Returns or sets the color of the object
DataSource
Returns the FieldDefinition object, which can be used to return and set format information specific to the kind of field
FieldFormat
Returns the FieldFormat object, which can be used to get and set format information specific to the type of field
Font
Returns the Font object (Note: Use the ApplyFont method to apply the changes)
Height
Returns or sets the object height
Left
Returns or sets the object upper left position
Name (inherited from ReportObject)Returns the object name
ObjectFormat
Returns the ObjectFormat object that contains all of the formatting attributes of the object
Top
Returns or sets the object upper top position
Width
Returns or sets the object width
So we can set some of the common formatting properties (such as font and color) directly, as shown in the code example here (it''s not included in the sample application, but you should play around with these properties to see what they can do):
If section.ReportObjects("field1").Kind = ReportObjectKind.FieldObject Then
fieldobject = section.ReportObjects("field1")
fieldObject.Color = Color.Blue
End If
There are specific properties that apply to the FieldFormat depending on what type of field you are working with. When you retrieve the FieldFormat, you will be able to set options that are specific to that field - there are five format types (in addition to a "common" type):
Property
Description
BooleanFormat BooleanFieldFormat
Gets the BooleanFieldFormat object
DateFormat DateFieldFormat
Gets the DateFieldFormat object
DateTimeFormat DateTimeFieldFormat
Gets the DateTimeFieldFormat object
NumericFormat NumericFieldFormat
Gets the NumericFieldFormat object
TimeFormat TimeFieldFormat
Gets the TimeFieldFormat object
In the following sections we are going to look at how to format the different types of fields using their FieldFormat.
Formatting Boolean Fields
With Boolean fields, and the BooleanFieldFormat formatting class, there is only one property, OutputType, which can be set to the following values:
Value
Description
OneOrZero
Boolean value to be displayed as a 1 or 0; 1 = True, 0 = False
TOrF
Boolean value to be displayed as a T or F; T = True, F = False
TrueOrFalse
Boolean value to be displayed as True or False
YesOrNo
Boolean value to be displayed as Yes or No
YOrN
Boolean value to be displayed as a Y or N; Y = True, N = False
So to change a Boolean field that appears on your report from displaying a binary representation to the text True/False, we could set this property to:
fieldObject.FieldFormat.BooleanFormat.OutputType = BooleanOutputType.TrueOrFalse
If you do need to see other values (such as On or Off, or Active or Inactive) you will probably want to create a formula in your report that translates these values for you:
If {Customer.Active} = True then "Active" else "Inactive")
Formatting Date Fields
Date fields within Crystal Reports have their own unique set of formatting properties that can be viewed in the Report Designer by right-clicking a date field and selecting Format. You can format date fields by example (picking a date format that looks similar to what you want) or you can customize most aspects of the date field using the dialog shown here:
To bring this some of the same functionality at run time, the formatting of a date field has been broken up into multiple classes, all members of the DateFieldFormat class:
Property
Description
DayFormat
Returns or sets the day format
MonthFormat
Returns or sets the month format
YearFormat
Returns or sets the year format
All of the properties in these classes can be set separately, so you don''t need to set the month format, for example, if you only want to change how the years are displayed. To start building up a format for a date field within our report, we are going look at the day format first. For the DayFormat property, we have three options:
LeadingZeroNumericDay - A single digit day will be printed with a leading zero (for example, 07)
NoDay - A day is not printed
NumericDay - A day is printed in numeric format with no leading zero
For the MonthFormat, we have five options:
LeadingZeroNumericMonth - The month is printed as a number with a leading 0, for single digit months
LongMonth - The month is printed as text
NoMonth - The month is not printed
NumericMonth - The month is printed as a number with no leading 0
ShortMonth - The month is printed as text in abbreviated format
For the YearFormat, another three:
LongYear - The year is printed in long format with four digits
NoYear - The year is not displayed
ShortYear - The year is printed in short format with two digits
So, to put it all together, here are some examples of how these can produce some commonly requested date formats. To display the date with the days and months with a leading zero, and the full 4-digit year, the code would look like this (again, the next two code snippets are for example only and not included in a sample application of their own, but it is recommended that you experiment with these properties):
With fieldObject.FieldFormat.DateFormat
.DayFormat = DayFormat.LeadingZeroNumericDay
.MonthFormat = MonthFormat.LeadingZeroNumericMonth
.YearFormat = YearFormat.LongYear
End With
The resulting date field would be displayed as "01/01/2003". For displaying only the month and year, we could change the code to read:
With fieldObject.FieldFormat.DateFormat
.DayFormat = DayFormat.NoDay
.MonthFormat = MonthFormat.LeadingZeroNumericMonth
.YearFormat = YearFormat.LongYear
End With
This in turn would display the date as "01/2003". At this point, you have got to be asking yourself - how do I change the separator character? Or, if you have looked at the formatting properties associated with the field in the Report Designer, you might be wondering how you would set some of the other formatting features.
Unfortunately, the object model does not extend to cover all of the formatting features available for every type of field. If you want to change the format of a particular field and don''t see the property listed, you can always create a formula based on the formula field to do the formatting work for you instead.
Formatting Time Fields
For formatting the time fields, the same concept applies, except there is only one class, TimeFieldFormat, which has the following properties:
Property
Description
AMPMFormat
Returns or sets the AM/PM type (either AMPMAfter or AMPMBefore) for 12: 00am or am12:00
AMString
Returns or sets the AM string
HourFormat
Returns or sets the hour type (NoHour, NumericHour, NumericHourNoLeadingZero)
HourMinuteSeparator
Returns or sets the hour-minute separator
MinuteFormat
Returns or sets the minute type (NoMinute, NumericMinute, NumericMinuteNoLeadingZero)
MinuteSecondSeparator
Returns or sets minute-second separator
PMString
Returns or sets the PM string
SecondFormat
Returns or sets the seconds type (NumericNoSecond, NumericSecond, NumericSecondNoLeadingZero)
TimeBase
Returns or sets the time base (On12Hour, On24Hour)
So again, by combining all of these formatting properties, you can set the appearance for any time fields that appear in your report. For example, if you wanted to display the time in 24-hour notation, you could simply set the TimeBase property, as shown here:
fieldObject.FieldFormat.TimeFormat.TimeBase = TimeBase.On24Hour
Or to display any times that would normally be shown as "PM" as "-Evening", you could set the PMstring as shown:
fieldObject.FieldFormat.TimeFormat.PMString = "-Evening"
Which would cause the time field to read "07:13:42-Evening".
Formatting Date-Time Fields
And finally, for date-time fields, all of the classes available for both date and time fields are consolidated under the DateTimeFieldFormat class. The only addition to this class that we haven''t looked at yet is the separator character that will appear between the date and time, which can be set using the DateTimeSeparator property, as shown here:
fieldObject.FieldFormat.DateTimeFormat.DateTimeSeparator = "="
If your report uses date-time fields and you would prefer not to see the date or time component, there is a setting available within the report designer to handle the way date-time fields are processed. Within the Report Designer, right-click on the report and select Report | Report Options to open the dialog shown below:
Use the first drop-down list to select how date-time fields should be interpreted in your report. You can also set this option globally by right-clicking on the report in the Report Designer and selecting Designer | Default Settings. Within the Reporting tab, there is an option for converting date-time fields.
Formatting Currency Fields
Currency fields within Crystal Reports have a number of formatting properties that can be set to create financial reports, statements, and other fiscal information and display the data in the correct format for the type of report that is being created.
You can format a number or currency field in your report by right-clicking on the field in the Report Designer and selecting Format, which will open a dialog that will allow you to format the field, and show a sample of the field in whichever format is chosen from the list. Or you can click the Customize button to control the granular properties associated with formatting.
Unfortunately, not all of these properties can be changed programmatically, but the following properties of the NumericFieldFormat class are supported:
Property
Description
CurrencySymbolFormat
Returns or sets the currency symbol type (FixedSymbol, FloatingSymbol, or NoSymbol).
DecimalPlaces
Returns or sets the number of decimal places.
EnableUseLeadingZero
Returns or sets the option to use a leading zero for decimal values.
NegativeFormat
Returns or sets the negative format type (Bracketed, LeadingMinus, NotNegative, TrailingMinus).
RoundingFormat
Returns or sets the rounding format type. Note: to see all of the different types available, search the Visual Studio Combined Help for "RoundingFormat Enumeration ".
So to change the format of a numeric field, showing a fixed currency symbol with two decimal places and rounding to the second decimal place (tenth), the code would look like this (again, for illustration only and not included in the sample application):
With fieldObject.FieldFormat.NumericFormat
.CurrencySymbolFormat = CurrencySymbolFormat.FixedSymbol
.DecimalPlaces = 2
.RoundingFormat = RoundingFormat.RoundToTenth
End With
Keep in mind that we don''t have the ability to actually change the currency symbol itself through the object model - you will need to set this in the report design itself or use a formula (for instance, If {Customer.Country} = "USA" then "US$" else "UKP") and position the formula immediately before the numeric field (or use yet another formula to concatenate the currency symbol formula and the field itself together.)
