Programming Crystal Reports with ASP.NET 2.0

This is the first article in a series focusing on programming with Crystal Reports with ASP.NET 2.0. In this article, we will focus on adding a Crystal Report to an ASP.NET 2.0 web site and displaying the same using the CrystalReportViewer control in a number of different ways.


A downloadable zip file is available for this article.

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:

http://www.aspfree.com/c/a/ASP.NET/Designing-and-Developing-Reports-in-
ASPNET-20/

http://www.aspfree.com/c/a/ASP.NET/Binding-Data-to-the-ReportViewer-Control-
Dynamically-in-ASPNET-20/

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.

{mospagebreak title=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.

{mospagebreak title=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."

{mospagebreak title=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

{mospagebreak title=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

{mospagebreak title=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 at http://jagchat.spaces.live.com

26 thoughts on “Programming Crystal Reports with ASP.NET 2.0

  1. Hello guys,

    This is first in the series focusing on “Progamming with Crystal Reports with ASP.NET 2.0″. Your feedback on this is well appreciated here.

    thanks
    Jag

  2. Hi,

    This is first in series focusing on “Programming Crystal Reports with ASP.NET 2.0″. It simply covers the basics. The upcoming articles would go into the depth of programming.

    Any feedback is well appreciated here.

    thanks
    Jag

  3. Hi
    this is 1 day in new project, I dn’t know the ABC of Crystal report, I read your artical and now I am confident I will develop my reports as per requirement here.

    Great Thanks

    Davinder

  4. I would like information on how to set the web server up to run the crystal reports project live. I have not been able to find a definitive guide. Thanks for this.

    John

  5. This article is very fine to activate CR in website.
    If u give details about CR it’s helped us
    it means how to use diff tools available in CRV. and how to make use of those controls…

  6. This is one of the best tutorials on setting up Crystal Reports within a .Net and Web Environment. Brilliant.

    I’ve been able to get my site up and running. Thanks.

    Any channce of an Update for VS 2008, and CRXI (reports and report server).

    TJ

  7. hi i m ajit.. working on crystalln reports . i put the data in crystal report through the dataset table… when i debug the code the data is displayed .. while making any change ,every time its askingthe server user name(sa) and password(sa)…… so how i can avoid it?

  8. I have found this article very useful for me as beginner :) ,,,,,,,,,, hope u’ll be keep doing this kind of work in future :) )

  9. Hi Jag,

    Thank you very much for this article. After several hours of searching for a good walkthrough i found yours. It was the best of all of them. The article explains things very well. It is clear and the sample code is very helpful.

    Thank You!

    Kristian

  10. Hi sir,
    Thanks for posting, but while i run the application page6(i.e dynamically add a report source to the CrystalReportViewer and bind it at runtime ) is showing 3 errors like

    Error 1 ‘Table’ is an ambiguous reference between ‘System.Web.UI.WebControls.Table’ and ‘CrystalDecisions.CrystalReports.Engine.Table’

    Error 2 ‘System.Web.UI.WebControls.Table’ does not contain a definition for ‘LogOnInfo’

    Error 3 ‘System.Web.UI.WebControls.Table’ does not contain a definition for ‘ApplyLogOnInfo’

    Please send the correct code to my mail id : satyadnet@gmail.com

  11. Hi Jagan,thanks a ton for the information provided..do you any have clue on how we can generate the report dynamically..
    I want to give an option to the user to select the column names also to generate a report (kind of custom report generation). Is that feasible in Crystal reports???

    Thanks in advance
    Praveen.

  12. Hi Sir
    Thank you for posting such an Imporatant and Useful Tutorial on Crystal Report. This Prooved Really useful. Thanks Again

  13. we set “EnableParameterPrompt=”False”". I apprecite if you can explain how pass parameters progarmatically?

    looking froward to your reply.
    - Sam

  14. First thanks for sharing.

    I want choose a crystal report from a dropdownlist but i tried to add a command for the connection like that:

    select bname from ” & Me.DropDownList1.SelectedItem.Text & “

    but it’s wrong because I can’t use invalid character.
    So how can I solve this issue

    Thanks And regard
    Mohamed

  15. how to assign dynamic datatable to crystal report in window application??
    with the following code data should not bind with crystal report….
    Dim myDS As New DataSet
    myDS.Tables.Add(MyTable1)
    Dim myReportDocument As New CrystalDecisions.CrystalReports.Engine.ReportDocument
    myReportDocument.Load(“D:\leena\Reports_window\Repoting Service\Repoting Service\Reports\crpabsence.rpt”, CrystalDecisions.Shared.OpenReportMethod.OpenReportByDefault)
    myReportDocument.SetDataSource(myDS)
    CrystalReportViewer1.ReportSource = myReportDocument
    CrystalReportViewer1.Show()

  16. Man I have been searching the web for articles on Crystal Report and believe me yours it the most comprehensive and simple one straight to the point and not to much of the technical stuff. KEEP UP the good work.

  17. Great great stuff..I searched the web for a time out issue I was having in asp.net which was not passing parameter values after about 90 seconds – that method above BindReport() solved my problem. I removed the toolbar and used the buttons as you demonstrated above.

    Thanks for sharing your knowledge with us.
    AB

[gp-comments width="770" linklove="off" ]