Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


Crystal Reports Using a DataSet DataTable

Crystal Reports - Dynamic WebForms Using a DataSet and SQL DataReader In Custom Class

This article describes how to retrieve data dynamically into a DataSet DataTable. The DataTable (not the TableAdapter Object) in the DataSet does not have a connection to the database. The DataTable columns represent the data fields that will appear on the Crystal Report. The DataSet will be populated with data from the AdventureWorks database using a SQLDataReader created in a Class.

Create the SQL Server Data Source for Crystal Reports

This project will use a SQL stored procedure called Sales.usp_SalesCustomers from the Adventure Works database. The View in the Stored Procedure code calls a custom view called dbo.vw_SalesCustomers.

CREATE PROCEDURE [Sales].[usp_SalesCustomers]
AS
SELECT CustomerID, CustomerName, AccountNumber, Territory, TotalYTD from
[dbo].[vw_SalesCustomers]

Code for the View.

ALTER VIEW [dbo].[vw_SalesCustomers]
AS
select Sales.Individual.CustomerID,FirstName + ' ' + LastName as CustomerName, Customer.AccountNumber, SalesTerritory.Name as Territory,
Demo.ref.value('declare namespace dr="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
(dr:TotalPurchaseYTD)[1]','varchar(50)') as TotalYTD
from Person.Address INNER JOIN Sales.CustomerAddress
ON Person.Address.AddressID = Sales.CustomerAddress.AddressID
INNER JOIN Sales.Customer ON Sales.CustomerAddress.CustomerID = Sales.Customer.CustomerID
AND Sales.CustomerAddress.CustomerID = Sales.Customer.CustomerID
INNER JOIN Sales.Individual ON Sales.Customer.CustomerID = Sales.Individual.CustomerID
INNER JOIN Person.Contact ON Sales.Individual.ContactID = Person.Contact.ContactID
INNER JOIN Sales.SalesTerritory ON Sales.Customer.TerritoryID = Sales.SalesTerritory.TerritoryID
AND Sales.Customer.TerritoryID = Sales.SalesTerritory.TerritoryID outer apply
Demographics.nodes('declare namespace dr="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; /dr:IndividualSurvey') as Demo(ref)

Create an ASP .NET WebSite in Visual Studio.

Create Web Site Project

Adding the DataSet and SQL Server Connection Class

Right click in the Solution Explorer on the project and click Add New Item. Select DataSet from the templates listed and click Add.

Add DataSet to Project

Add a DataTable to the empty DataSet. Change the Name of the DataTable by clicking in the top portion. Right click on the DataTable to Add the Columns (field names) to the DataTable.

Add Columns to DataSet

Add a class to the Project. This will be the class that will return a populated DataSet to the Default.aspx WebPage to be used as the ReportDataSource for a CrystalReport.

Add Data Layer

Add using statements to include SQL classes and objects in the new class.

using System.Data.SqlClient;
using System.Data;

Add the code to the class that will create a SqlConnection object and SqlCommand object to connect to the database and execute the stored procedure into a SqlDataReader. The code will create a new DataSet with the same schema as the DataSet1 created earlier and then load the DataSet's DataTable with the data from the SqlDataReader. Finally, it will return the populated DataSet to the Default webpage. Important: Add the namespace attribute above the class name. It will be used in the webpage to access the method created to return the DataSet.

namespace ReportTest
{
 public static DataSet sqlReportData()
 {
  try
  {
   string scmd = "Sales.usp_SalesCustomers";
   string scon = @"Data Source=YourSQLServer;Initial Catalog=AdventureWorks;Integrated    Security=SSPI;";
   SqlConnection cn = new SqlConnection(scon);
   cn.Open();

   SqlCommand cmd = new SqlCommand(scmd, cn);    cmd.CommandType = CommandType.StoredProcedure;
   SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
   DataSet ds = new DataSet();
   ds = new DataSet1();
   ds.Tables["SalesCustomers"].Load(dr);
   return ds;
  }
  catch (Exception ex)
  {
   throw ex;
  }
 }
}

Adding the Crystal Report

Create the CrystalReport by clicking Add New Item.

Add Crystal Report

The wizard will appear and by clicking Using the Report Wizard, you can step through to select the DataSet and the fields for the report.

Crystal Reports Wizard

Select the DataSet1 object and click the > button to add it to the Selected Tables box.

DataSet Connection

Click on the >> button to add all of the fields to the report.

Crystal Reports Choose Fields

Select the Style of the report and click Finish.

Crystal Report Finish

Open the FieldExplorer to Expand the DataSet and drag and drop fields onto the report and format them as needed.

Crystal Report Formatting

Adding the Crystal Report to the ASP .Net Web Page

Double click the Default.aspx page in the Solution Explorer to open it. Select Split or Design View to get the Toolbox to appear. Drag and drop a CrystalReportViewer onto the Design portion of the page.

Report Viewer

In the Solution Explorer, double click the Default.aspx.cs page to open it. Add these lines of code to the top of the page under the Using statements.

using ReportTest;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.ReportSource;
using CrystalDecisions.Web;
using System.Data.Common;

Add this code within the page class. This code will create a new DataSet and populate it with the data from our custom class by calling the static method sqlreportData(). Using the ReportDocument object, the data source for the report can be set and then loaded dynamically to the Viewer at run time.

ReportDocument rd = new ReportDocument();

protected void CrystalReportsLoad()
{
 try
 {
  DataSet ds = new DataSet();
  ds = SQL_Class.sqlReportData();
  reportPath = Server.MapPath("CrystalReportSales.rpt");
  rd.Load(reportPath);
  rd.SetDataSource(ds);
  rd.PrintOptions.PaperOrientation =   CrystalDecisions.Shared.PaperOrientation.Landscape;
  CrystalReportViewer1.ReportSource = rd;
  CrystalReportViewer1.DataBind();
 }
 catch (Exception ex)
 {
  throw ex;
 }
}

Finally, add code to detect the Page_Init event and call the code to run & databind the report here.

protected void Page_Init(object sender, EventArgs e)
{
  try
  {
   CrystalReportsLoad();
   }
  catch (Exception ex)
  {
   ex.Message.ToString();
  }
}

Run the program to verify that the report runs without any problems or errors.

Final Report