Web-Enabled Decision Support Systems

1
Web-Enabled Decision Support Systems

• Crystal Reports

Prof. Name
name_at_email.com Position
[phone] University Name
2
Overview

• 16.1 Introduction
• 16.2 Crystal Report Primer A Simple Student
  Report
• 16.3 Crystal Report Sections
• 16.4 Customizing Reports Exploring the Design
  Environment
• 16.5 Properties of a CrystalReportViewer Control
• 16.6 Hands-On Tutorial Creating a Pie-Chart to
  Display Salaries
• 16.7 Hands-On Tutorial Creating a Report from
  Multiple Tables
• 16.8 Hands-On Tutorial Creating Parameterized
  Crystal Reports
• 16.9 In-Class Assignments
• 16.10 Summary

3
Introduction

• Crystal Reports is one of the worlds leading
  software packages for creating interactive
  reports
• Allow us to compare, summarize, group, and
  present data to users in a variety of formats
• Invoices, bills, charts, graphs, and more
• Provides a rich collection of features
• Grouping, sorting, summarizing, analyzing,
  applying selection criteria, selecting display
  styles, and graphically presenting data using
  charts
• Integrated and distributed along with the Visual
  Studio package
• We utilize the Visual Studio IDE to design
  reports and integrate them in database
  applications
• Display charts and summarized information on
  Windows forms or web forms
• Built on existing or new data sources or on other
  ADO .NET objects

4
Sample Crystal Reports
5
Overview

• 16.1 Introduction
• 16.2 Crystal Report Primer A Simple Student
  Report
• 16.3 Crystal Report Sections
• 16.4 Customizing Reports Exploring the Design
  Environment
• 16.5 Properties of a CrystalReportViewer Control
• 16.6 Hands-On Tutorial Creating a Pie-Chart to
  Display Salaries
• 16.7 Hands-On Tutorial Creating a Report from
  Multiple Tables
• 16.8 Hands-On Tutorial Creating Parameterized
  Crystal Reports
• 16.9 In-Class Assignments
• 16.10 Summary

6
A Simple Student Report

• The report should display
• Contact information (name, email, and phone) of
  students in the Industrial Engineering (ISE)
  department, grouped by student classes
• Bar chart for the number of ISE students in each
  of the five student classes

The Student Report
7
Application, DB Connection, and Data Source

• How-to Create a Simple Student Report
• Create a new Windows application named
  CrystalReports, using the New Project dialog box.
• Choose the Data Show Data Sources option from
  the Main menu to open the Data Sources Window.
  Click Add New Data Source to start the Data
  Source Configuration wizard.
• Create a new connection to the University
  database copy the database to the current
  project folder when prompted choose the student,
  department, college, and faculty tables from the
  tables nodes on the last page. Click Finish.

8
Adding a Report to the Application

1. Choose Project Add New Item from the Main menu
   to open the Add New Item dialog box. Locate and
   select the Crystal Report option. Name the report
   as StudentReport.rpt in the Name TextBox and
   click Add.

Adding a Crystal Report to the Application
9
Using the Report Expert Wizard

• We choose the mode of the Crystal Report creation
  in the Crystal Reports Gallery dialog box from
  the following options
• Using the Report Wizard
• Choose from a variety of Experts in the bottom
  half of the dialog box
• Experts create different types of reports and
  are, arguably, the most popular methods of
  creating new reports
• As a Blank Report
• Advanced users can build reports from a blank
  report
• No Expert wizards are available for this option
• From an Existing Report
• Imports an existing report, which is used as the
  basis for a new report
• Again, Experts wizards are unavailable with this
  option

10
Using the Report Expert Wizard (cont.)

• Selecting the Using the Report Wizard option
  allows us to choose from three different Experts
  as shown in the table below

11
Using the Report Expert Wizard (cont.)

1. For the student report example, keep the default
   selections, Using the Report Wizard and Standard
   Expert. Click OK.

