Crystal Report with SQL Stored Procedure Parameter and Visual Studio

Follow the below steps to create Stored Proc and Crystal report to show in a Web page.

1. Create Northwind Report Web Application

   Open your visual studio and create new ASP.NET Web Application name it as NorthwindReports. You may use Default.aspx or add new aspx page. Open Default.aspx or newly created web form and add one DropDownList control name it as ddlCustomer. ddlCustomer use to show all Customers and user can select any customer to see its Order Details in the report. You can add those customers from Northwind database by creating SQL commands or add some static ListItems with similar values of Customer table.

   Add below ListItems to ddlCustomer. A entry is also added to see details of all customers.

   <asp:DropDownList ID="ddlCustomer" runat="server">
       <asp:ListItem Value="All" Text="All" />
       <asp:ListItem Value="ALFKI" Text="Alfreds Futterkiste" />
       <asp:ListItem Value="ANATR" Text="Ana Trujillo Emparedados y helados" />
       <asp:ListItem Value="ANTON" Text="Antonio Moreno Taquería" />
       <asp:ListItem Value="AROUT" Text="Around the Horn" />
       <asp:ListItem Value="BERGS" Text="Berglunds snabbköp" />
   </asp:DropDownList>
                   

   Your screen should look like

   

2. Create SQL Server Stored Procedure

   In this step, we will create a store procedure that returns Customer Order details from the Northwind database. Open your SQL Server and the Northwind database and use the below query. which creates GetCustomerOrderDetails and return details from Customer, Order, [Order Details], and Products table depending on CustomerID.

   USE [Northwind]
   GO
   
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   
   CREATE PROCEDURE [dbo].[GetCustomerOrderDetails]
   	@CustomerID nchar(5)
   AS
   BEGIN
   	
   IF @CustomerID = ''  
   BEGIN
   	SELECT Customers.CustomerID, Customers.CompanyName, 
           Customers.ContactName,  
   	Customers.ContactTitle, Customers.City, Customers.Phone,
   	Orders.OrderID, Orders.OrderDate, Orders.ShipCountry,
           [Order Details].UnitPrice,[Order Details].Quantity, 
           ([Order Details].UnitPrice * [Order Details].Quantity) AS 'Total',
           Products.ProductName 
   	FROM Customers JOIN ORDERS 
   	ON  Customers.CustomerID = Orders.CustomerID
   	JOIN [Order Details] ON [Order Details].OrderID = Orders.OrderID
   	JOIN Products ON Products.ProductID = [Order Details].ProductID
   END
   ELSE
   BEGIN
   	SELECT Customers.CustomerID, Customers.CompanyName, 
           Customers.ContactName,  
   	Customers.ContactTitle, Customers.City, Customers.Phone,
   	Orders.OrderID, Orders.OrderDate, Orders.ShipCountry,
           [Order Details].UnitPrice,[Order Details].Quantity, 
           ([Order Details].UnitPrice * [Order Details].Quantity) AS 'Total',
           Products.ProductName 
   	FROM Customers JOIN ORDERS 
   	ON  Customers.CustomerID = Orders.CustomerID
   	JOIN [Order Details] ON [Order Details].OrderID = Orders.OrderID
   	JOIN Products ON Products.ProductID = [Order Details].ProductID
   	WHERE Customers.CustomerID  = @CustomerID
   END	
   END     
       
   

   I always prefer to add business logic in SQL Server Stored Proc instead of creating formulas, custom fields, and other things in reports which just add complexity to the report. So I added business logic for a custom field to show the Total value of Product entry and to return all Customer details if input parameter CustomerID else return only Orders related to Customer is empty.

3. Create CustomerOrders Crystal Report

   Add new Crystal Report by right clicking on NorthwindReports application -> Select Add -> New Item. From New Window select Crystal Report give name as CustomerOrderReport.rpt and click Ok.

   

   Crystal Report Gallary will open for you to choose your report type. Select Using the Report Wizard from Create a New Crystal Report Document panel and select Standard from Choose an Expert panel and click Ok.

   

   Standard Report Creation Wizard will open, from Available Data Sources select My Connection -> [Your SQL Server name or Computer Name] -> Northwind -> Stored Procedure -> GetCustomerOrderDetails;1 and click on > button then Next buttons.

   The next screen Available Fields will display, select all fields under GetCustomerOrderDetails;1 and click on > and Next button.

   The next screen is Grouping, select GetCustomerOrderDetails;1.CustomerID and GetCustomerOrderDetails;1.ProductID and click on Next button.

   The next screen is Summaries select fields as shown in below screen and click on Finish.

   

4. Designing Crystal Report for CustomerOrder

   Your report is ready for design. You may delete all the fields that are auto- generated on report by the wizard. Design your report as per shown in below screen by selecting fields from Field Explorer -> Database Fields

   

5. Display CustomerOrder on ASP.NET ASPX page

   Now open Default.aspx or the page where you created DropDownList for customers. Add Button control to the page, name it as btnReport and create a click event for it.

   Add CrystalReportViewer control on page to show CustomerOrder.rpt file.

   You may display Customer and its order to ASP.NET repeater control before displaying it in Crystal Report.

   Add below code for btnReport click event which creates ReportDocument, ParameterField, and display Crystal Report file on the web page.

   
   protected void btnReport_Click(object sender, EventArgs e)
   {
       //Instantiate variables
       ReportDocument reportDocument = new ReportDocument();
       ParameterField paramField = new ParameterField();
       ParameterFields paramFields = new ParameterFields();
       ParameterDiscreteValue paramDiscreteValue = new ParameterDiscreteValue();
   
       //Set instances for input parameter 1 -  @CustomerID
       paramField.Name = "@CustomerID";
   
       if (ddlCustomer.SelectedValue == "All")
           paramDiscreteValue.Value = string.Empty;
       else
           paramDiscreteValue.Value = ddlCustomer.SelectedValue;  
               
       paramField.CurrentValues.Add(paramDiscreteValue);
       paramFields.Add(paramField);
   
       //Add the paramField to paramFields
       paramFields.Add(paramField);
   
       CrystalReportViewer1.ParameterFieldInfo = paramFields;
   
       string reportPath = Server.MapPath("~/CustomerOrderReport.rpt");
   
       reportDocument.Load(reportPath);
   
       //Load the report by setting the report source
       CrystalReportViewer1.ReportSource = reportDocument;
   }   
       
           

   If you are connecting to remote SQL Server you have to use SetDatabaseLogon method of ReportDocument after load method.

   
   reportDocument.Load(reportPath);
   
   reportDocument.SetDatabaseLogon("[user name]", "[password]",
                    "[Server details or DSN]", "[database]");