This tutorial focuses on creating a crystal report in Visual studio 2010. The report uses a stored procedure to get its data from an Sql server 2008 R2 database. In order to create the report you will need to download and install crystal reports for Visual Studio 2010. This is because VS 2010 does not ship with crystal reports. However you can download crystal reports from SAP websitehttp://downloads.businessobjects.com/akdlm/cr4vs2010/CRforVS_13_0.exe
- Start by running the following scripts in sql database to create a test table, insert some records and also create a procedure that will retrieve the records.
--===========================================
--create a test customer table
CREATE TABLE t_Customers
(
CustomerID VARCHAR(20),
FirstName VARCHAR(25),
LastName VARCHAR(25)
)
--===========================================
--insert dummy data into the test table
INSERT INTO t_Customers(CustomerID,FirstName,LastName)
VALUES('12345','Evans','Amenya')
INSERT INTO t_Customers(CustomerID,FirstName,LastName)
VALUES('324132123','ASas','ASasASas')
INSERT INTO t_Customers(CustomerID,FirstName,LastName)
VALUES('234234','evans','asdasdasd')
--===========================================
--create procedure to retrieve the records
CREATE PROCEDURE sp_getCustomers
AS
SELECT CustomerID,FirstName,LastName FROM t_Customers
--=========================================== - Create a new Visual Studio 2010 project
- Right-click on the project on the solution explorer and click on Add New Item on the pop-up menu as shown below
- The Add New Item dialogue box will appear as shown below. Select Crystal Reports in the middle pane then enter the name as “CustomersReport.rpt” in the Name field.
- Click the Add button.
Crystal Reports Gallery dialogue box will appear as shown below
Select As a Blank Report in Create a New Crystal Report Document options. Then click the OK button - A blank report will appear. Right-click on any blank area on the report, then point to Database on the pop-up menu that appears. Click on Database Expert as shown below
- The Database Expert dialogue box will appear as shown below. Expand the Create New Connection node. Then expand the OLE DB (ADO) node.
- OLE DB Provider dialogue box will appear as shown below. Scroll down and select SQL Server Native Client 10.0 from the list of Providers. Then click the Next button.
- OLE DB Connection information dialogue box will appear as shown below. Provide necessary information to log on and click Next.
- The Advanced Information dialogue box will appear as shown below. Click the Finish button.
- The connected database will appear as shown in the screen below. Expand the Database name node.
Expand the dbo node. Expand the Stored Procedures node. Select the stored procedure to use,
“sp_getCustomers”, then click the right-pointing single arrow to move the procedure to the right-hand pane.
- Click OK button.
The Database Expert dialogue box closes and the blank report is visible.
Expand the Database Fields node on the Field Explorer box. Expand procedure name node ("sp_getCustomers") as shown below.
- Drag and drop the fields from the Field Explorer box onto the blank report just below the Details
section. After you release the mouse key, the heading should automatically appear as shown below.
- The linking of our crystal report to sql server database is complete. Now we can create a .aspx page that will call it.
Add web form to the project and choose C# as the programming language. Also make sure the option to place code in separate file is selected. Name the page "CustomerReportViewer.aspx" - Drag and drop a CrystalReportViewer control from the Reporting tab onto the CustomerReportViewer.aspx page. Your page should appear as shown below,
- Add the following code on the code file
...
using System.Reflection;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using CrystalDecisions.Reporting;
using CrystalDecisions;
using System.Data.SqlClient;
public partial class CustomerReportViewer : System.Web.UI.Page
{
ReportDocument doc;
protected void Page_Unload(object sender, EventArgs e)
{
if (doc != null)
doc.Close();
}
protected void Page_Load(object sender, EventArgs e)
{
ShowReport(Server.MapPath(".\\CustomersReport.rpt"), "sp_getCustomers");
}
protected void ShowReport(String fileName, String strProcedureName)
{
TableLogOnInfo crTableLogOnInfo = new TableLogOnInfo();
ConnectionInfo crConnectionInfo = new ConnectionInfo();
CrystalDecisions.CrystalReports.Engine.Database crDatabase;
CrystalDecisions.CrystalReports.Engine.Tables crTables;
doc = new ReportDocument();
doc.Load(fileName);
crConnectionInfo.ServerName = "HP-PC";
crConnectionInfo.DatabaseName = "ProgrammingSamples";
crConnectionInfo.UserID = "xxxxxxx";
crConnectionInfo.Password = "yyyyyyyy";
crConnectionInfo.Type = ConnectionInfoType.SQL;
crConnectionInfo.IntegratedSecurity = false;
crDatabase = doc.Database;
crTables = crDatabase.Tables;
SqlConnection conn = new System.Data.SqlClient.SqlConnection(ConfigurationManager.AppSettings["ConnectionString"].ToString());
SqlCommand cmd = new SqlCommand("dbo." + strProcedureName, conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adpt = new SqlDataAdapter(cmd);
DataSet dataSet = new DataSet();
adpt.Fill(dataSet, "Customers");
foreach (CrystalDecisions.CrystalReports.Engine.Table crTable in crTables)
{
crTableLogOnInfo = crTable.LogOnInfo;
crTableLogOnInfo.ConnectionInfo = crConnectionInfo;
crTable.ApplyLogOnInfo(crTableLogOnInfo);
}
doc.SetDataSource(dataSet.Tables[0]);
CrystalReportViewer1.ReportSource = doc;
}
} - Set "CustomerReportViewer.aspx" as the startup page and run the project.
The report will be displayed as shown below.
No comments:
Post a Comment