Programming crystal report with ASP.Net 2.0 (VB) , Oracle

[dhilipkumar]

Introduction

The source code in this article has been developed using Microsoft Visual Studio 2005 Professional Edition on Microsoft Windows 2003 Server with Oracle 11i. I used the same version of Crystal Reports which comes with Visual Studio 2005 Professional Edition. But there is no problem for design the reports with Business Objects Crystal Reports 11.

Creating an ASP.Net 2.0 Web Site Project

Create a new Website

ASP.Net Website
File System
Language VBScript
Select path and give a name to your project (Sample web). Now we have to mention the connection string. Open the Web.Config file from your SolutionExplorer window. And find <ConnectionStrings\> under <Configuration> change it by adding your connection string.

<ConnectionStrings>
    <add name=""[Your="" Connection="" string="" name=""] = "Provider=MSDAORA; Data Source=[your Service name];Persist Security Info=True; Password=[your password];User ID=[your user id]" Provider="" Name="System.Data.OleDb"/>    </connectionStrings>

The Front End Design:

For the front end I used Macromedia Dream Weaver. After that I just copy the HTML code and paste into our website source. This is not a big issue. You can design as you like. But I found VS2005 is not good for a complex front end design.

Creating a Data Set:

Right click on the Sample Web (our website name) available in then Solution Explorer and select Add New Item.

Select Data Set and give a name to the new Data Set. Here I did not use any name, I just let it go with its default name DataSet1.

Click on Add.

Here a message will prompt for adding Application Code to your Project.

Click on Yes.

A new window will arrive; and select the connection string as we entered in web.config. Here "ConnectionStringTest" [Your Connection string name].

Click on Next

You will get a new window. Click on Advanced Options and check both check boxes.

Click Ok.

Now click on Query Builder button and select the Table you need. Here I selected

Daily Balances.

Check the field that we need Execute Query. Now you will get something like following.

Click OK then click Next and then Finish.

Now you can see one DataSet1.xsd came under the Application Code.

By this we created one data set. And if you need you can add more tables into your DataSet1.xsd, by Adding Table Adapter.
Open the DataSet1.xsd, Right click on any where n the body, Add -> Table Adapter.

Adding Crystal Reports to the Website:

Right click on the Sample Web (our website name) available in the Solution Explorer and select Add New Item. Now select Crystal Reports, name it as Daily.rpt

New window will appear:

Select using Report wizard
Standard
Click on OK.

Another window will arrive for specifying data source to you report. Here under the Project Data Source expand the ADO.Net Data Set.
Select the Table.

Click on the next button and then finish.

A report Designer will appear. Design the report. Save.
Here I am using one parameter. For creating one parameter object in the Report,
In the Field Explorer box, Right click on Parameter Fields -> New
I gave a name Dtes type as String

Click OK

Save the Report.

[dhilipkumar]

Adding a Crystal Report Viewer in our website:

Open the Default.Aspx [or your form] in Design viewInsert the following controls,

One Label: Change its Text Property to Enter Date
One Text box: Change its Text Property to Seven Button controls:


ID    Text    Usage
1    Btn_Print   Print Report    For taking printouts. For this I just export our report to PDF and take a printout from there.
2    Btn_toXL   Export to Excel    For exporting to MS Excel.
3    Btn_Prvw   Preview    For Viewing the report in the same page. For this we need four more buttons to navigate through the report.
4    Btn_FIrst    |<   For showing the first page of the Report
5    Btn_Prv   <    For showing the previous page of the Report
6    Btn_next   >    For showing the next page of the Report
7    Btn_FIrst    >|   For showing the last page of the Report

One Crystal Reports Viewer control from the Smart Task Window uncheck all the items excepts Enable Report View.

Code Select Expand

Coding:
Imports CrystalDecisions.Shared
Imports CrystalDecisions.CrystalReports.Engine
Imports System.Configuration
Imports System.Web
Imports System.Data.OleDb

[b]'First on Preview Button click.[/b]

Dim thisConnectionString As String = ConfigurationManager.ConnectionStrings("ConnectionStringTest").ConnectionString
Dim myConn As OleDbConnection = New OleDbConnection(thisConnectionString)
Dim MyCommand As New OleDbCommand()
MyCommand.Connection = myConn
MyCommand.CommandText = "Select CLIENTID,upper(CLIENTNAME)as CLIENTNAME,BALAMT,BALDATE From ALPHA_CLIENT_DAILYBALANCES WHERE (to_char(BALDATE,'dd/mm/yyyy'))=" & TextBox1.Text & ""
MyCommand.CommandType = Data.CommandType.Text
Dim myDa As New OleDbDataAdapter
myDa.SelectCommand = MyCommand
Dim myDS As New DataSet1()
myDa.Fill(myDS, "DAILYBALANCES")
Dim Rpt As New ReportDocument
Rpt.Load(Server.MapPath("Daily.rpt"))
setting Parameter to report
Dim myparams As New ParameterFields
Dim myparam As New ParameterField
Dim mydiscvalue As New ParameterDiscreteValue
myparam.ParameterFieldName = "Dtes" Dtes, the parameter name that we created in the report
mydiscvalue.Value = TextBox1.Text
myparam.CurrentValues.Add(mydiscvalue)
myparams.Add(myparam)
CrystalReportViewer1.ParameterFieldInfo = myparams
Rpt.SetDataSource(myDS)
Rpt.PrintOptions.PaperOrientation = PaperOrientation.Portrait
Rpt.PrintOptions.PaperSize = PaperSize.PaperA4
Me.CrystalReportViewer1.ReportSource = Rpt