The Crystal Reports Gallery Dialog Box
12
Specifying Source of the Report Data

1. On the Data page, collapse the Project Data ADO
   .NET DataSets CrystalReports.UniversityDataSet
   nodes to see the list of data tables.
2. Add the student table from the Available Data
   Sources pane to the Selected Tables pane. Click
   Next.

Specifying Data Sources
13
Selecting the Fields of the Report

1. On the Fields page, collapse the tblStudent node
   to view its available fields. Add the StudentID,
   DeptID, Name, Class, Email, and Phone fields of
   the student table from the Available Fields pane
   to the Fields to Display pane. Click Next .

Selecting Relevant Fields for the Report
14
Performing Grouping Operations

1. On the Grouping page, add the tblStudent.Class
   field from the Available Fields pane to the Group
   By pane. Accept the default in ascending order
   sorting order at the bottom of the wizard page
   and click Next.

Group By Field of the Report
15
Performing Summary Operations

1. On the Summary page, add the tblStudent.StudentID
   field from the Available Fields pane to the
   Summarized Fields pane.
2. Select the StudentID summary field from the
   Summarized Fields pane and choose the Count
   operation from the drop-down list below. Click
   Next.

Counting the Number of Students
16
Using the Group Sorting Feature

1. On the Group Sorting page, accept the default
   None option for the Class group and click Next.

Group Sorting Options
17
Using the Chart Option

• Chart is one of the most attractive features of
  the Crystal Reports
• On the Chart page of the Expert wizard, we can
  specify
• Chart type
• Chart title
• Chart data
• Chart types
• Bar Chart
• Uses vertical or horizontal bars to show the
  relationship between data
• Line Chart
• Uses lines along a grid to present the data
• Pie Chart
• A circle divided into segments, with each piece
  of the pie representing the data

18
Using the Chart Option (cont.)

1. On the Chart page, select the Bar Chart radio
   button. Select tblStudent.Class as the On change
   of field (X-axis) and Count of
   tblStudent.StudentID as the Show summary field
   (Y-axis). Name the chart title as shown in below.
   Click Next.

Selecting the Chart Type as a Bar Chart
19
Filtering the Report

1. On the Record Selection page, move the
   tblStudent.DeptID field from the Available Fields
   pane to the Filter Fields pane.
2. Select the DeptID field in the Filter Fields
   pane, and choose the is equal to item from the
   drop-down list. Assign the value of the
   expression ISE in the second drop-down list and
   click Next.

Applying Filtering Criteria
20
Selecting the Report Style

1. On the Report Style page, select the Red/Blue
   Border style for the student report. Click Finish
   to add the report to the application.

Selecting the Report Style
21
Selecting the Report Style (cont.)

1. Select the bar chart object from the report
   header and right-click the selection. Choose the
   Chart Options Template option from the
   short-cut menu to open the Chart Option dialog
   box. Select the Use Depth option and click OK.

The Crystal Reports Design Environment
22
Adding a CrystalReportViewer

1. Open Form1 in the Design Window. Drag and drop
   the CrystalReportViewer control under the Crystal
   Reports tab in the Toolbox on Form1.

Adding a CrystalReportViewer Control to a Windows
Form
23
Binding the Report and Running

1. Double-click on the Form1 to open the Code
   Window. Replace Form1s Form_Load event with the
   code below. Press Ctrl F5 to run the
   application.

Getting Data
Binding Report
Crystal Report Binding Code
24
Overview

• 16.1 Introduction
• 16.2 Crystal Report Primer A Simple Student
  Report
• 16.3 Crystal Report Sections
• 16.4 Customizing Reports Exploring the Design
  Environment
• 16.5 Properties of a CrystalReportViewer Control
• 16.6 Hands-On Tutorial Creating a Pie-Chart to
  Display Salaries
• 16.7 Hands-On Tutorial Creating a Report from
  Multiple Tables
• 16.8 Hands-On Tutorial Creating Parameterized
  Crystal Reports
