Chapter 1

Getting Started with VB .NET and Crystal Reports

Debbie St. GeorgeWonder Ranch Publishing DBA/Programmer

Covington Visual Studio.Net User Group

Slide 2

Objectives• Create a simple Crystal Report in Visual

Studio.NET• Display a Crystal Report in a Windows

Forms Application• Display a crystal Report in a Web Forms

Application• Publish a Crystal Report as an XML

Report Web Service• Deploy Crystal Reports to the Web

Slide 3

Crystal Report Advantages Some of the major advantages of using Crystal

Report for Visual Studio .NET are : - Rapid report development - Can extend it to complicated reports with

interactive charts - Exposes a report object model using which it

can interact with other controls on the web form

- Can programmatically export the reports into widely used formats like .pdf, .doc, .xls, .html and .rtf

Slide 4

Information Delivery Infrastructure is the Key

Slide 5

Based on Standards

Supports Existing Data Sources

Accessible by All Users

Delivered the Way User’s Want to Use Data

Architecture in Detail…

Slide 6

Crystal Reports Walkthrough - using the Pull Model Add a New Project to the Solution.Project… Add Item… Crystal Decisions Window App

Slide 7

Add a Crystal Report:Go to Project.. Add New Item..Web Project Items..Crystal Report Keep the CrystalReport1.rpt as the default name for this report. Click Open and you will get the Crystal Report Gallery Dialog.

Slide 8

Select standard report

Slide 9

Select OLE DB (ADO) Microsoft Jet 4.0 OLE DB Provider

Slide 10

Adding data and fields

Slide 11

Group and Total

Slide 12

Top N and Chart Data

Slide 13

Standard Report Design

Slide 14

Crystal Report

Slide 15

Add Crystal Report Viewer to Form1.vb

Slide 16

Right-click on Form1 and View code. type Text in yellow areas.

Slide 17

Build and then run the report

Slide 18

Add Project.. new project…ASP.net web application

Slide 19

Right-click and set form as startup form. Project as startup project.

Slide 20

From an existing report

Slide 21

Add previously created CrystalReport1

Slide 22

Add code behind webformBuild..Build SolutionDebug.. Start without debugging

Slide 23

Run from the web.

Slide 24

As an Asp.Net Web Service

Slide 25

Add crystalreport1

Slide 26

Testing CrystalReport1Service

Slide 27

Right-click on Crystal Report and

set as Startup

Slide 28

Right-Click on Form1.vb and Select View Code

Slide 29

Change ReportSource to our Web Service .asmx

Slide 30

Web Form hosted in Windows Environment

Slide 31

Add CR Deployment Project

Crystal Reports Deployment Steps:

* Create a Web Setup Package

* Defining required runtime components

Crystal Reports Deployment Steps:

* Create a Web Setup Package

* Defining required runtime components

Slide 32

Web Setup Project(Note: Setup is the same for a Win Project)

Slide 33

From WebSetup (add Merge Modules)

Slide 34

Report files (.RPT) need to be distributed when a .NET application is deployed.

Merge Modules

CR for VS .NET provides merge modules in setup projects to make deployment

a simple task. The merge modules are installed to the following folder, by

default:

C:\Program Files\Common Files\Merge Modules\

There are four merge modules that need to be included in a setup project to

deploy reports.

•Managed.MSM

•Database_Access.MSM

•Database_Access_enu.MSM

•REGWIZ.MSM

Report files (.RPT) need to be distributed when a .NET application is deployed.

Merge Modules

CR for VS .NET provides merge modules in setup projects to make deployment

a simple task. The merge modules are installed to the following folder, by

default:

C:\Program Files\Common Files\Merge Modules\

There are four merge modules that need to be included in a setup project to

deploy reports.

•Managed.MSM

•Database_Access.MSM

•Database_Access_enu.MSM

•REGWIZ.MSM

Deploy Crystal Reports

Slide 35

RegWiz.msm Needs Lic Key

Slide 36

Registering Crystal Report ProductHttp://www.crystaldecisions.com/register.Content.asp

Slide 37

Resources

Crystal Decisions Websitehttp://www.crystaldecisions.com/products/dev_zone/default.asp

