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

Report Layout

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

View Entire Article

User Comments

Title: drag and drop   
Name: mohit
Date: 2006-02-16 7:33:35 AM
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 :-
Title: Query   
Name: Murali
Date: 2006-01-26 11:39:56 PM
Sir i want to generate a crystal report with dynamically a table is generated on it possible
Title: need creating a crystal report   
Name: d pavan
Date: 2006-01-20 10:34:44 PM
Dear friend

I need a code to create a crystal report using a sql database, with as backend code
Title: Nice code but one more clarification   
Name: jalaj
Date: 2006-01-11 11:54:59 PM
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
the article is great and really useful. wish you good luck.
Title: Nice Article   
Name: Sunny
Date: 2005-12-13 9:58:36 PM
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 :
Title: AssemblyInfo.cs   
Name: Chris
Date: 2005-11-29 3:15:11 PM
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
Title: Crystal Report   
Name: Vishal
Date: 2005-09-08 5:33:42 AM
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
How can i configure a Crystal Report @ Run Time, Means that Creating a DataSet @ Run time and other things?

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

Name: anupama
Date: 2005-07-26 10:58:29 AM
I tried to run ur code by just using a single line by converting it to

But it is not working it gives error invalid cast.

Could you plese help me why is this coming
Title: CustomerOrders.cs missing   
Name: govinn
Date: 2005-06-29 9:36:02 AM
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
This is Caleb. I thank you for your kind reply.

Title: Nice   
Name: Caleb
Date: 2005-05-30 7:43:33 AM
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
Thank you

Product Spotlight
Product Spotlight 

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

©Copyright 1998-2024  |  Page Processed at 2024-07-25 12:26:35 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search