Crystal Reports: Using A Dataset And Multiple Tables
May 20, 2010
I was recently tasked with creating a excel file export of users in a database. Unfortunately the data is spread across multiple tables. Crystal Reports documentation on the web is unfortunately sparse when it comes to using multiple tables. In the following tutorial I will show you how to accomplish this task using asp.net and C# in Visual Studio 2008.
This tutorial assumes you have a site created properly with a web config and App_Data directories in the right places. Please google on how to create a web site using visual studio 2008 if you haven’t already done so.
Our first task here is to create a query to grab that data from your tables. So lets make an example sql query.
select
m.CreateDate, u.UserName, o.FirstName, o.LastName, o.CompanyName, o.Phone, o.CellPhone, .Email, o.HeardAboutUs, o.HeardAboutOther from Users u left join Membership m on m.UserId = u.UserId left join UserInfo o on o.UserId=m.UserId
order by m.CreateDate desc
Second let’s now create a dataset with a table having the fields you want to use in your report. In VS2008 click File->New File Click on the DataSet. Now lets add from the Toolbox a DataTable. Change the name of that table to whatever you want. Im our example lets use “tables”. Next let’s right click grey part at the top of the table and select add column. Add the columns 1 at a time. And you should end up with something that looks like this.
Now that we have our dataset created we need to make a report. Again File->New File select Crystal Report and name the file however you want. I’ll name mine mulist.rpt. Using the report wizard select a Standard report and click ok. In the Create New Connection select ADO.NET and click make a new connection. Then select the location of the dataset file you make in the previous section. Once this is done select the dataset and highlight it. Click on the arrow in the center to pass the dataset to the right side. Click next and then add all the available fields to the report. Click next twice and just pass the groups and filter fields. Choose a report style, i did standard and click on finish.
Now we can create our aspx page. Again File->New File and select Web Form. Now from the toolbox in the reporting section grab a crystalreportsviewer and insert it into your page. You can add a crystalreportsource if you want. But in this example I wanted a direct download so we’ll skip that for now. Your code should look something like this.
<CR:CrystalReportViewer ID=”CrystalReportViewer1″ runat=”server” AutoDataBind=”True” EnableDatabaseLogonPrompt=”False” />
Now on your code behind page add to the page load. you will add the sql query to your database connection info. I’ll leave that code up to you. Since different people connect differently.
selectSQL =
“select convert(varchar,m.CreateDate,110) as CreateDate, u.UserName, o.FirstName, o.LastName, o.CompanyName, o.Phone, o.CellPhone, “ +“m.Email, o.HeardAboutUs, o.HeardAboutOther “ +“from Users u “ +“left join Membership m on m.UserId = u.UserId “ +“left join UserInfo o on o.UserId=m.UserId “ +“order by m.CreateDate desc”;
//Your connection info should go here. If you have questions about this you can google around on the net for more examples.
// create the report
ReportDocument Rpt = new ReportDocument();
// define the report path
Rpt.Load(Server.MapPath(“mulist.rpt”));
// Fill the DataSet.
DataSet ds = new DataSet();// ad the table tables
MyDA.Fill(ds,“tables”);// this is important. you want to include all the tables
Rpt.SetDataSource(ds.Tables[0]);CrystalReportViewer1.ReportSource = Rpt;
CrystalReportViewer1.DataBind();
Response.Buffer =
false;
// Clear the response content and headers
Response.ClearContent();Response.ClearHeaders();
// Export the Report to Response stream in PDF format and file name Customers
Rpt.ExportToHttpResponse(ExportFormatType.Excel, Response, true, “ExcelDoc”);
Now hopefully when you run the page you will get an excel report with the data you want. The most important part here. And that it took me days to figure out was creating a DataTable and loading the column names in that table so you could push data programatically to the report. You can do this fairly easily when it’s just one table. Doing it this way makes it very easy to use multiple joins since you are still just using one table in the dataset file.
Got something to say?
You must be logged in to post a comment.
