Monday, June 25, 2012

Crystal Reports In ASP.NET

This example shows how to Create Crystal Reports In ASP.NET 2.0,3.5,4.0 Using C# And VB.NET. I am generating Crystal report by fetching data from two tables and grouping them based on Project Name. Database tables are just for demo purpose you can create your own tables with whatever schema you want

Two tables are as shown below.

Create a new website and right click on solution explorer > add new Item > Select Crystal Report
In the dialog box choose blank report.

Now click on CrystalReports Menu in VS and select DataBase Expert 

In database expert dialog box expend create new connection > OLEDB(ADO) section

Now select SQL Native client and enter you SQL server address , username , password and pick database name from the dropdown. 

In next screen Expend your database objects in left pane and add the tables you want to use in right pane 

Link your tables based on Primary keys (If any)

Click ok to finish the wizard.
Right click on Field Explorer and select Group Name Fields  > Insert Group

In next box select the field you to report to be grouped (in my case it's ProjectsName)

Click on OK to finish
Now design the report , drag and fields from Database fields in field explorer and which you want to show in report and drop them in Section3(Details), and preview the report, it should look like show below.

Go to default.aspx page and drag and drop CrystalReportViewer from the toolbox, click on smart tag and choose new report source.

Choose you report from the dropdown menu and click ok to finish.
Now when you build and run the sample , it asks for the database password everytime

To fix this we need to load the report programmatically and provide username and password from code behind .
Now run the report , it should look like this 

Html markup of default.aspx look like
<form id="form1" runat="server">
  <CR:CrystalReportViewer ID="CrystalReportViewer1" 
                          runat="server" AutoDataBind="True"
                          Width="901px" />
  <CR:CrystalReportSource ID="CrystalReportSource1" 
            <Report FileName="CrystalReport.rpt">

using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

C# code behind

Write this code in the event you find appropriate , i m writing it in Page_Load , you can write this code in click event of button or in pagePreRender event
The code to provide password programmatically.
protected void Page_Load(object sender, EventArgs e)
        ReportDocument crystalReport = new ReportDocument();
            ("amit", "password", @"AMIT\SQLEXPRESS", "TestDB");
        CrystalReportViewer1.ReportSource = crystalReport;

VB.NET code behind
Protected Sub Page_Load
(ByVal sender As Object, ByVal e As EventArgs)

Dim crystalReport As New ReportDocument()


("amit", "password", "AMIT\SQLEXPRESS", "TestDB")

CrystalReportViewer1.ReportSource = crystalReport

End Sub

1 comment:

  1. Crystal Downloads