Free ASP.NET Web Matrix http://www.asp.net/webmatrix/default.aspx?tabindex=4&tabid=46

The XML for ASP.NET Developers Website Video Tutorialshttp://www.xmlforasp.net/

http://www.aspfree.com/authors/jitajay/crystalreport.aspx

Slide 38

Passing a Stored Procedure to a Report

NOTE: This sample Windows application uses the Windows Forms viewer. The sample has one form (Form1) that contains the viewer. The report code is run from the form's constructor (Public Sub New( )). ==================== Imports CrystalDecisions.CrystalReports.Engine Imports CrystalDecisions.Shared

Public Class Form1 Inherits System.Windows.Forms.Form

'Crystal Reports Variables Dim oRpt As New CRStoredParam() Dim crTables As Tables Dim crTable As Table Dim crTableLogOnInfo As TableLogOnInfo Dim crConnectionInfo As New ConnectionInfo() Dim crParameterValues As ParameterValues Dim crParameterDiscreteValue As ParameterDiscreteValue Dim crParameterFieldDefinitions As ParameterFieldDefinitions Dim crParameterFieldDefinition As ParameterFieldDefinition

NOTE: This sample Windows application uses the Windows Forms viewer. The sample has one form (Form1) that contains the viewer. The report code is run from the form's constructor (Public Sub New( )). ==================== Imports CrystalDecisions.CrystalReports.Engine Imports CrystalDecisions.Shared

Public Class Form1 Inherits System.Windows.Forms.Form

'Crystal Reports Variables Dim oRpt As New CRStoredParam() Dim crTables As Tables Dim crTable As Table Dim crTableLogOnInfo As TableLogOnInfo Dim crConnectionInfo As New ConnectionInfo() Dim crParameterValues As ParameterValues Dim crParameterDiscreteValue As ParameterDiscreteValue Dim crParameterFieldDefinitions As ParameterFieldDefinitions Dim crParameterFieldDefinition As ParameterFieldDefinition

Slide 39

#Region " Windows Form Designer generated code “ Public Sub New() MyBase.New() 'This call is required by the Windows Form Designer. InitializeComponent()

'Add any initialization after the InitializeComponent() call ' Set the Connection parameters ' Using an OLEDB connection to a SQL Sever DB (Database: Pubs) With crConnectionInfo .DatabaseName = "DBName“ .ServerName = "ServerNameOrODBCName“ .UserID = "UserID“ .Password = "Password“ End With

crTables = oRpt.Database.Tables 'Set the logon information for each table.

For Each crTable In crTables crTableLogOnInfo = crTable.LogOnInfo crTableLogOnInfo.ConnectionInfo = crConnectionInfo crTable.ApplyLogOnInfo(crTableLogOnInfo) Next

#Region " Windows Form Designer generated code “ Public Sub New() MyBase.New() 'This call is required by the Windows Form Designer. InitializeComponent()

'Add any initialization after the InitializeComponent() call ' Set the Connection parameters ' Using an OLEDB connection to a SQL Sever DB (Database: Pubs) With crConnectionInfo .DatabaseName = "DBName“ .ServerName = "ServerNameOrODBCName“ .UserID = "UserID“ .Password = "Password“ End With

crTables = oRpt.Database.Tables 'Set the logon information for each table.

For Each crTable In crTables crTableLogOnInfo = crTable.LogOnInfo crTableLogOnInfo.ConnectionInfo = crConnectionInfo crTable.ApplyLogOnInfo(crTableLogOnInfo) Next

Passing a Stored Procedure to a Report (cont)

Slide 40

' Pass the Stored Procedure parameter to the report

'Get the collection of parameters from the report crParameterFieldDefinitions = oRpt.DataDefinition.ParameterFields 'Access the specified parameter from the collection crParameterFieldDefinition = rParameterFieldDefinitions.Item("@percentage")

'Get the current values from the parameter field. At this point 'there are zero values set. crParameterValues = crParameterFieldDefinition.CurrentValues

'Set the current values for the numeric parameter field crParameterDiscreteValue = New ParameterDiscreteValue() crParameterDiscreteValue.Value = "50"

'Add the current value for the parameter field crParameterValues.Add(crParameterDiscreteValue)

