[ 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
Customers.ContactName AS Field1,
'' AS Field2
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.