• 16.9 In-Class Assignments
• 16.10 Summary

25
Crystal Report Sections

• A Crystal Report consists of a number of
  sections
• Report Header/Footer
• Appear once at the beginning/end of the report
• Page Header/Footer
• Appear on each page of the report
• Footer includes page number by default
• Group Header/Footer
• Appear automatically for each group
• Details
• Core section of a report
• Appears once for each record

Crystal Report Sections
26
Overview

• 16.1 Introduction
• 16.2 Crystal Report Primer A Simple Student
  Report
• 16.3 Crystal Report Sections
• 16.4 Customizing Reports Exploring the Design
  Environment
• 16.5 Properties of a CrystalReportViewer Control
• 16.6 Hands-On Tutorial Creating a Pie-Chart to
  Display Salaries
• 16.7 Hands-On Tutorial Creating a Report from
  Multiple Tables
• 16.8 Hands-On Tutorial Creating Parameterized
  Crystal Reports
• 16.9 In-Class Assignments
• 16.10 Summary

27
Formatting Charts and Fields

• We format charts to make them look more appealing
• Includes modifying the chart type, editing its
  title, layout, data labels, axis labels, and grid
  lines settings
• There are four different options available in the
  Chart Options menu to format a chart

28
Formatting Charts and Fields (cont.)

• How-to Format Charts and Fields
• Open the StudentReport.rpt file from the Solution
  Explorer to view the reports design.
• Right-click on the chart object in the report
  header (Section 1) to display its formatting
  options. Choose the Chart Options item.

Chart Options Item for Chart Formatting
Properties of Report Charts and Fields
29
Editing Chart Design

• To edit any of the Expert wizard options
• Right-click outside the chart object in the
  report header section and choose the appropriate
  option from the short-cut menu.

Editing Chart Design
30
Overview

• 16.1 Introduction
• 16.2 Crystal Report Primer A Simple Student
  Report
• 16.3 Crystal Report Sections
• 16.4 Customizing Reports Exploring the Design
  Environment
• 16.5 Properties of a CrystalReportViewer Control
• 16.6 Hands-On Tutorial Creating a Pie-Chart to
  Display Salaries
• 16.7 Hands-On Tutorial Creating a Report from
  Multiple Tables
• 16.8 Hands-On Tutorial Creating Parameterized
  Crystal Reports
• 16.9 In-Class Assignments
• 16.10 Summary

31
Properties of a CrystalReportViewer Control

• We can further enhance report appearance and
  behavior by manipulating properties of the
  CrystalReportViewer control

32
Overview

• 16.1 Introduction
• 16.2 Crystal Report Primer A Simple Student
  Report
• 16.3 Crystal Report Sections
• 16.4 Customizing Reports Exploring the Design
  Environment
• 16.5 Properties of a CrystalReportViewer Control
• 16.6 Hands-On Tutorial Creating a Pie-Chart to
  Display Salaries
• 16.7 Hands-On Tutorial Creating a Report from
  Multiple Tables
• 16.8 Hands-On Tutorial Creating Parameterized
  Crystal Reports
• 16.9 In-Class Assignments
• 16.10 Summary

33
Creating the Report

• How-to Create a Pie-Chart to Display Faculty
  Salaries
• Add Form2 to the CrystalReports application.
• Choose the Project Add New Item option from the
  Main menu to invoke the Add New Item dialog box.
  Choose the Crystal Report item, and name the
  report FacultySalary.rpt. Click Add to create a
  new Crystal Report.
• Select the default Using the Report Wizard option
  with the Standard Expert selection in the Crystal
  Reports Gallery dialog box. Click OK.

34
Creating the Report (cont.)

1. On the Data page of the wizard, select the
   tblFaculty data table under Project Data
   ADO.NET Datasets in the Available Data Sources
   pane, and add it to the Selected Tables pane.
   Click Next.
2. On the Fields page, select and add the FacultyID,
   DeptID, and Salary fields to the Fields to
   Display pane. Click Next.
