[ 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.