Create a Web Based Non-Typed Crystal Reports Application

• Download source - 22 Kb

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%'