3. On the Grouping page, select and add the DeptID
   field to the Group By pane. Click Next.

Choosing Tables on the Data Page
35
Creating the Report (cont.)

1. On the Summaries page, remove the Sum of
   tblFaculty.FacultyID field from the Summarized
   Fields pane. For the tblFaculty.Salary field,
   choose the Average summary option from the
   drop-down list at the bottom of the page. Click
   Next.

Using the Summary Page
36
Creating the Report (cont.)

1. On the Group Sorting page, select the None
   ordering option and click Next.
2. On the Chart page, choose the Pie Chart option.
   Change the Chart Title box as shown below. Accept
   default values for the On Change of and Show
   summary drop-down lists. Click Finish.

Setting up a Pie Chart
37
Creating the Report (cont.)

1. In the FacultyReport.rpts design view, remove
   all the fields in the various report sections
   except for the chart object in the report header.
2. Select the chart object and right-click the
   selection. Choose Chart Options General item
   from the short-cut menu to open the Chart Options
   dialog box.
3. Navigate to the Data Labels tab and configure it
   as shown below.

Configuring Data Labels on the Pie Chart
38
Binding the Report

1. Add a CrystalReportViewer control from the
   ToolBox on Form2.
2. Using the Properties Window of the control,
   change its Dock property from Fill to None, and
   set its DisplayGroupTree property to False.
   Adjust its length and width on the form.

Un-docking the CrystalReportViewer Control
39
Binding the Report (cont.)

1. Drag and drop the tblFaculty data table from the
   Data Sources Window on Form2.
2. Remove the auto-created DataGridView control and
   the ToolStrip control from Form2.
3. Replace the forms Form_Load event with the code
   shown below.

Component Tray
Binding Code
40
Binding the Report (cont.)

1. Set the Form2 as the start-up form and run the
   application.

Running Application Average Faculty Salaries on
a Pie Chart
41
Overview

• 16.1 Introduction
• 16.2 Crystal Report Primer A Simple Student
  Report
• 16.3 Crystal Report Sections
• 16.4 Customizing Reports Exploring the Design
  Environment
• 16.5 Properties of a CrystalReportViewer Control
• 16.6 Hands-On Tutorial Creating a Pie-Chart to
  Display Salaries
• 16.7 Hands-On Tutorial Creating a Report from
  Multiple Tables
• 16.8 Hands-On Tutorial Creating Parameterized
  Crystal Reports
• 16.9 In-Class Assignments
• 16.10 Summary

42
Creating a Report Based on Multiple Tables

• We often need to create a Crystal Report based on
  the data from the join of two or more tables
• Desired report
• Plot a line chart of average student grades for
  each calendar year.
• In the University database
• Grade information is stored in the transcript
  table for student-section pairs
• The year in which the sections are offered is
  stored in the section table

43
Setting up the Data Table

• How-to Create a Crystal Report Based on Multiple
  Tables
• Open the Dataset Designer. Right-click anywhere
  in the designer, and choose the Add TableAdapter
  option from the short-cut menu to open the
  TableAdapter Configuration wizard.
• On the Enter a SQL Statement page of the wizard,
  build the SQL query as shown below, and finish
  the wizard.

Average Grade Line Charts Base Query
44
Setting up the Data Table (cont.)

1. The wizard adds a DataTable and TableAdapter in
   the Data Sources Window. Rename these objects as
   tblGrades and tblGradesTableAdapter as shown
   below.

TableAdapter and DataTable Objects for the Join
Query
45
Creating the Report

1. Add Form3 to the CrystalReports application.
2. Add an AverageGrades.rpt report to the
   application.
3. Accept the default selections in the Crystal
   Reports Gallery dialog box.
4. Select the tblGrades data table under the Project
   Data ADO.NET Datasets item and add it to the
   Selected Tables pane.

Choosing Data for the Grades Report
46
Creating the Report (cont.)

1. On the Fields page, select and add the Year and
   Grade fields to the Fields to Display pane. Click
   Next.
