Web-Enabled Decision Support Systems

Web-Enabled Decision Support Systems Crystal Reports Prof. Name name@email.com Position (123) 456-7890 University Name

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

Introduction • Crystal Reports is one of the world’s 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

Sample Crystal Reports

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

Application, DB Connection, and Data Source • How-to: Create a Simple Student Report • Create a new Windows application named CrystalReports, using the NewProject dialog box. • Choose the Data | Show Data Sources option from the Main menu to open the Data Sources Window. Click AddNewDataSource to start the DataSourceConfiguration 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 table’s nodes on the last page. Click Finish.

Adding a Report to the Application • Choose Project | Add New Item from the Main menu to open the Add New Item dialog box. Locate and select the CrystalReport option. Name the report as StudentReport.rpt in the NameTextBox and click Add. Adding a Crystal Report to the Application

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

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:

Using the Report Expert Wizard (cont.) • For the student report example, keep the default selections, Using the Report Wizard and Standard Expert. Click OK. The Crystal Reports Gallery Dialog Box

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

Selecting the Fields of the Report • 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

Performing Grouping Operations • 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

Performing Summary Operations • On the Summary page, add the tblStudent.StudentID field from the Available Fields pane to the Summarized Fields pane. • Select the StudentID summary field from the SummarizedFields pane and choose the Count operation from the drop-down list below. Click Next. Counting the Number of Students

Using the Group Sorting Feature • On the Group Sorting page, accept the default None option for the Class group and click Next. Group Sorting Options

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

Using the Chart Option (cont.) • 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

Filtering the Report • On the Record Selection page, move the tblStudent.DeptID field from the Available Fields pane to the Filter Fields pane. • Select the DeptID field in the FilterFields pane, and choose the “isequalto” 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

Selecting the Report Style • 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

Selecting the Report Style (cont.) • 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 Report’s Design Environment

Adding a CrystalReportViewer • 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

Binding the Report and Running • Double-click on the Form1 to open the Code Window. Replace Form1’s Form_Load event with the code below. Press Ctrl + F5 to run the application. Getting Data Binding Report Crystal Report Binding Code

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

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:

Formatting Charts and Fields (cont.) • How-to: Format Charts and Fields • Open the StudentReport.rpt file from the Solution Explorer to view the report’s 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

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

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

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.

Creating the Report (cont.) • 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. • On the Fields page, select and add the FacultyID, DeptID, and Salary fields to the FieldstoDisplay pane. Click Next. • On the Grouping page, select and add the DeptID field to the GroupBy pane. Click Next. Choosing Tables on the Data Page

Creating the Report (cont.) • On the Summaries page, remove the Sum of tblFaculty.FacultyID field from the SummarizedFields 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

Creating the Report (cont.) • On the GroupSorting page, select the None ordering option and click Next. • On the Chart page, choose the PieChart 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

Creating the Report (cont.) • In the FacultyReport.rpt’s design view, remove all the fields in the various report sections except for the chart object in the report header. • 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. • Navigate to the Data Labels tab and configure it as shown below. Configuring Data Labels on the Pie Chart

Binding the Report • Add a CrystalReportViewer control from the ToolBox on Form2. • 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

Binding the Report (cont.) • Drag and drop the tblFaculty data table from the Data Sources Window on Form2. • Remove the auto-created DataGridView control and the ToolStrip control from Form2. • Replace the form’s Form_Load event with the code shown below. Component Tray Binding Code

Binding the Report (cont.) • Set the Form2 as the start-up form and run the application. Running Application: Average Faculty Salaries on a Pie Chart

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

Setting up the Data Table • How-to: Create a Crystal Report Based on Multiple Tables • Open the DatasetDesigner. 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 Chart’s Base Query

Setting up the Data Table (cont.) • 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

Creating the Report • Add Form3 to the CrystalReports application. • Add an AverageGrades.rpt report to the application. • Accept the default selections in the Crystal Reports Gallery dialog box. • 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

Creating the Report (cont.) • On the Fields page, select and add the Year and Grade fields to the FieldstoDisplay pane. Click Next. • On the Grouping page, add the Year field to the GroupBy pane. Click Next. • On the Summaries page, remove any additional fields from the SummarizedFields pane. For the tblSection.Grade field, choose the Average summary option. Click Next. Choosing Average Function for the Grade Field

Creating the Report (cont.) • On the GroupSorting page, accept the None ordering option and click Next. • On the Chart page, choose the LineChart option. Change the ChartTitle box as shown. Accept default values for the “Onchangeof” and “Showsummary” drop-down lists. Click Finish. Setting up the Line Chart

Creating the Report (cont.) • In the report’s design view, remove all the fields in the various report sections except the chart object in the report header. • 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 ChartOption dialog box. Check the UseDepth option and click OK. • Now choose Chart Options | Titles from the same short-cut menu, and change the Y-axis title to AverageGrade. Click OK to accept changes.

Creating the Report (cont.) • Choose the Chart Options | Grid option, and navigate to the Scales tab in the NumericAxisGrids&Scales dialog box. Set up the scales as shown. • In the same dialog box, navigate to the Grids tab and check the UseManualGrid option. Set the interval value to 0.5. Adjusting Y-axis Scales

Binding the Report • Open Form3 in the Design Window, and add a CrystalReportViewer control. Change the control’s Dock property from Fill to None. Adjust its length and width. Also use the Property Window to set the DisplayGroupTree property to False. • Drag and drop the newly created tblGrades data table from the Data Sources Window on Form3. • Remove the auto-created DataGridView control and the ToolStrip control. TableAdapter and Dataset Components for Chart Data

Web-Enabled Decision Support Systems