Crystal Report with SQL Stored Procedure Parameter and Visual Studio

by GeeksArray

This article will describe you how to create Crystal Report using Stored Procedure Parameters and Visual Studio. Crystal Report tool was available with Visual Studio 2005 or its previous versions however it does not include with the default version of VS2010. You will have to download and install it.

For this tutorial, I am using VS 2013, SQL Server. I used Northwind database to generate CustomerOrders report. If you do not have Northwind database you can get it from here.

This tutorial will create ASP.NET web application with Web Form which displays all Customers in DropDownList control. On selection of Customer from DropDownList it will call the stored procedure which accepts CustomerID as input parameter and return its Customer and Order details. Those Customer and Order details will be shown in Crystal report.

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

    drop down list in asp.net application

  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.

    Add new crystal report

    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.

    Crystal Report Gallary for Visual Studio

    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.

    Summary fields for Northwind Crystal Report

  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

    Northwind Customer Order Crystal Report

  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]");    
    
    

    Northwind Customer Order Crystal Report

Source code on Git hub Source Code on Github

Speak your mind
Please login to post your comment!


  • geeksarray user
    02/22/2014 12:30 PM jsk

    hi this code is working well for the first post back but when I am (Selecting the Ddl and clicking button) for the next time its asking to enter the parameter value please help me to solve this

  • geeksarray user
    02/23/2014 03:12 PM brandonteohno1

    hi jsk, I do not have the time to look thoroughly into this sample code. However, with a quick glimpse, it looks that the codes provided under '5. Display CustomerOrder on ASP.NET ASPX page' didn't attempt to 'silence' the parameter prompt. Hence, if you are talking about direct conversion of this sample into C#, here it is: 1.1)Assuming that CRViewer is a type of CrystalDecisions.Windows.Forms.CrystalReportViewer 1.2)Need to include references for: CrystalDecisions.CrystalReports.Engine CrystalDecisions.Shared CrystalDecisions.Windows.Forms 1.3)Conversion for LoadReport_1 private void LoadReport_1(Microsoft.VisualBasic.Collection remoteDataCol) { CrystalDecisions.CrystalReports.Engine.ReportDocument CrxReport = new CrystalDecisions.CrystalReports.Engine.ReportDocument(); String strReportName = ""; CrystalDecisions.Shared.ParameterValues par

  • geeksarray user
    05/22/2019 05:52 PM alfroy

    Thanks good man.

Blog Search





If you like my content please feel free to buy me coffee. Buy Me A Coffee