The entire solution (source code) for this article is available as a free download (in the form of a zip). The source code in this article has been developed using Microsoft Visual Studio 2005 Professional Edition on Microsoft Windows Vista Ultimate Edition with Microsoft SQL Server 2005 Express Edition. I used the same version of Crystal Reports which comes with Visual Studio 2005 Professional Edition. I didn't really test any of the code in any other tools/IDEs/servers/editions/versions. If you have any problems, please feel free to post in the discussion area.
I contributed two articles focusing on the capabilities of ASP.NET 2.0's built-in reporting. If you are new to the built-in reporting (also called local reporting), you can find the articles here:
Starting an ASP.NET 2.0 Web Site Project
The following are the steps necessary to create a new ASP.NET 2.0 web site:
- Open Microsoft Visual Studio 2005 Professional Edition
- Open File || New || Web Site
- Select "ASP.NET Web Site" in templates, select "File System" as the location, provide the website name "SampleWebSite01" and finally click on OK.
- Right click on "SampleWebSite01" available in Solution Explorer and select "Add New Item."
The next section walks you through adding a Crystal report to the project.
Adding a Crystal Report to the Project
In the previous section, a new ASP.NET web site was created. In this section, we shall walk through the creation of a Crystal report. The following are the steps you need to take; they pick up from where we left off in the previous section:
In the "Add New Item" dialog box, select "Crystal Report" as the template, provide the name "SampleRpt01.rpt" and click on the "Add" button.
You should see "SampleRpt01.rpt" added to the "Solution Explorer" and the "Crystal Reports Gallery" dialog will be shown (Fig 05). Select "Standard" in "Choose an Expert" and click OK (Fig 05).
Next, you will be taken to provide details for the Data Source. In the "Available Data Sources," open "Create New Connection" and open "OLE DB (ADO)." It brings up another dialog box to let you select the OLEDB Provider. Select "Microsoft OLEDB Provider for SQL Server" in the list of Providers and click on "Next" as shown below.
Provide the db connection information to connect to the Northwind database and click on "Finish."
A new connection gets added to the "OLEDB (ADO)" group. Open the added connection and further drill down to Northwind || dbo || Tables, add the "Orders" table to the "Selected Tables" list (by selecting it and clicking on the greater than arrow), and click on "Next" as shown below.
In the next screen of the wizard, you will be provided with all the fields (or columns) to include in the report. Add OrderID, CustomerID, EmployeeID and OrderDate to the "Fields to Display" section by double clicking each of those fields in the "Available Fields" list and click on "Finish" as shown below.
Crystal Report Components
In the previous section, we added a new Crystal Report to the web site. Once the report gets added, you should be able to see the Visual Studio layout with "Field Explorer," the new "Crystal Reports" menu and an "Embedded Crystal Report Designer" with two modes ("Main Report" and "Main Report Preview" at the bottom) as shown below.
The "Field Explorer" can be used to drag and drop the columns onto the report, add new, add new tables to the report, add new formula oriented columns, running totals, and so on.
The report designer is initially divided into five sections (Report Header, Report Footer, Page Header, Page Footer and Details). Report Header gets displayed only on the first page. Report Footer gets displayed only on the last page. Page Header gets displayed on top of every page. Page Footer gets displayed at the bottom of every page. The Detail section is the body of the report where all of the rows get displayed.
Using the formatting toolbar, properties windows and tools in toolbox, we can format the report in the required manner. Try playing with formatting the report (and preview it simultaneously without executing the project).
Displaying Crystal Report on the ASP.NET 2.0 web page
To display the report on the web page, we need to work with Crystal Report Viewer control. The following are the steps required to attach the Crystal Report to the ASP.NET 2.0 web page:
Using the ToolBox, drag and drop the CrystalReportViewer control.
Using the Smart tag of the CrystalReportViewer control, select "New Report Source."
This leads you to a new dialog box, "Crystal Report Source." Every CrystalReportViewer gets associated with the CrystalReportSource control. You can directly drag and drop the CrystalReportSource control from the toolbox as well and assign the same in a smart tag. Provide the details and click OK.
The moment you hit OK, you should be previewing the report. Just hit F5 to execute your web site. Once the web page gets displayed, provide logon information for the report and click on "Logon."
Providing Login information for the CrystalReportViewer control dynamically
The CrystalReportViewer control needs to be provided with database logon information to execute and show the report. At the same time, it is a bit awkward to have the database login page displayed for every Crystal Report. No end user likes it (and of course, it is dangerous too!).
To solve this problem, we can provide database logon information to the CrystalReportViewer control dynamically at runtime. The following is the code to achieve the same:
Imports CrystalDecisions.Shared
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles form1.Load
Dim ConnInfo As New ConnectionInfo
With ConnInfo
.ServerName = ".sqlexpress"
.DatabaseName = "Northwind"
.UserID = "sa"
.Password = "eXpress2005"
End With
For Each cnInfo As TableLogOnInfo In Me.CrystalReportViewer1.LogOnInfo
cnInfo.ConnectionInfo = ConnInfo
Next
End Sub
End Class
You must observe that the CrystalDecisions.Shared namespace is added to the top. In the above code, the database connection information is stored in ConnInfo and is defined as follows:
Dim ConnInfo As New ConnectionInfo
With ConnInfo
.ServerName = ".sqlexpress"
.DatabaseName = "Northwind"
.UserID = "sa"
.Password = "eXpress2005"
End With
The above connection information is assigned to CrystalReportViewer control using the following code:
For Each cnInfo As TableLogOnInfo In Me.CrystalReportViewer1.LogOnInfo
cnInfo.ConnectionInfo = ConnInfo
Next
Hiding the toolbar and adding First, Last, Next and Previous page buttons to the report
By default, CrystalReportViewer automatically displays toolbar at the top. Not every end user uses all features. At the same time, some end users may not like the toolbar.
To hide the toolbar, modify the CrystalReportViewer code (in Source mode) as follows:
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="True"ReuseParameterValuesOnRefresh="True" DisplayToolbar="False" EnableDatabaseLogonPrompt="False"EnableParameterPrompt="False" DisplayGroupTree="False"
Height="1064px" ReportSourceID="CrystalReportSource1" Width="928px" />
Add First, Last, Next and Previous page buttons to the web page and modify the code behind with new events as follows:
Protected Sub btnFirst_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnFirst.Click
Me.CrystalReportViewer1.ShowFirstPage()
End Sub
Protected Sub btnLast_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnLast.Click
Me.CrystalReportViewer1.ShowLastPage()
End Sub
Protected Sub btnNext_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnNext.Click
Me.CrystalReportViewer1.ShowNextPage()
End Sub
Protected Sub btnPrevious_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
Me.CrystalReportViewer1.ShowPreviousPage()
End Sub
Enhancing the report with run-time binding along with session handling
In all of the above sections, the CrystalReportViewer worked with CrystalReportSource. Now, let us dynamically add a report source to the CrystalReportViewer and bind it at runtime. This gives us the flexibility to use the same viewer for different reports (showing one at a time).
Add a new web page to the project, add the four buttons (First, Previous, Next and Last) and a CrystalReportViewer control. Modify the CrystalReportViewer control, so that it looks like the following:
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="True"ReuseParameterValuesOnRefresh="True" DisplayToolbar="False" EnableDatabaseLogonPrompt="False"EnableParameterPrompt="False" DisplayGroupTree="False"
Height="1064px" Width="928px" />
In the code-behind, add the following at the top:
Imports CrystalDecisions.Shared
Imports CrystalDecisions.CrystalReports.Engine
Add a new method as follows:
Private Sub BindReport()
If Session("Rep") Is Nothing Then
Dim ConnInfo As New ConnectionInfo
With ConnInfo
.ServerName = ".sqlexpress"
.DatabaseName = "Northwind"
.UserID = "sa"
.Password = "eXpress2005"
End With
Dim rep As New ReportDocument
rep.Load(Server.MapPath("SampleRpt01.rpt"))
Me.CrystalReportViewer1.ReportSource = rep
Dim RepTbls As Tables = rep.Database.Tables
For Each RepTbl As Table In RepTbls
Dim RepTblLogonInfo As TableLogOnInfo = RepTbl.LogOnInfo
RepTblLogonInfo.ConnectionInfo = ConnInfo
RepTbl.ApplyLogOnInfo(RepTblLogonInfo)
Next
Session("Rep") = rep
End If
Me.CrystalReportViewer1.ReportSource = Session("Rep")
Me.CrystalReportViewer1.DataBind()
End Sub
Add the following code to bind the report for every button click and also during the page load event:
Protected Sub btnFirst_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnFirst.Click
BindReport()
Me.CrystalReportViewer1.ShowFirstPage()
End Sub
Protected Sub btnLast_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnLast.Click
BindReport()
Me.CrystalReportViewer1.ShowLastPage()
End Sub
Protected Sub btnNext_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnNext.Click
BindReport()
Me.CrystalReportViewer1.ShowNextPage()
End Sub
Protected Sub btnPrev_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnPrev.Click
BindReport()
Me.CrystalReportViewer1.ShowPreviousPage()
End Sub
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not IsPostBack Then
BindReport()
End If
End Sub
I hope you enjoyed the article and any suggestions, bugs, errors, enhancements etc. are highly appreciated athttp://jagchat.spaces.live.com
Tidak ada komentar:
Posting Komentar