Click here to Skip to main content
Click here to Skip to main content

Create a Web Based Non-Typed Crystal Reports Application

, 5 Oct 2005
Rate this:
Please Sign up or sign in to vote.
This article explains how to build a generic and data bound Web based Crystal Reports server.

Introduction

The .NET Crystal Reports engine supports two kinds of reports: strong typed and non–typed. The strong typed reports bind reports into the executable binary at design time via dataset classes, and the report files are not required at runtime. Non-typed reports bind reports at runtime, and dataset classes are not included into the application at design time, but report files are physically required to be present with binary assembles at runtime.

While each type of report has its own pros and cons, the non-typed reports have two advantages. First, it provides more scalability and flexibility. Since applications do not compile a dataset class into the executable, when a new report is added, the only thing that needs to be done is to create a new report file, and put it into the report server. Meanwhile, since report design and creation are separated from the report applications, the executable assembly can remain “static” and generic to report files. The cost and effort to maintain, deploy and update applications can be significantly reduced.

Using the code

Create a simple report file

  1. Create a dumb Windows application project. (Visual Studio .NET does not allow to create a Crystal Reports file without creating a project first).
  2. Add new item, and select Crystal Reports from the dialog, name the report file as Report1.rpt, and then click Open.
  3. On the Crystal Reports Gallery dialog, select Blank Report, then click OK.
  4. Right click the mouse on the report working area, and select Database, Add/Remove Database… from the context menu. Then select OLEDB (ADO), and OLEDB Provider for SQL Server, from the OLEDB (ADO) dialog, and type in the required information.
  5. On the Database Expert dialog, select the table Titles from the Pubs/Database/Tables tree.
  6. Select fields on the Report design screen, then save the report file.

Create a Web based Reporting application

The next step is to create an ASP.NET application to process and present this report:

  1. Create a new ASP Web application, name the project as MyReport. Rename the default webform1.aspx to main.aspx (you can keep default names if you like).
  2. Add CrystalDecisions to project reference from the Solution view.
  3. Place the code in the source main.aspx Page_Load section into your Page_Load event section, and then build the solution.

All codes are wrapped in the Page_Load event. It does the following:

  1. Creates a Crystal Reports object instance, parses the report name from the QueryString, and loads the report file into the Report object.
  2. Applies logon information to the Report object.
  3. Passes additional parameters (if any) from the QueryString, passes them into the Report object.
  4. Export the report into PDF format (or any other format), and write back to the client browser.

The code for loading the report is as follows:

String reportFile=Request.QueryString.Get("Report") +".rpt";
String appPath=MapPath(".");
String reportPath = appPath + "\\" + reportFile;
ReportDocument theReport=new ReportDocument();
theReport.Load (reportPath);

After the report is loaded, we can apply the logon information to the Report object:

CrystalDecisions.Shared.ConnectionInfo conn=new ConnectionInfo();
CrystalDecisions.CrystalReports.Engine.Table myTable; 
CrystalDecisions.Shared.TableLogOnInfo myLog;
conn.ServerName="My-Sql-Server";
conn.DatabaseName="pubs";
conn.UserID ="sa";
conn.Password="password";
for(inti=0;i<theReport.Database.Tables.Count;i++)
{
    myTable=theReport.Database.Tables[i];
    myLog = myTable.LogOnInfo;
    myLog.ConnectionInfo= conn;
    myTable.ApplyLogOnInfo(myLog);
    myTable.Location= myLog.TableName;
}

Exporting the report file to PDF (or another format), and writing back to the client browser are quite simple:

System.IO.MemoryStream m;
m= (System.IO.MemoryStream)theReport.ExportToStream(
    CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
theReport.Close();
Response.ClearContent();
Response.ClearHeaders();
Response.ContentType="Application/pdf";
Response.Buffer="true";
Response.BinaryWrite(m.ToArray());
Response.End ();

Now put the report1.rpt file in the same directory as the ASP page. We can test the Web application using a URL like this:

http://localhost/Myreport/main.aspx?Report=report1

Report with Parameters

To illustrate the report with parameters, we create a stored procedure "Get_Title1" with the following code:

Create procedure Get_Title1(@price float ,@pubdate datetime)
As
set nocount on
select Title, type, price, notes, pubdate from titles
where price<=@price and pubdate<=@pubdate
go

Then we create a new report file, report2.rpt. It should be identical to report1.rpt, except it will use the stored procedure "Get_title1" as table name. At the report design screen, when "Get_title1" is selected, parameters @price and @pubdate also show up.

At the web Page_Load event, we need code to process the parameters, as follows:

String paramName,paramValue;
CrystalDecisions.Shared.ParameterValues pList = new  ParameterValues()
CrystalDecisions.Shared.ParameterDiscreteValue pV=new ParameterDiscreteValue ();
for( int i=0;i<theReport.DataDefinition.ParameterFields.Count;i++)
{
    paramName=theReport.DataDefinition.ParameterFields[i].Name;
    paramValue=Request.QueryString.Get(paramName); 
    pV.Value =paramValue;
    pList.Add(pV);
    theReport.DataDefinition.ParameterFields[paramName].ApplyCurrentValues(pList);
}

To run the report with parameters, the URL to be used looks like this:

http://localhost/Myreport/main.aspx?report=report2&@price=15&@pubdate='1/1/2000'

Dynamic SQL Statement as Parameter

Predefined parameters cannot always meet the needs of a complicated query. Although at design time, Crystal Reports can create a report derived from a SQL command, it disallows to access and modify a SQL command at runtime. To workaround this, we can use the feature of SQL Server executing dynamical SQL statements to generate a report, then pass a SQL string to run the report from the URL.

First, let's create a stored procedure to execute a dynamic SQL statement:

Create procedure Get_Title2(@sqlstring varchar(2000))
As
set nocount on
create table #temp
(title_id char(6),title varchar(80),type char(12),
pub_id char(4),price money,advance money,
royalty int,ytd_sale int, notes varchar(200), pubdate datetime)
insert into #temp
exec(@sqlstring)
select * from #temp
drop table #temp
go

Then create report3.rpt derived from the stored procedure 'Get_title2'. Save it and run the URL like:

http://localhost/myreport/main.aspx?report=report3&@sqlstring=select * from 
titles where type='business' and price<20 and upper(title) like '%COMPUTER%'

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

junyaoxu

United States United States
like to write simple codes to solve complicated problems.

Comments and Discussions

 
GeneralNeed to open the pdf in open model window PinmemberkarthiBalu20-Mar-07 4:52 
GeneralCrystal Report Help Pleaseeee Pinmemberashokjangid12-Nov-06 23:07 
GeneralCrystal Report Help Pinmemberashokjangid12-Nov-06 23:07 
Hi everybody,
I am developing a web based application which is having lots of report. can anyone know how to pass a query into a crystal report in web application. I know in vb and vb.net(desktop applicaion),but how can do this in web application i don't know.Please Help to recover these kind of problem.
Thanking You,
 
Ashok
GeneralCrystal Report graphs PinmemberJesu.Official6-Nov-06 0:58 
GeneralServererror on Application... PinmemberJan Waiz8-Jan-06 23:38 
GeneralRe: Servererror on Application... Pinmemberjunyaoxu9-Jan-06 3:00 
GeneralRe: Servererror on Application... Pinmemberterror2310-Jan-07 19:52 
GeneralLogon PinmemberAubyone23-Nov-05 18:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.141022.2 | Last Updated 5 Oct 2005
Article Copyright 2005 by junyaoxu
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid