Crystal Reports

1
Chapter 9

• Crystal Reports

2
Objectives

• Create a blank Crystal Report
• Preview a report with the CrystalReportViewer
  control
• Create fields on a Crystal Report
• Create reports based on the data from multiple
  database tables
• Create a tabular report with a control break
• Export reports
• Configure a report to display information
  dynamically

3
Introduction to Crystal Reports

• Crystal Reports is a reporting tool
• Use to design and implement presentation quality
  reports and graphs
• Visual designer is called the Crystal Reports
  Designer
• Create reports visually
• Designer uses several dialog boxes to specify
  report attributes

4
Solution Explorer Displays Report Files
Report Files
5
Sample Report
6
Processing Stagesof a Crystal Report
7
Creating a Report

• Add a Crystal Report to a project
• Use the Add New Item dialog box
• Another dialog box (Crystal Report Gallery)
  appears allowing you to define the type of report
  to create
• Report Experts are similar to Wizards
• Blank reports contain default sections but no
  fields
• Copy one report from another report

8
Crystal Report Gallery
Create a blank report
Available report experts
9
Five Sections of the Crystal Reports Designer

• Report Header section appears once at the top of
  the first report page
• Contents of the Page Header section appear at the
  top of each page
• The Details section contains the report data
• Contents of the Page Footer section appear at the
  bottom of each page
• Contents of the Report Footer section appear at
  the end of the report

10
Blank Crystal Report
Report Sections
Section height ruler
Field Explorer
11
Crystal Reports Namespaces

• When adding a Crystal Report to a project, VB
  .NET adds references to the following three
  namespaces
• The CrystalDecisions.CrystalReports.Engine
  namespace allows you to access the elements of a
  report and to define how the report will be
  printed
• You need not typically use this namespace
• Used internally by Crystal Reports itself
• The CrystalDecisions.ReportSource namespace
  provides an interface layer between the Crystal
  Reports Engine and a Windows form
• The CrystalDecisions.Shared namespace contains
  generic classes to define parameters, page
  margins, and how the page is aligned about the
  report

12
Crystal Reports Files

• When creating a blank report, Crystal Reports
  generates the following two files
• One file has a suffix of .rpt
• The .rpt file contains the report itself
• Crystal Reports also generates a corresponding
  .vb file
• It contains a class that derives from the generic
  Crystal Reports ReportClass
• Similar to a strongly typed DataSet

13
Common Report Elements

• Crystal Reports controls
• Contain constant information such as a report
  title or a column header
• Controls appear on the Crystal Reports tab of the
  Toolbox
• Cannot use Win Forms controls on a Crystal Report
• Crystal Reports fields
• Typically store dynamic and calculated data
• Conceptually similar to controls

14
Crystal Reports Controls

• Crystal Reports supports three controls
• Text Object control is used to create a box
  containing literal text that does not change as
  the report is printed
• Line Object control is used to draw a horizontal
  or vertical line
• Diagonal lines are not supported
• Box Object control draws a flat or
  three-dimensional rectangle on the report

15
Creating Control Instances

• Create a control instance by selecting it in the
  Toolbox and creating it on the report
• The height of controls, like a text box, are
  initially defined by font size
• Reposition by selecting control instance and
  moving it
• Delete by selecting and pressing Delete key

16
Format Editor

• Contains tabs to apply formatting attributes
• Available tabs depend on control type or field
  type
• Box and Rounding tabs are used to format boxes
• Common tab is used to hide fields and rotate text
• Border tab defines various borders
• Font tab is used to define font and font
  attributes
• Line tab defines color and thickness of a line
• Paragraph tab is used with multi-line text
  object, RTF, and HTTP data

17
Format Editor (Border Tab)
18
Format Editor (Line Tab)
19
Other Formatting Properties

• Currency related properties
• CurrencyPositionType
• CurrencySymbol
• CurrencySymbolType
• DecimalPlaces
• Date related properties
• DateCalendarType
• DateOrder
• Time properties
• TimeBase
• YearType

20
Previewing a Report

• CrystalReportViewer control is used to preview a
  report
• Properties
• Dock property has the same meaning as the Dock
  property of other controls
• DisplayToolbar property (Boolean) defines whether
  or not a toolbar appears along the top of the
  control instance

21
CrystalReportViewer (Properties)

• A drill-down interface appears along the left
  side of the control instance if the Boolean
  EnableDrillDown property is True
• ReportSource property defines the name of the
  Crystal Reports file
• ShowPageNavigationButtons property defines
  whether the user can navigate to different report
  pages
• ShowPrintButton property defines whether the
  Print button appears at the top of the control
  instance
• ShowZoomButton property defines whether or not
  zooming is enabled

22
Previewing a Report (Methods)

• PrintReport method prints the report displayed in
  the CrystalReportViewer control instance
