The following writeup will show you how to use a .NET datatable and map it to fields within a Crystal Report, by using .NET instead of Crystal’s language.
Using Crystal Reports can sometimes pose difficulties if you are mapping a report directly to a data source. The difficulty lies with the limitations of Crystal Report’s built in language. It is sometimes much easier to modify the DataTable to your liking within .NET and then passing that DataTable as your data-source in Crystal Reports. This way, you can create any type of Dataset easily, use any of your SQL queries, and gives you complete control of the data that you pass to Crystal Reports. It is my preference to have such control within .NET instead of relying on Crystal Report’s data mechanisms.
In this article, I will show you a straight-forward procedure to be able to do this.
The first step is to create a Dataset that will mimic the columns in your DataTable. Right click on your solution, select Add New Item, select the Data tab on the left hand side, and select Dataset.
Call this Dataset any name you’d like, and then click Add. You will then be presented with the Dataset Designer. You can use this to create a Dataset that contains the table (or tables) that you would like to pass to Crystal Reports.
Right click on the Dataset Designer and select Add New Table.
Here I have created a basic DataTable that has four columns. The Dataset designer gives you complete control over each type of column, and it is a good idea to specify the type of each column so that Crystal Reports knows how to handle them properly. For example, I have specified the ‘WorkingTime’ column to be an Int32 so that Crystal Reports will know that this column is a number, and then I can use the features within Crystal Reports (such as Sum, Average, etc.).
Once I have created this Dataset, I can then use this as a Data-Source within Crystal Reports. At this point, go into your crystal report that you wish to add this Dataset to, then in the ‘Field Explorer’ right click Database Fields and then select Database Expert.
In ‘Available Data Sources’ look under ‘Project Data’, then under ‘ADO.NET DataSets’, and voila! Your newly created DataTable appears in the list. Highlight the DataTable and select the single right arrow so that it is in your list of ‘Selected Tables’.
Select OK and then voila! Now you can place the fields directly onto your report just as you would with any other field.
Now, it’s just a matter of mapping a DataTable you have within .NET, with any calculations you may need to perform before you send it to Crystal Reports. By the time you send your DataTable to Crystal Reports it should be ready to go.
Dim EmployeeTable As DataTable = EmployeeDB.GetWorkingTime()
Try
Dim Report As New CrystalDecisions.CrystalReports.Engine.ReportDocument
Report.Load("EmployeeReport.rpt")
Report.Database.Tables("EmployeeTime").SetDataSource(EmployeeTable)
frmReport.CrystalReportViewer1.ReportSource = Report
frmReport.Show()
Catch ex As Exception
Throw ex
Finally
Report.Dispose()
End Try
Joseph Regan NJ 