Crystal Report with SQL Stored Procedure Parameter and Visual Studio

This article will describes you about 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 default version of VS2010. You will have to download and install it. You can download Crystal Report for Visual Studio.

For this tutorial I am using VS 2010, SQL Server 2008 R2. I use 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 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 below steps to create Stored Proc and Crystal report to show in 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 report. You can add those customers from Northwind database by creating SQL commands or add some static ListItems with similar values to 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
    Northwind Customer Order report by dotnetmentors.com
  2. Create SQL Server Stored Procedure

    In this step we will create a store procedure which returns Customer Order details from Northwind database. Open your SQL Server and Northwind database and use below query or click here to download 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 Total of 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.
    If you do not have Crystal Report installed Click here to install. Add New Crystal Report to ASP.Net Web Application

    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.

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

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

    Next screen is Summaries select fields as per 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 designing. You may delete all the fields that are auto generated on report by 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 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 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

Download source code



Latest Jobs in


Web Developers - Client of Talent Transformers
Multiple openings for .NET developers (Net, ASP.Net,C#,SQL Server) with 4 to 8 years of ... Position Title .NET developers (Net, ASP.Net,C#,SQL...
 
Web Developers - S2C Consulting Pvt Ltd
, SharePoint Developer, SharePoint 2007, SharePoint 2010, SharePoint 2013, Office 365 App, NET framework, ASP.NET ...
 
Computer Software Engineers, Applications - CADDY CODE SOLUTIONS PVT LTD
timelines are met. Expertise: ASP.Net 2.0 and above with C#, Microsoft SQL Server 2008, XML, XHTML, and CSS ... projects, excellent command on C#,...
 
Computer Software Engineers, Applications - CerebralLink Limited
, SQL Plus, K Shell, C Shell, Perl, Cognos, BusinessObjects, MicroStrategy, Tableau, Pentaho, JasperSoft ...
 
Computer Software Engineers, Applications - Teamware Solutions Pvt Ltd
Skills Required: PL/SQL, Oracle 11g, Good understanding of Data warehousing concepts; Unix ...






Latest Blogs on Dotnetmentors.com


Implementing Finalize and Dispose(.NET Framework) in C#
This article describes about implementing between dispose and finalize methods for
 
User Defined Functions in SQL Server
This article talks about user defined functions (UDF) in SQL Server, its benefits, type of UDF and UDF parameters.
 
IIS settings to improve ASP.NET Website Performance
This article helps you to understand required IIS settings to improve ASP.NET Website Performance.
 
ASP.NET Website Performance Improvement Tips
This article give you some of the best tips to improve your ASP.NET Website performance.
 
Getting started with ASP.NET Web API
This article helps you to understand ASP.NET Web API concepts


Speak your mind :
Leave a comment for this article on dotnetbloogers.com
User profile picture on dotnetmentors.com

by jsk at 2/22/2014 12:29:00 PM
hi
post comment
User profile picture on dotnetmentors.com

by jsk at 2/22/2014 12:30:00 PM
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
post comment
User profile picture on dotnetmentors.com

by brandonteohno1 at 2/23/2014 3:12:00 PM
hi jsk, http://www.it-sideways.com/2011/10/how-to-disable-parameter-prompt-for.html 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
post comment