Thursday, 5 April 2012

Crystal reports in ASP.NET

In this example i m going to describe how to create crystal reports in ASP.NET.

In this i am generating 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 reportit 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">

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();
            ("abhinav", "password", @"Coderskey\SQLEXPRESS", "NewDB");
        CrystalReportViewer1.ReportSource = crystalReport;

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

Dim crystalReport As New ReportDocument()


("abhinav", "password", "Coderskey\SQLEXPRESS", "NewDB")

CrystalReportViewer1.ReportSource = crystalReport

End Sub

Hope this helps 


Post a Comment

Design by Abhinav Ranjan Sinha