CRYSTAL REPORT USING DATABASE TABLES AS DATASOURCE

Crystal reports can be created in .Net applications with different data sources including using database tables, stored procedures, typed datasets and also without a data source.

In this article we are going through how to create a crystal report for the first time using database tables as the datasource.

1. You have to create your windows project in using VS 2005. After creating the project, right on then project name in the solution explorer, select “add” and then “add new” as shown below.

image

2. In the add new dialog box, select “crystal Report” and name it, and then click on the add button.

image

3. You will be presented with the “crystal report gallery” dialog, choose “using the report wizard” and click on ok

image 

4.In the choose data source dialog, go to the “ Create New Connection” node and then select the “OLE DB(ADO)” sub node.

image

4. You will then be required to provide the database connection information. Slect the “Microsoft OLE DB provider for SQL server” and click on the next button

image

5. Here you provide the database logon parameters

image

6. After successfully connecting to the database, you then select the table (s) you want to display its content on your report

image

6. After selecting the table(s) you select the actual columns you want to displayimage

7. After choosing the columns you will display, you can click on next to choose the columns for grouping and then for filtering, and then finally you choose the format to display your report.

image

8. After you have finished designing your report, you then drag a “crystal reports viewer” onto your form.

image

Then create a code under the form load event to show your report on for load

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ‘create a new report document 
        ‘and load the report from its source
        ‘use Application.StartupPath to get the address of your report fro deployment issues
        Dim rpd As New ReportDocument
        rpd.Load("C:\Gilbert\Personal\Projects\BlogPosts\BlogPosts\tableDS.rpt")
        Me.CrystalReportViewer1.ReportSource = rpd
        SetDBLogonForReport(rpd)

    End Sub

    ‘for deployment issues make sure the database paramaters are encrypted
    ‘and saved in the mysettings class

Private Sub SetDBLogonForReport(ByVal myReportDocument As ReportDocument)
       Dim myConnectionInfo As ConnectionInfo = New   ConnectionInfo()
       myConnectionInfo.DatabaseName = "datalinkDB"
       myConnectionInfo.UserID = "sa"
       myConnectionInfo.Password = "qwerty"
       myConnectionInfo.ServerName = "localhost"
       myConnectionInfo.IntegratedSecurity = "false"
       Dim myTables As Tables = myReportDocument.Database.Tables
       For Each myTable As  CrystalDecisions.CrystalReports.Engine.Table In myTables
           Dim myTableLogonInfo As TableLogOnInfo = myTable.LogOnInfo
           myTableLogonInfo.ConnectionInfo = myConnectionInfo
           myTable.ApplyLogOnInfo(myTableLogonInfo)
       Next
   End Sub

Below is a picture when the application is run

image

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s