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