Navigation

Btn_first_Click
      PrintDailyBalance() I made one procedure viz PrintDailyBalance, in that I made the above code
      Me.CrystalReportViewer1.ShowFirstPage()Now we finished coding of four buttons. When we click on the preview button the report will display on the same page.
Btn_prev_Click
      PrintDailyBalance()
      Me.CrystalReportViewer1.ShowPreviousPage()
Btn_next_Click
      PrintDailyBalance()
      Me.CrystalReportViewer1.ShownextPage()
Btn_last_ClickA
      PrintDailyBalance()
      Me.CrystalReportViewer1.ShowLastPage()
Print Report

Before we go into these Print or Export button; right click on our Sample Web available in the Solution Explore -> Select New Folder.

Name that folder to Export.

PrintDailyBalance()
Rpt.SetParameterValue("Dtes", TextBox1.Text) Setting up the parameter
Rpt.ExportToDisk(ExportFormatType.PortableDocFormat,
Server.MapPath("~/Export/Daily.pdf")

This will export our report into daily.pdf in the Export folder.

Response.Redirect("~/Export/ Daily.pdf ")

The above code s used to display the report at the same time when the user clicks on Print.

If you want to print directly you can use the following code

PrintDailyBalance()
Rpt.PrintOptions.PaperOrientation = PaperOrientation.Landscape
Rpt.PrintOptions.PaperSize = PaperSize.PaperA4
Rpt.PrintToPrinter(1, False, 0, 0)

It will print all pages , you can change last two vaue for start page and end page. If you vant to print only 1 page just give 1,1 instaed 0,0

But this can not show any printer dialog. Thats why I used with pdf.

Export to Excel

PrintDailyBalance()
Rpt.SetParameterValue("Dtes", TextBox1.Text)
Rpt.ExportToDisk(ExportFormatType.Excel, Server.MapPath("~/Export/Daily.xls")
Response.Redirect("~/Export/ Daily.xls")

Now you can test your website.
Right click on Default.aspx -> Select View n Browser
Give the date in the Text box like dd/mm/yyyy -> click on Preview / Print Report / Export to Excel Button.


Publishing our website using web set up programme.
Copy CrystalReportsRedist2005_x86.msm to the \Program Files\Common Files\Merge Modules folder.
You can download this msm file from www.businesobjects.com. And download cr_net_2005_mergemodules_mlb_x86.zip.

Start Visual Studio.

In the File menu, select New -> Project.
In the New Project dialog box, select a Web Setup Project or a Web Setup Project.
In the Solution Explorer, select your setup project, right-click, and select Add -> Merge Module from the pop-up menus:

Add CrystalReportsRedist2005_x86.msm to your project:

Note that Microsoft_VC80_ATL_x86.msm and policy_8_0_Microsoft_VC80_ATL_x86.msm will be automatically included when you add CrystalReportsRedist2005_x86.msm to your project:

Next we have to copy our website components into this setup file.

Open the folder, D:\Abie\TestingReports\SampleWeb (externally)

Copy all the files and Come back to new set up programme -> click on the Web Application Folder now in the centre of the screen you can see one Bin folder.  Just paste our file into this Web Application Folder.

A message will prompt select Yes or No

Go to -> Build -> Build Web Setup.

Now go to the directory where the web setup is file is created.

Find debug folder. In that you can see one websetup1.msi file and setup.exe. If you want to install just run the msi file.

[dhilipkumar]

The libraries must be included in namespace. The crystalDecisions.CrystalReports.Engine namespace provides support for the report engine.

using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
In DataSet object give the names of the table defined in the XSD File. I am using only a single table but there can be multiple tables in schema so please don't forget to define the table name in dataset while populating DataSet for respective table.

dsSource = new DataSet("TableA");
Populate the dataset with the xml source file. Reads XML schema and data into the System.Data.DataSet using the specified file.

dsSource.ReadXml(strSourceFile);

The report document object represents a report and contains properties and methods to define, format, load, export, and print the report e.g,

reportDoc = new ReportDocument();
reportDoc.Load(strFileName);

After finalizing above steps it time to pass it to the crystal report viewer object. The CrystalReportViewer control allows a Crystal Report to be viewed in an application. The ReportSource property is used to set which report is to be viewed. Once this property is set, the report will be shown in the viewer. The source of the report can either be a ReportDocument, a path to the report file, or a strongly typed report.
reportDoc.SetDataSource(dsSource);
frmShowReport.crViewer.ReportSource = reportDoc;
frmShowReport.ShowDialog();