2. On the Grouping page, add the Year field to the
   Group By pane. Click Next.
3. On the Summaries page, remove any additional
   fields from the Summarized Fields pane. For the
   tblSection.Grade field, choose the Average
   summary option. Click Next.

Choosing Average Function for the Grade Field
47
Creating the Report (cont.)

1. On the Group Sorting page, accept the None
   ordering option and click Next.
2. On the Chart page, choose the Line Chart option.
   Change the Chart Title box as shown. Accept
   default values for the On change of and Show
   summary drop-down lists. Click Finish.

Setting up the Line Chart
48
Creating the Report (cont.)

1. In the reports design view, remove all the
   fields in the various report sections except the
   chart object in the report header.
2. Select the chart object from the report header
   and right-click the selection. Choose Chart
   Options Template from the short-cut menu to
   open the Chart Option dialog box. Check the Use
   Depth option and click OK.
3. Now choose Chart Options Titles from the same
   short-cut menu, and change the Y-axis title to
   Average Grade. Click OK to accept changes.

49
Creating the Report (cont.)

1. Choose the Chart Options Grid option, and
   navigate to the Scales tab in the Numeric Axis
   Grids Scales dialog box. Set up the scales as
   shown.
2. In the same dialog box, navigate to the Grids tab
   and check the Use Manual Grid option. Set the
   interval value to 0.5.

Adjusting Y-axis Scales
50
Binding the Report

1. Open Form3 in the Design Window, and add a
   CrystalReportViewer control. Change the controls
   Dock property from Fill to None. Adjust its
   length and width. Also use the Property Window to
   set the DisplayGroupTree property to False.
2. Drag and drop the newly created tblGrades data
   table from the Data Sources Window on Form3.
3. Remove the auto-created DataGridView control and
   the ToolStrip control.

TableAdapter and Dataset Components for Chart Data
51
Binding the Report (cont.)

• Replace the forms Form_Load event with the code
  below.
• Set Form3 as the start-up form
• and run the application.

Running Application Line Chart
52
Overview

• 16.1 Introduction
• 16.2 Crystal Report Primer A Simple Student
  Report
• 16.3 Crystal Report Sections
• 16.4 Customizing Reports Exploring the Design
  Environment
• 16.5 Properties of a CrystalReportViewer Control
• 16.6 Hands-On Tutorial Creating a Pie-Chart to
  Display Salaries
• 16.7 Hands-On Tutorial Creating a Report from
  Multiple Tables
• 16.8 Hands-On Tutorial Creating Parameterized
  Crystal Reports
• 16.9 In-Class Assignments
• 16.10 Summary

53
Creating Parameterized Crystal Reports

• Static reports are designed to display
  information with certain fixed characteristics
• We can use parameterized Crystal Reports to
  display information based on parameter values
• Desired report
• Display contact information and a class bar
  chart for any user-selected department.
• The department selected by the user becomes a
  parameter for the report query
• The query result is displayed on the report
  dynamically

54
Edit a Copy of an Existing Report

• How-to Create Parameterized Crystal Reports
• Select the previously created StudentReport.rpt.
  Choose Edit Copy and Edit Paste from the Main
  menu. Rename it as ParamStudentReport.rpt.
• Double-click on ParamStudentReport.rpt to open it
  in the Design Window. Right-click outside the
  chart object, and choose Report Select Expert
  from the short-cut menu to open the Select Expert
  dialog box.
• Click Delete to remove the existing selection
  criterion, is equal to ISE. Click OK to accept
  the changes.

Removing Selection Criteria from an Existing
Report
55
Adding a Parameter Query

1. Open the Dataset Designer, select the
   TblStudentTableAdapter, and right-click to choose
   the Add Query option to open the TableAdapter
   Query Configuration wizard.
2. On the page Specify a SQL SELECT Statement, enter
   the SQL query as shown below and click Next.
3. Name the methods FillByDeptID and
   GetDataByDeptID, and click Finish.

