Post
Ask Question

Crystal Report Using Stored Procedure

Step 1: Create 2 tables Department and Employee using below script.
  1. /****** Object: Table [dbo].[Department] Script Date: 02-12-2015 21:50:00 ******/  
  2. SET  
  3. ANSI_NULLS ON GO  
  4. SET  
  5. QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Department](  
  6. [DeptId] [int] IDENTITY(1, 1) NOT NULL,  
  7. [DeptName] [nvarchar](50) NULL,  
  8. CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED ([DeptId] ASC) WITH (  
  9. PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,  
  10. IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,  
  11. ALLOW_PAGE_LOCKS = ON  
  12. ) ON [PRIMARY]  
  13. ) ON [PRIMARY] GO  
  14. /****** Object: Table [dbo].[Employee] Script Date: 02-12-2015 21:50:24 ******/  
  15. SET  
  16. ANSI_NULLS ON GO  
  17. SET  
  18. QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Employee](  
  19. [EmpId] [int] IDENTITY(1, 1) NOT NULL,  
  20. [EmpFirstName] [nvarchar](50) NULL,  
  21. [EmpLastName] [nvarchar](50) NULL,  
  22. [DeptId] [int] NULL,  
  23. CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([EmpId] ASC) WITH (  
  24. PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,  
  25. IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,  
  26. ALLOW_PAGE_LOCKS = ON  
  27. ) ON [PRIMARY]  
  28. ) ON [PRIMARY] GO  
Step 2: Run below script to create sample stored procedure using that we will create crystal report.
  1. SET ANSI_NULLS ON  
  2. GO  
  3. SET QUOTED_IDENTIFIER ON  
  4. GO  
  5. -- =============================================  
  6. -- Author:  <Author,,Ankur>  
  7. -- Create date: <Create Date,02-12-2015,>  
  8. -- Description: <Description,,>  
  9. -- =============================================  
  10. CREATE PROCEDURE EmpDetails  
  11. AS  
  12. BEGIN  
  13. -- SET NOCOUNT ON added to prevent extra result sets from  
  14. -- interfering with SELECT statements.  
  15. SET NOCOUNT ON;  
  16. -- Insert statements for procedure here  
  17. SELECT e.EmpFirstName, e.EmpLastName, d.DeptName  
  18. FROM Department d INNER JOIN  
  19. Employee e ON d.DeptId = e.DeptId  
  20. END  
  21. GO  
Step 3: Add new Crystal Report in your project,


Step 4: Next step, select 'using the report' wizard and click 'Ok',



Step 5: Connect to SQL database and Select your Stored Procedure 'EmployeeDetails, and click 'Next'.


Step 6: Drag and drop Fields from Field Explorer to Report Details Section as shown in below screen.


Step 7: Now save and check report by clicking main report preview.


Hope you like the simple steps to create crystal report using Stored Procedure.