• The current page displayed can be set by calling
  the ShowFirstPage, ShowNextPage,
  ShowPreviousPage, and ShowLastPage methods
• ShowNthPage method displays a particular report
  page
• Zoom method changes the magnification level

23
Previewing a Report (Events)

• Navigate event fires as the user navigates from
  one report page to another
• If an exception fires in the CrystalReportViewer,
  create an event handler for the HandleException
  event

24
Crystal Reports Fields (1)

• Field Explorer categorizes fields into seven
  types using a drill-down interface
• A Database field corresponds to a field in a
  database table
• Formula fields perform calculations using the
  contents of Database fields, other Formula
  fields, or Unbound fields
• Parameter fields allow you to select or specify
  data dynamically at runtime either from a Crystal
  Reports-supplied dialog box or programmatically

25
Crystal Reports Fields (2)

• Group Name fields allow similar data to be
  grouped together to form a control break report
• Running Total fields
• Tally the values for multiple rows
• Perform calculations on those rows
• Special fields provide the means to display
  information common to most reports
• Current date or page number
• Unbound fields store numeric, string, or Boolean
  data that is not associated with a Database field

26
Connecting to a Database

• Crystal Reports works with ADO.NET through a
  Schema Definition File (.xsd) file
• Uses typed or untyped DataSets
• You use Database Expert to associate report with
  a database

27
Database Expert
Select button
Select all button
28
Database Fields

• Database fields display data from an ADO.NET
  DataSet
• Use Field Explorer to create fields
• Create Database fields in any report section but
  typically they are created in the details section

29
Creating Database Fields
Database fields
30
ReportDocument Class

• Use to manage a report programmatically
• Crystal Reports creates a class having the same
  name as the report
• Example to create an instance of the Report class
• Dim crReportDocument As _
• EmployeeReport
• crReportDocument New _
• EmployeeReport

31
ReportDocument Class (Properties)

• DataBase property references a DataBase object
• Various field categories, such as Database
  fields, Formula fields, and Parameter fields,
  correspond to collections contained within the
  DataDefinition property
• ExportOptions property allows a report to be
  exported to another format
• Excel, Word, HTML
• PrintOptions property stores a reference to a
  PrintOptions object
• Use to specify the page margins and the paper
  size, and to select a printer
• SummaryInfo property defines general information
  about the report
• report author, report title, comments, etc.

32
ReportDocument Class (Methods)

• Close method destroys the ReportDocument, thereby
  destroying the elements of the report, such as
  the DataDefinition
• Export method uses the contents of the
  ExportOptions property to export a report to a
  file or other destination
• FilePath property returns the path and file name
  of the report
• Load method explicitly loads a report
• Once a report is loaded, access the properties of
  the report, such as the DataDefinition
• PrintToPrinter method sends the report to a
  printer on the system
• SetDataSource method associates a populated
  DataSet with the Crystal Reports engine

33
ReportDocument Class (Events)

• InitReport event fires for the report just after
  the report loads

34
Managing a Crystal Report Programmatically
(Example)

• Create a report instance and populate the DataSet
• Dim crReportDocument As New EmployeeReport()
• odbdaEmployees.Fill(DsEmployees1)
• Associate the DataSet with the report
• crReportDocument.SetDataSource(DsEmployees1)
• Associate the CrystalReportViewer control
  instance with the report
• crvEmployees.ReportSource crReportDocument

35
Working with MultipleTables in Crystal Reports

• Steps
• Define a DataSet containing multiple tables
• Fill that DataSet from multiple OleDbDataAdapters
• Associate the DataSet with a Crystal Report

36
Creating Formula Fields

• Formula Editor is divided into three panes,
  called trees
• Field Tree appears in the left-most pane
• Lists the fields that have been created on the
  report in the Report Fields section
• Function Tree appears in the middle pane
• Lists the mathematical and statistical functions
  that you can apply to the fields appearing in the
  Field Tree
• Operator Tree contains the mathematical, string,
  relational, and conditional operators supported
  by Crystal Reports

37
Formula Editor
Operator Tree
Function Tree
Field Tree
Completed formula
38
Crystal Reports Formulas

• Syntax differs from VB .NET expressions
• Field names appear in braces
• Expression has no left side
• Example Add two fields
• tblPayroll.fldFIT tblPayroll.fldFICA

39
Crystal Reports Functions

• Financial functions are supported
• String functions
• Formulas can contain conditional operators and
  control structures
• Logical operators are supported

40
Special Fields

• Special fields are nothing more than ordinary
  field objects that Crystal Reports automatically
  configures for you to form specific tasks
• Print Date field prints the current date
• Print Time field prints the current time
• Record Number field prints the 1-based record
  number
• Page Number field prints the 1-based page number
• Page N of M field prints the current page number
  and the total number of pages
