Create Crystal Reports on the Fly using ASP.NET
page 3 of 4
by Eric Landes
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 59390/ 112

Building the Report using Different Fields

[ Download Sample ]

There are some great articles that show how to dynamically bind your reports to a report viewer on a web page.  If you are not aware of the concepts, check out the ASPAlliance Crystal section, Brian Bishof’s Crystal book, or the Business Objects support site.  I will show you some binding code but will not be going over it in detail. Be sure to refer to those other resources if you want to go more in depth with the subject.

To begin with, create an ASP.NET Web Project.  After I created the project I created a default.aspx web page because that’s part of my normal routine when creating web projects.  On the default.aspx web page I drag and drop a Crystal Report viewer from the toolbox onto the web page.  This object is automatically named CrystalViewer1 and, for this article, I won’t change that.  I do believe it is good practice to have some sort of naming conventions though. For demonstration purposes, the name that will be used for the viewer is CrystalViewer1.

The following code populates the DataSet with data using that new select stored procedure. It then changes the caption for CustomerID to “Testing 2”, and the first caption to “Testing”. To access the different properties and methods, you need to cast the different objects to their proper type.  It’s hard to know which type to use and I found out by going through code.  Some are obvious where others may not be so obvious.  If a wizard created a text object as the heading for your field, it’s probably a FieldHeadingObject. Other objects are a DataFieldObject and a plain old TextObject. The code snippet below is from the download link.  If you have any questions on it, please refer to that code.

Code Listing 2

oRpt.Load(@"C:\wwwroot\TestCrystalDynamic\NorthwindSales2.rpt");
DataSet  dsCust1 = new DataSet();
dsCust1 = SqlHelper.ExecuteDataset("Data Source=(local);Initial
Catalog=Northwind;Integrated Security=SSPI"
CommandType.StoredProcedure,"NewSelectCommand");

dsCust1.Tables[0].TableName="Orders"
oRpt.SetDataSource(dsCust1);

((CrystalDecisions.CrystalReports.Engine.TextObject)oRpt.ReportDefinition.
Sections[4].ReportObjects[4]).Text="Testing"
((FieldHeadingObject)oRpt.ReportDefinition.Sections[4].ReportObjects[1]).Text="Testing 2"

CrystalReportViewer1.ReportSource=oRpt;

Now, what if we want to change one of the fields being used to be another field? There are a couple of options to accomplish this. One would be to change the field before binding the report (in populating the DataSet phase). For instance, if I wanted to change the CustomerID field to the CategoryName field, I could use the following SQL code:

Code Listing 3

SELECT Categories.CategoryName as 'CustomerID',
[Order Details].UnitPrice,
[Order Details].Quantity,
Products.ProductName,
Customers.CompanyName,
Customers.ContactName AS Field1,
'' AS Field2
FROM Orders
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
INNER JOIN Products ON [Order Details].ProductID = Products.ProductID
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID

I kept the same alias name for the field of CustomerID.  When the DataSet is generated, the report will be able to bind properly since the structure of the DataSource has not changed. With this code, placed in the binding code, I can then change the source field on a report from one field to another. I can also change the heading programmatically. The following code demonstrates this:

Code Listing 4

oRpt.Load(@"C:\wwwroot\TestCrystalDynamic\NorthwindSales2.rpt");
DataSet  dsCust1 = new DataSet();

dsCust1 = SqlHelper.ExecuteDataset("Data Source=(local);Initial
Catalog=Northwind;Integrated Security=SSPI"
CommandType.Text,"SELECT Categories.CategoryName as 'CustomerID', " +
"[Order Details].UnitPrice, [Order Details].Quantity, Products.ProductName, " +
"Customers.CompanyName, Customers.ContactName AS Field1, '' AS Field2 " +
"FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID " +
"INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID " +
"INNER JOIN Products ON [Order Details].ProductID = Products.ProductID " +
"INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID");

dsCust1.Tables[0].TableName="Orders"
oRpt.SetDataSource(dsCust1);
((FieldHeadingObject)oRpt.ReportDefinition.Sections[4].ReportObjects[1]).Text="Category"
CrystalReportViewer1.ReportSource=oRpt;

Now that I have demonstrated one method of changing the fields and the headers, I need to point out that there are other ways to accomplish this as well, including using FieldObjects and FieldDefinitions. This should give you a taste of how to change the field and caption on the fly.

Changing Formatting

Now let’s do some fancy formatting. Actually, since I might be classified as “style-impaired”, we’re not really doing fancy formatting. However, you folks with some sense of style may be able to utilize this information to create some fancy styles on the fly.

This should be as simple as what we’ve been doing with the captions and setting different fields.  Just by adding the following code after setting the text of the caption, you can change the font color and position of the header object:

Code Listing 5