'The current parameter values must be applied for the parameter field. crParameterFieldDefinition.ApplyCurrentValues(crParameterValues) CrystalReportViewer1.ReportSource = oRpt End Sub

#End Region

End Class

' Pass the Stored Procedure parameter to the report

'Get the collection of parameters from the report crParameterFieldDefinitions = oRpt.DataDefinition.ParameterFields 'Access the specified parameter from the collection crParameterFieldDefinition = rParameterFieldDefinitions.Item("@percentage")

'Get the current values from the parameter field. At this point 'there are zero values set. crParameterValues = crParameterFieldDefinition.CurrentValues

'Set the current values for the numeric parameter field crParameterDiscreteValue = New ParameterDiscreteValue() crParameterDiscreteValue.Value = "50"

'Add the current value for the parameter field crParameterValues.Add(crParameterDiscreteValue)

'The current parameter values must be applied for the parameter field. crParameterFieldDefinition.ApplyCurrentValues(crParameterValues) CrystalReportViewer1.ReportSource = oRpt End Sub

#End Region

End Class

Passing a Stored Procedure to a Report (cont)

Slide 41

CRYSTAL REPORTS WALKTHROUGH –

USING THE PUSH MODEL (Objectives)

1. Create a Dataset during design time. 2. Create the .rpt file (from scratch) and make it point

to the Dataset that we created in the previous step. 3. Place a CrystalReportViewer control on the .aspx

page and set its properties to point to the .rpt file that we created in the previous step.

4. In your code behind page, write the subroutine to make the connections to the database and populate the dataset that we created previously in step one.

5. Call the Databind method from your code behind page.

Slide 42

Creating a Dataset during Design Time to Define the Fields of the

Reports 1. Right click on "Solution Explorer", select "Add" --> select "Add New Item--> Select "DataSet"

Slide 43

2) Drag and drop the "Stores" table (within the PUBS database) from the "SQL Server" Item under "Server Explorer".

Slide 44

3) This should create a definition of the "Stores" table within the DatasetNote:The .xsd file created this way contains only the field definitions without any data in it. It is up to the developer to create the connection to the database, populate the dataset and feed it to the Crystal Report .

Slide 45

Creating the .rpt File : 4) Create the report file using the steps mentioned previously.

The only difference here is that instead of connecting to the Database thru Crystal Report to get to the Table, we would be using our DataSet that we just created.

  5)After creating the .rpt file, right click on the "Details" section

of the Report file, select "Add/Remove Database"  6) In the "Database Expert" window, expand "Project Data"

(instead of "OLE DB" that was selected in the case of the PULL Model), expand "ADO.NET DataSet", "DataSet1", and select the "Stores" table.

 

Slide 46

7) Include the "Stores" table into the "Selected Tables" section by clicking on ">" and then Click "ok"

Slide 47

8) Follow the remaining steps to create the report layout as mentioned previously in the PULL Model to complete the .rpt Report file creation

  Creating a CrystalReportViewer Control9) Follow the steps mentioned previously in the PULL

Model to create a Crystal Report Viewer control and set its properties.

Slide 48

48Code Behind Page Modifications : 10) Call this subroutine in your page load - Sub BindReport() Dim myConnection As New SqlClient.SqlConnection() myConnection.ConnectionString= "server= (local)\NetSDK;database=pubs;Trusted_Connection=yes" Dim MyCommand As New SqlClient.SqlCommand() MyCommand.Connection = myConnection MyCommand.CommandText = "Select * from Stores" MyCommand.CommandType = CommandType.Text Dim MyDA As New SqlClient.SqlDataAdapter() MyDA.SelectCommand = MyCommand   Dim myDS As New Dataset1()     'This is our DataSet created at Design Time         MyDA.Fill(myDS, "Stores")             'You have to use the same name as that of your Dataset that you created during design time    Dim oRpt As New CrystalReport1()          ' This is the Crystal Report file created at Design Time    oRpt.SetDataSource(myDS)          ' Set the SetDataSource property of the Report to the Dataset   CrystalReportViewer1.ReportSource = oRpt          ' Set the Crystal Report Viewer's property to the oRpt Report object that we created   End Sub

Slide 49

Session Summary

Thanks for Attending

Wonder Ranch Publishing

http://www.wonderranchpublishing.net/