• Total Page Count field prints the total number of
  pages

41
Special Fields (Illustration)
Special Fields
42
Control Break Reports

• A control break report is a report where some
  special processing occurs when the value of a
  particular field changes
• When the value of some defined field changes, a
  control break occurs
• Normal processing is suspended
• Special processing occurs
• Totals are typically printed
• Normal processing resumes until that fields
  value changes again

43
Creating a Control Breakwith the Group Name Field
Records are grouped by the selected field
Groups will be sorted in ascending order
44
Insert Group Dialog Box (1)

• Fields in the Insert Group dialog box
• In the top list box, select the field that marks
  the control break
• In the second list box, select whether the
  records will be sorted in ascending order,
  descending order, a custom order, or not sorted
  at all
• The Group Options section allows you to customize
  the behavior of the group

45
Insert Group Dialog Box (2)

• If the Keep Group Together check box is checked,
  then Crystal Reports will try to print the
  records in the group on the same page
• If the records in a group span multiple pages and
  the Repeat Group Header On Each Page check box is
  checked, then the Group header will be printed on
  the continuation pages for the group

46
Report with Group Headers
47
Reordering Groups
Click to reorder groups
48
Creating Running Totals (1)

• Running totals summarize the value of a
  particular column
• General options applicable to all Running Total
  fields
• Running Total field must have a unique name
• In the Summary section, define the field to
  summarize
• Once a field has been selected, the Type of
  Summary list box is enabled allowing you to
  specify how the data should be summarized

49
Creating Running Totals (2)

• The Evaluate section
• If the For each record option is selected, then
  the running total will be calculated for each
  detail record processed
• The On change of field option is typically used
  with control break reports
• The On change of group option is also used with
  control break reports
• The Use a formula option is used to create a
  custom condition, which, in turn, is used to
  determine when the total should be recalculated

50
Creating Running Totals (3)

• The Reset section
• If the Never option is selected, the running
  total is never reset
• Use for grand totals
• The On change of field option is used with
  control break reports
• The On change of group option is used with
  control break reports
• Use for subtotals
• The Use a formula option is used to create a
  custom condition, which, in turn, is used to
  determine when the total should be reset

51
Creating Running Totals (Illustration)
52
Report with Subtotals and Grand Totals
53
Exporting Reports (1)

• The ExportOptions object supports four properties
• The DestinationOptions property can store one of
  three different object types
• DiskFileDestinationOptions
• ExchangeFolderDestinationOptions
• MicrosoftMailDestinationOptions
• The DestinationType property contains one of
  three enumeration values
• DiskFile
• ExchangeFolder
• MicrosoftMail

54
Exporting Reports (2)

• The ExportFormatType property defines the type of
  output Crystal Reports will produce
• The FormatOptions property stores one of three
  object types
• The object type corresponds to the
  ExportFormatType
• ExcelFormatOptions
• HTMLFormatOptions
• PdfRtfWordFormatOptions

55
Exporting a Report as anExcel Workbook (Example)

• Dim FileName As String "C\ExcelFile.xls"
• Dim diskOpts As New DiskFileDestinationOptions()
• Dim exportOpts As New ExportOptions()
• diskOpts.DiskFileName FileName
• crReportDocument.ExportOptions.DestinationOptions
  _
• diskOpts
• crReportDocument.ExportOptions.ExportFormatType
  _
• CrystalDecisions.Shared.ExportFormatType.Excel
• crReportDocument.ExportOptions.ExportDestinationTy
  pe _
• CrystalDecisions.Shared.ExportDestinationType.Dis
  kFile
• crReportDocument.Export()

56
Communicating withParameters Programmatically

• Reference each of the Crystal Reports fields
  programmatically through the DataDefinition class
  of the ReportDocument
• First, define the Parameter field
• Second, write the statements to fill in the
  report parameter at runtime

57
Create Parameter Field Dialog Box
58
Select Expert
59
Enter Parameter Values Dialog Box
60
DataDefinition Class (Properties)

• FormulaFields property has a data type of
  FormulaFieldDefinitions
• GroupNameFields property has a data type of
  GroupNameFieldDefinitions
• ParameterFields property has a data type of
  ParameterFieldDefinitions
• Each object in the collection corresponds to a
  Parameter field defined in the report
• The RunningTotalsFields property corresponds to
  the running totals appearing on the report

61
ParameterRangeValue Object

• ParameterRangeValue object properties
• The StartValue property defines the minimum value
  of the range
• The EndValue property defines the maximum value
  of the range
• If the LowerBoundType property is set to
  Inclusive, then the StartValue is included in the
  range. If the property is set to Exclusive, then
  the StartValue is not included in the range
• The UpperBoundType property works in the same way
  as the LowerBoundType property, but works with
  the EndValue instead of the StartValue

62
Configuring Parameters Programmatically