((CrystalDecisions.CrystalReports.Engine.TextObject)
oRpt.ReportDefinition.Sections[4].ReportObjects[4]).Color=Color.CadetBlue;
((CrystalDecisions.CrystalReports.Engine.TextObject)
oRpt.ReportDefinition.Sections[4].ReportObjects[4]).Top=1;

Experiment with the color, font, and other styles to change the formatting.


View Entire Article

User Comments

Title: drag and drop   
Name: mohit
Date: 2006-02-16 7:33:35 AM
Comment:
Hello Sir,

i m trying to make project using drag and drop function.but my problem is that when i m try to connect two or more picturebox using lines , i cant do it . if i clear or refresh the lines then at a time only one line is possible and i cant draw lines with other boxes .
i need ur help . can u tell me how it is possible.

email id :- rathod_mohit20@yahoo.co.in
Title: Query   
Name: Murali
Date: 2006-01-26 11:39:56 PM
Comment:
Sir i want to generate a crystal report with dynamically a table is generated on runtime.is it possible
Title: need creating a crystal report   
Name: d pavan
Date: 2006-01-20 10:34:44 PM
Comment:
Dear friend

I need a code to create a crystal report using a sql database, with vb.net as backend code
Title: Nice code but one more clarification   
Name: jalaj
Date: 2006-01-11 11:54:59 PM
Comment:
i have one scenario in which i have certain bills which are to printed and bill page no I m getting from database so now if I want to manipulate the page no for last page programmatically then how to do that
Title: great   
Name: navaneethkrishnan
Date: 2005-12-27 12:01:01 AM
Comment:
the article is great and really useful. wish you good luck.
Title: Nice Article   
Name: Sunny
Date: 2005-12-13 9:58:36 PM
Comment:
Hi Eric,
This is article is very nice. And downloaded the sample but somefiles are missing in the project. It's asking for customerOrders.cs file and we couldn't find SQLHelper class file. "The referenced component 'Microsoft.ApplicationBlocks.Data' could not be found". Can U please send me those files. It will be very much useful to me. Thanks, Sunny
This is my Mail Id : laxman.rd@gmail.com
Title: AssemblyInfo.cs   
Name: Chris
Date: 2005-11-29 3:15:11 PM
Comment:
I try to run the report.It didn't include the AssemblyInfo.cs file in that zip file. could you send the "AssemblyInfo.cs" file to my Email chris_chin192@yahoo.com
Thank.
Title: Crystal Report   
Name: Vishal
Date: 2005-09-08 5:33:42 AM
Comment:
If Noboby Having SqlHelper class Then What We Will Use instead of SqlHelper Class
Title: Can I?   
Name: Shararti
Date: 2005-08-19 8:12:10 AM
Comment:
How can i configure a Crystal Report @ Run Time, Means that Creating a DataSet @ Run time and other things?

Thanks.
Title: GREAT !!!   
Name: Nimesh Sheravia
Date: 2005-07-30 8:10:10 AM
Comment:
Great Code that will help me for my application development.
Title: Not Working   
Name: Rukku
Date: 2005-07-28 3:53:26 AM
Comment:
my mail ID : reddy.ruk@gmail.com
Title: Not working   
Name: Rukku
Date: 2005-07-28 3:49:18 AM
Comment:
its saying invalid cast, any reponses appreciated

thanks
Title: UR CODE NOT WORKING   
Name: anupama
Date: 2005-07-26 10:58:29 AM
Comment:
Hi,
I tried to run ur code by just using a single line by converting it to vb.net
oRpt.SetDataSource(dsCust1);
((FieldHeadingObject)oRpt.ReportDefinition.Sections[4].ReportObjects[1]).Text="Category"

But it is not working it gives error invalid cast.

Could you plese help me why is this coming
email: anupama_lakhanpal@satyam.com
Title: CustomerOrders.cs missing   
Name: govinn
Date: 2005-06-29 9:36:02 AM
Comment:
CustomerOrders.cs file is needed for the project. But it is missing in the download.
Title: Thanks   
Name: Caleb
Date: 2005-05-31 12:30:13 AM
Comment:
Sir,
This is Caleb. I thank you for your kind reply.

caleb
Title: Nice   
Name: Caleb
Date: 2005-05-30 7:43:33 AM
Comment:
sir,
This is Caleb. I've downloaded your sample on how to dynamically create new looking reports by using an existing report. But you didn't include the AssemblyInfo.cs file in that zip file. It will be more usefull for me if you send me the "AssemblyInfo.cs" file to my Email id
caleb.sathiyanathan@inmail.tranquilmoney.com
Thank you
caleb

Product Spotlight
Product Spotlight 



Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2018 ASPAlliance.com  |  Page Processed at 2018-04-24 8:45:33 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search