Parameterized Query for the Student Report
56
Adding a ToolStrip Control

1. Add a new form, Form4, to the CrystalReports
   application. Drag and drop the CrystalReportViewer
   control onto the form.
2. Set the Dock property of the control to None. Add
   a ToolStrip control from the All Windows Forms
   tab of the Toolbox on Form4.
3. Position the ToolStrip on top of the page. Use
   the drop-down list on the ToolStrip control to
   add a Label control.

Adding Controls to the ToolStrip Control
57
Adding a ToolStrip Control (cont.)

1. Select the newly added Label control and alter
   its Text property using the Property Window.
2. Similarly, add a TextBox and Button controls to
   the ToolStrip. Manipulate their properties as
   shown in the table below.

Property Values for Controls in the ToolStrip
58
Binding the Report

1. Enter the code shown below for Form4.

Form_Load Event Code for a Parametric Student
Report
59
Binding the Report (cont.)

• Enter the code shown below for the Click event of
  the command button in the ToolStrip.

Click Event Code for a Parametric Student Report
60
Binding the Report (cont.)

1. Set Form4 as the start-up form and run the
   application. Test the application by entering
   different DeptIDs in the ToolStrips TextBox
   control.

Running application A Parameterized Student
Report
61
Overview

• 16.1 Introduction
• 16.2 Crystal Report Primer A Simple Student
  Report
• 16.3 Crystal Report Sections
• 16.4 Customizing Reports Exploring the Design
  Environment
• 16.5 Properties of a CrystalReportViewer Control
• 16.6 Hands-On Tutorial Creating a Pie-Chart to
  Display Salaries
• 16.7 Hands-On Tutorial Creating a Report from
  Multiple Tables
• 16.8 Hands-On Tutorial Creating Parameterized
  Crystal Reports
• 16.9 In-Class Assignments
• 16.10 Summary

62
In-Class Assignment

• In this section we assign a simple problem that
  covers most of the features we have discussed in
  this chapter.
• Create an application that plots a bar chart for
  the number of students in each department.
• Do not display any record fields in the report,
  the chart being the only requirement.
• Further, users should be able to view the bar
  chart for any college they select from a
  drop-down list of all colleges in the university.

63
Overview

• 16.1 Introduction
• 16.2 Crystal Report Primer A Simple Student
  Report
• 16.3 Crystal Report Sections
• 16.4 Customizing Reports Exploring the Design
  Environment
• 16.5 Properties of a CrystalReportViewer Control
• 16.6 Hands-On Tutorial Creating a Pie-Chart to
  Display Salaries
• 16.7 Hands-On Tutorial Creating a Report from
  Multiple Tables
• 16.8 Hands-On Tutorial Creating Parameterized
  Crystal Reports
• 16.9 In-Class Assignments
• 16.10 Summary

64
Summary

• Reports are the best way to create a printed copy
  of information.
• They allow us to compare, summarize, and subtotal
  large data sets in well-designed and professional
  forms
• Invoices, purchase orders, mailing orders,
  monthly bills, etc.
• Crystal Reports is one of the worlds leading
  software packages for creating interactive
  reports.
• Crystal Reports are widely used and have set the
  standard for reports.
• We can also build a report based on the
  TableAdapter that has a join of multiple tables.
  
• We introduce a powerful technique to parameterize
  reports, and we display reports based on the
  results of parameterized queries.

65
Summary (cont.)

• Adding Crystal Reports on a Windows form involves
  the following steps
• Add a Crystal Report to the application.
• Use the Report Expert to specify the report
• Data source, report fields, group-by fields,
  aggregate function, selection fields, and
  filtering criteria
• In the Expert wizard, specify a chart type, chart
  titles, and chart data.
• Add a CrystalReportViewer control on the Windows
  form.
• Add the data source used for report design
• Either in the Component tray or create it
  programmatically at run-time.
• Bind the report to the viewer control.

66
Additional Links

• Add links here.