A web developer may or may not be aware of crystal report in depth as compared to desktop application developer.
Couple of months back I was suppose to access crystal reports in asp.net, but I found many problems while working with crystal report using crystalreportviewer in asp.net. I am writing this application for web-developers who want to access crystal reports in asp.net by using multiple tables or views etc. This application might help them and save their development time.
List of problems which I came across while accessing crystal reports in asp.net
- Logon failure Refer to following URL.
http://support.businessobjects.com/library/
kbase/articles/c2010371.asp
- DLL not found On a development machine if crystal report is not installed then you might get above error.
- Fail to render page Basically there could be two possible ways for the solution.
- This error can occur due to the unsuppressed report header, removing space between the header might solve the problem.
- This error can be solved by assigning permission to ASPNET user by default. ASPNET user does not have permission on windows XP and XP professional machine. To explain in detail it creates crystal report image in the local machine's temp directory or in the same directory where application is residing, due to security issue or permission problem. ASP.net unable to render image created in the memory and hence error occur.
Refer to following URL for the detail information.
http://support.businessobjects.com/library/kbase/articles/c2013414.asp
http://support.businessobjects.com/library/kbase/articles/c2014843.asp
http://support.businessobjects.com/library/kbase/articles/c2014618.asp
Merge Modules
Once build is ready and wanted to deployed it on the development server. You should add merge module in the build. This is important because you never know that crystal report is installed on the development machine and it is not possible for you to provide crystal report to the client. You can include following modules in the build and it will take care of all the further issues of deployment on the server.
- Crystal_managed2003.msm
- Crystal_database_Access2003.msm
- Crystal_datavase_Access2003.msm
- Crystal_regwiz2003.msm
-- I have used Northwind database of SQL-SERVER.
--View Categorywise_products on product and categories table.
create view ProductsList AS
SELECT Categories.CategoryName, Products.ProductName, Products.QuantityPerUnit, Products.UnitsInStock, Products.Discontinued
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE Products.Discontinued <> 1
To create a dataset object from a database.
Create a new schema file in the project:
- In the Solution Explorer, right-click the project name, point to Add, and click Add New Item.
- In the Categories area of the Add New Item dialog box, expand the folder and select Data.
- In the Templates area, select Dataset.
- Accept the default name Dataset1.xsd.
This creates a new schema file (Dataset1.xsd) that will be used to generate a strongly-typed dataset. The schema file will be displayed in the ADO.NET Dataset Designer.
Specify where the database is located:
- In the Server Explorer, right-click Data Connections and select Add Connection.
- In the Data Link Properties dialog box, click the Provider tab and select a provider (for example, Microsoft OLE DB Provider for SQL Server).
- Click the Connection tab and specify the location of your database. Enter server and logon information where necessary.
- Click OK.
Your database, its tables, and its fields now appear in the Server Explorer under the Data Connections node.
In the Solution Explorer, double-click Dataset1.xsd, if it is not already the active view. Dataset1.xsd should now be displayed in the Dataset tab.
To build a schema for your dataset, drag the desired tables from the Server Explorer to the Dataset tab of Dataset1.xsd. Click Save Dataset1.xsd to save the Dataset1.xsd file. On the Build menu, click Build to generate the dataset object for the project.
There is another reason why I have created xsd (schema file) for report. Let us consider a situation where you want to deploy your application on client's server. Now situation is you have set location of crystal report to your development server and though you set logon information programmatically logon failure occurs. To overcome this problem, I have a view associated in schema file. I can set location of ADO.NET Dataset to the xsd view and this will take care of all the problems for setting location while runtime, since it is independent of any of the server and to access dataset we are applying separate connection string from the web.config file. This way you can get rid of all the problems to access crystal reports in asp.net.
Actual code as below:
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using CrystalDecisions.Shared;
using CrystalDecisions.CrystalReports.Engine;
using System.Configuration;
namespace CrystalReports
{
/// <summary>
/// Summary description for WebForm1.
/// </summary>
public class WebForm1 : System.Web.UI.Page
{
protected CrystalDecisions.Web.CrystalReportViewer CrystalReportViewer1;
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
}
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
//Database connectivity
SqlConnection mycon = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
DataTable dtbl = new DataTable();
DataSet dtst = new DataSet();
SqlDataAdapter sqdt;
try
{
//Database activity
sqdt = new SqlDataAdapter("SELECT * FROM ProductsList",mycon);
sqdt.Fill(dtbl);
dtst.Tables.Add();
productList crptProList = new productList();
crptProList.SetDataSource(dtst);
CrystalReportViewer1.DisplayGroupTree = false;
CrystalReportViewer1.ReportSource = crptProList;
/**
* Passing parameter to the crystal report
* let us assume that i have added a parameter to the crystal report which
accepts
* categoryname and based on this generate report this parameter you can pass
from any web form as a request parameter
* string catName = Request.QueryString["catageoryName"];
* you can set parameter after setting report source as below
* crptProList.SetParameteraValue("CatagoryName",catName) similarly you can set
multiple parameters to the crystal report
* ------------- Overcomming problem of setting location at runtime -------------
* ------------- Setting Logon Information--------------------------------------
*
**/
}
catch(Exception ex)
{
Response.Write(ex.Message);
}
}
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
}
}