Download the Source

Why Stored Procedures to Dasets?
Our shop uses Stored Procedures to do most of our Crystal Reports. We currently develop the reports in Crystal 8.5 and 8.0, and are integrating the Crytsal.Net reports. When I first began using Crystal .Net, I discovered there was not a lot of documentation on displaying a Crystal .Net report using parameters in a Stored Procedure for an existing 8.5 report.

After many attempts, I finally got the report to work by binding the report (originally a Crystal 8.5 report), to a dataset in Crystal .Net. The dataset was populated from the original stored procedure. Later columns will show how to display a report using a straight Stored Procedure, and printing using the .Net viewer server control. Now let's walk through how to use an existing Crystal 8.5 report, convert it to .Net, and set the data source to a dataset. Then we will create code for the parameters making this an interactive report.

Requirements
I am assuming familiarity with asp.net concepts, Visual Studio .Net, SQL Server (7.0 or 2000), and Crystal Reports. For help with asp.net, I recommend any of the books like Steve Smith's ASP.Net by Example, Programming Data Driven Web Applications with ASP.NET by Donny Mack and Doug Seven or the great resources at aspfriends.com, aspadvice.com. support.Crystaldecisions.com as you would expect, has a lot of walkthroughs for various of the Crystal technologies. You'll also find a bunch of Crystal resources at /crystal/.  For SQL Server, again there are great mailing lists at aspfriends.com, your books on line (BOL).

Original Report
This report uses the Northwind Database. Basically we use a stored procedure (SP) that comes with Northwind called "Sales by Year". Also, in Visual Studio .Net, create a project called CrystalwSP. For this example, this project is an asp.net project using VB.Net.

Now that you have created the Visual Studio .Net project, let's emulate an existing crystal 8.5 report. To do this create a simple report in Crystal by going through the wizard (I am using Crystal 8.5 in this instance). I created a report that connects to the Northwind database, using the SP "Sales by Year" and groups by year. If you don't want to go through the hassle, the file is available for download at the end of this column.

Converting Report from 8.5 to .Net
First we need to convert the report from Crystal 8.5 to the new .Net format. First you need to be Visual Studio .Net. Next you need to have Crystal Reports referenced in your project. Do this by going to Projects/ References/ and Add the Crystal Reports Lib Info class.

Now add the Crystal report we created above to your project. Click on your project Select Add Existing Item, then Select Existing Object, and browse or enter the path and file name of the report we just created. Converting Report from Stored Procedure to Dataset Code First we need to create a dataset in the Project to use in our Crystal report.

I am assuming that you have experience with creating a dataset in a project. If not follow these instructions. · First from your data tab, drag and drop the SQL Connection object onto your form "webform1.aspx" setting it to your server with Northwind, and the Database to Northwind. · Next drag an SQLAdapter onto the form, using the sqlconnection you just set up. In the wizard, use Existing Stored Procedure, then set the select command to [Sales by Year]. Then finish the wizard · Finally, right click on the sqladapter you just added, and select Generate Dataset.

I kept the default dataset name, Dataset1. If you change the name, remember to use that name in place of Dataset1 that I am using in code. Now that your report and dataset are in the project, simply open the report in Visual Studio by double clicking on it.

Once you have it displaying in Visual Studio, right click anywhere in the report to display the different options available in Crystal .Net. Choose the Set Locations options, and select the dataset as your data location. Now save the report (it will prompt you to save it in the Crystal.Net format). I have saved this report in that format for download as test.rpt.

Code to Display
Now on to the fun stuff. First some housekeeping. Keep in mind that all code and forms are available for download at the end of the article.

Now, let's create a form to display the code and allow the user to input the dates. The form we create will display 2 text boxes to enter in the begin and end dates. Also we will have a refresh data button that will allow the user to enter in the dates, then press the button to refresh the report.

You may want to do some validation of the dates etc. here on the form, but that is beyond the scope of this article. I have zipped the contents of the file so you can utilize those files.

Below is the code in the aspx HTML form .I dropped the Crystal Viewer onto the page along with labels and the text boxes. The code that process's the data is in the code behind page:

Here is the code behind code:

As you can see, the code calls a procedure to build a new instance of the dataset the test report uses.

Dim myDs As New DataSet1()

It then creates a new instance of the report and binds the new dataset to that report, and binds the Crystal Viewer to that report.

Dim oRpt As New Test() oRpt.Load() oRpt.SetDataSource(myDs) CrystalReportViewer1.ReportSource = oRpt myconnection.Close()

The method BindReportToDS can be called by the refresh button and is called the first time your page loads. Feel free to modify the code That pretty much sums up creating a dataset for your Crystal report from a Stored Procedure. I created this because most of the resources I looked up did not cover this directly that I could see. I believe as I get more familiar with the tools in Visual Studio .Net and Crystal .Net this code can become more efficient. In my next column, I will tackle printing using the Crystal .Net server control (viewer).

Eric Landes, your Corporate Coder
email:corporatecoder@aspalliance.com

Crystal Resources

Crystal Alliance