[VB.NET] Create Report Using Crystal Report

Description

     this section , i will explain how to create report from Crystal Repot

HowTo
1. Install Crystal Report

2. To include in toolbox, right click on toolbox and select "choose item", click tab "Com Component" and choose Crystal Report Viewer 9 (9 is the version crystal report that i use). it will appear a new icon in toolbox.

3. Make 2 form . the first form is for the input and the second form is for report.

4. Drag crystal viewer object (from toolbox) into form 2.

5. Create rpt file, open crystal report application, FILE>NEW and save it with name myreport.rpt into debug and release folder (the extention is .rpt)

5. Now, passing value from form1(input) to form2(report) there is 2 method, passing using variable or using recordset.

Passing using variable // you can use method form_load() for this example

Dim crtAplication As CRAXDRT.Application

Dim crtReport As CRAXDRT.Report

Dim arrParam(3) As String // this param will be passing to report.

crtAplication = New CRAXDRT.Application

crtReport = New CRAXDRT.Report

crtReport = crtAplication.OpenReport(My.Application.Info.DirectoryPath & "\Report\myreport.rpt")

arrParam(0) = "value 1"

arrParam(1) = "value 2"

arrParam(2) = "value 3"

With crtReport

         .EnableParameterPrompting = False

         .DiscardSavedData()

         For i = 0 To 2 'total parameter = 3 (index started from 0)

                 If arrParam(i) <> "" Then

                        .ParameterFields(i + 1).AddCurrentValue(CStr(arrParam(i)))

                 End If

         Next

 End With

With form2.crystalViewer //crystalViewer is the name of Object in toolbox

                .ReportSource = crtReport

                .DisplayGroupTree = False

                .ViewReport()

End With

frmPrintPreview.crystalViewer.Zoom(100)

me.hide

form2.show

open again your file report (.rpt), on "Field Explore" (Bottom Left) Right Click on Parameter Field and make new 3 paramater, of course the first parameter will be get index = 0. drag the parameter field into design. and then save it. 

now running your form and it will automatic populate the value into the form2. the format will follow the report (.rpt).

Passing using Recordset // you can use method form_load() for this example

Dim crtAplication As CRAXDRT.Application

Dim crtReport As CRAXDRT.Report

 Dim strsql As String

Dim RSORASERVER As ADODB.Recordset

crtAplication = New CRAXDRT.Application

crtReport = New CRAXDRT.Report

RSORASERVER = New ADODB.Recordset

RSORASERVER.CursorLocation = adUseClient

strsql = "select * From xxx"

If RSORASERVER.State <> adStateClosed Then RSORASERVER.Close()

        Try

            RSORASERVER.Open(strsql, ConnectORAServer, adOpenKeyset, adLockOptimistic)

        Catch

            Exit Function

End Try

// to create connection using adodb in VB.NET you can find on google or this blog :).

If RSORASERVER.RecordCount > 0 Then

crtReport = crtAplication.OpenReport(My.Application.Info.DirectoryPath & "\Report\myreport.rpt")

            

            'Buffer(crtReport) get data source from Record Set 

            If RSORASERVER.State = 1 Then

                crtReport.Database.Tables.Item(1).SetPrivateData(3, RSORASERVER)

                crtReport.ReadRecords()

            End If

           With frmPrintPreview.crystalViewer

                .ReportSource = crtReport

                .DisplayGroupTree = False

                .ViewReport()

            End With

            frmPrintPreview.crystalViewer.Zoom(100)

else 

msgbox ("Data Return 0")

end if

now setting file report open file myreport.rpt, because we using database then open menu DATABASE>DATABASE EXPERT , and select New Connection. select the type connection do you want, and fill the data. if connection success you fill find the table on the right side, just click ok to finish.

on "Field Explore" (Bottom Left) you will find database field, if connection success it wil llisting many field in database you just connect. drag that field into design. and then save it. 

now running your form and it will automatic populate the value from database  into the form2. the format will follow the report (.rpt). 

that's all how to create report using crystal report in VB.NET.

this simple code :
Imports System.Threading
Imports ADODB.CursorOptionEnum
Imports ADODB.CursorLocationEnum
Imports ADODB.CommandTypeEnum
Imports ADODB.CursorTypeEnum
Imports ADODB.DataTypeEnum
Imports ADODB.RecordStatusEnum
Imports ADODB.RecordTypeEnum
Imports ADODB.LockTypeEnum
Imports ADODB.ObjectStateEnum

Dim crtAplication As New CRAXDRT.Application      
Dim crtReport As New CRAXDRT.Report      
Dim ConnectORAServer As ADODB.Connection      
Dim RSORASERVER As ADODB.Recordset      
Dim strsql As String
     
ConnectORAServer = New ADODB.Connection      
ConnectORAServer.Open("FILE NAME=" & My.Application.Info.DirectoryPath & "\Gen21Server.udl")      
RSORASERVER = New ADODB.Recordset      
RSORASERVER.CursorLocation = adUseClient      
strsql = "Select * from it_v_QC B where b.PROGRAMNAME LIKE '%" & txtSearchValue.Text & "%'"
     
'Dim ds As New DataSet      
'ds.Tables.Add(datagridQC.DataSource)
     
RSORASERVER.Open(strsql, ConnectORAServer, adOpenKeyset, adLockOptimistic)      
crtReport = crtAplication.OpenReport(My.Application.Info.DirectoryPath & "\Report\beritaAcaraQC.rpt")      
crtReport.EnableParameterPrompting = False      
crtReport.DiscardSavedData()
     
crtReport.Database.Tables(1).SetDataSource(datagridQC.DataSource)      
crtReport.Database.Tables.Item(1).SetPrivateData(3, RSORASERVER)      
crtReport.ReadRecords()
     
frmPrintPreview.crystalViewer.ReportSource = crtReport      
frmPrintPreview.crystalViewer.DisplayGroupTree = False      
frmPrintPreview.crystalViewer.ViewReport()      
frmPrintPreview.crystalViewer.Zoom(100)      
frmPrintPreview.Show()