[ Download Sample ]

Those of you familiar with Crystal are probably asking, what exactly I’m smoking?  You can’t create Crystal Reports on the fly, at least not with Crystal .NET.  So I’ll admit this title is a little misleading.  But I will show you how to dynamically create new looking reports by using an existing report.  I consider that close to creating reports on the fly.

I’ve seen many .NET developers asking how to add fields to existing reports or how to altogether create a Crystal report on the fly, in code.  The answer for those using Crystal .NET is that it can’t be done.  However, using Crystal 10, I’m going to show you how to come close to dynamically creating your report.

System Requirements

This code was created using Visual Studio 2003 Enterprise Architect, Crystal Reports 10 Developer Edition, and SQL Server 2000 on a Windows XP SP2 machine.  I also have the Northwind sample database installed on my SQL Server and I utilize the 1.x version of the Microsoft Data Access Application Blocks in some of the C# code.  I’m also assuming that the reader has experience developing with ASP.NET, C#, Crystal Reports, and SQL.

[ Download Sample ]

I created a sample report based on the Northwind database using the Report Wizard in Crystal 10.  This involved creating a DataSet in Visual Studio, and then creating the ADO.NET connection in Crystal.  The DataSet was created using the following select statement (as a new stored procedure):

Code Listing 1

SELECT Orders.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

I accomplished this the easy way by configuring a SQLAdapter on a Web Page in Visual Studio.  To accomplish this within Visual Studio .NET 2003, create a new ASP.NET Project. Drag and drop a SQL Adapter object from the Data toolbox onto the Web Form. Using the “Configure Data Adapter” option found when right clicking on the Data Adapter object, I defined the connection information and the select statement by using the steps below. 

The wizard stepped me through creating a connection, and I selected “Create New Stored Procedures” when prompted.  That’s where I placed the select statement which is provided in code listing 1.1.  Once I had stepped all the way through the wizard, I right-clicked again and selected “Generate DataSet”.  You can create your DataSet this way or any other way you are comfortable with.  For example, create a DataSet in code and use its WriteXml method.

Once I created the DataSet in Visual Studio, I opened the Crystal Reports Designer (CR 10) from the Programs Menu.  You could also add a Crystal Report to your project in Visual Studio using the "Add New Item" option, and open the report from there.  Since you have Crystal Reports 9 or 10, you can also open the stand-alone designer, which I prefer. Select “Create a Report” using the wizard. In the Create Report wizard, I selected the ADO.NET (XML) connection. I then navigated to the XSD file I had just created (an XML file will work also).  For example, the path to that file was c:\dev\TestCrystalDynamic\CustomerOrders.xsd.

When prompted to select the table, I only have one table in my DataSet, named Orders.  I double-clicked on Orders to add it to the Tables used in the report.  Moving to the next screen, I selected all the fields available to display.  In the next screen, the group by dialog box, I added the CompanyName field as the first Group. I also put the ProductName field underneath CompanyName as the second Group. 

In the Details Section, I have the fields CustomerID, UnitPrice, Quantity, Field1, and Field2. I included Field1 and Field2 as buffer fields in case I need a couple more fields in the dynamic creation of the reports. If those fields are not required, we can hide these fields programmatically.

After I selected the grouping fields, I then kept the defaults throughout the wizard until the themes dialog box appears.  On the themes dialog box, I select the Corporate Green theme.  If you are following along, feel free to do things a little differently with your report, or you can download my report with the related XML file. 

Now we have a finished report.  Save that report somewhere; I saved mine with the filename Northwind2.rpt.  Now we’re ready to work some dynamic magic with this report.

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

[ Download Sample ]

In this article, I have attempted to present you with some ideas on how to programmatically customize the appearance and change the data presented in your report. While this is not a comprehensive guide on how to create dynamic Crystal reports, you may use this as a starting point for your Crystal Projects. I’d love to hear of others who may be doing more than this with Crystal Reports and .NET. I will try to explore more dynamic coding of Crystal in future articles (especially if there are any changes within Visual Studio .NET 2005). Keep on Codin’.