Post
Ask Question

Create Cross Tab Report Using RDLC And Crystal Reports

In this article we will create Cross-Tab report using RDLC  (Report Definition Language Client-side) and Crystal reports. We can call reports using 'ReportViewer' control in ASP.NET, Winforms, etc.
 
Step 1: Create tables Department, Employee and Salary using the following script,
  1. /****** Object: Table [dbo].[Department] Script Date: 07-12-2015 23:33:33 ******/  
  2. SET ANSI_NULLS ON  
  3. GO  
  4. SET QUOTED_IDENTIFIER ON  
  5. GO  
  6. CREATE TABLE [dbo].[Department](  
  7. [DeptId] [int] IDENTITY(1,1) NOT NULL,  
  8. [DeptName] [nvarchar](50) NULL  
  9. ) ON [PRIMARY]  
  10. GO  
  11. /****** Object: Table [dbo].[Employee] Script Date: 07-12-2015 23:33:33 ******/  
  12. SET ANSI_NULLS ON  
  13. GO  
  14. SET QUOTED_IDENTIFIER ON  
  15. GO  
  16. CREATE TABLE [dbo].[Employee](  
  17. [EmpId] [int] IDENTITY(1,1) NOT NULL,  
  18. [EmpFirstName] [nvarchar](50) NULL,  
  19. [EmpLastName] [nvarchar](50) NULL,  
  20. [DeptId] [int] NULL  
  21. ) ON [PRIMARY]  
  22. GO  
  23. /****** Object: Table [dbo].[Salary] Script Date: 07-12-2015 23:33:33 ******/  
  24. SET ANSI_NULLS ON  
  25. GO  
  26. SET QUOTED_IDENTIFIER ON  
  27. GO  
  28. CREATE TABLE [dbo].[Salary](  
  29. [EmpId] [int] NULL,  
  30. [Salary] [numeric](18, 2) NULL  
  31. ) ON [PRIMARY]  
  32. GO  
Step 2: Run the following script to create sample stored procedure using that we will create report. 
  1. CREATE PROCEDURE [dbo].[EmpDetails]  
  2. AS  
  3. BEGIN  
  4. -- SET NOCOUNT ON added to prevent extra result sets from  
  5. -- interfering with SELECT statements.  
  6. SET NOCOUNT ON;  
  7. SELECT e.EmpFirstName, e.EmpLastName, d.DeptName,s.Salary  
  8. FROM Department d INNER JOIN  
  9. Employee e ON d.DeptId = e.DeptId  
  10. INNER JOIN [dbo].[Salary] s  
  11. ON e.EmpId = s.EmpId  
  12. END  
  13. GO  
Step 3: Create new Report application project from reporting template as in the following screenshot and give a name 'ReportsApplication1'.
 


Step 4: Select Database from the Data Source Configuration Wizard and click 'Next'.
 
 
Step 5: It will ask to select 'Dataset' or 'Entity Data model' and we will use 'Dataset' here.
 
 
 
Step 6: Add your SQL Server database connection and click 'Ok'.
 
 
 
Step 7: Select 'Empdetails' Stored Procedure which we have created previously and Click 'Finish'.
 
 
 
Step 8: In Report wizard click 'Ok'.
 
 

Step 9: From the fields we will add 'EmpFirstName' and 'EmpLastName' field in Row Group Field which will be displayed in Report's row area.
We will add DeptName in Column area and fields like salary we will add in Values area. 
 
 

Step 10: Now in next step select layout and Style from as in the following screenshot,
 
 
 
 
 
Step 11 : Now run the report and check the output, it will display Name in Row Area, Department Name in Column Area and Salary in Data Area with Namewise Total and Deptwise Total.

 
Step 12 : Now add Crystal Report in your Project as in the following screenshot,
 
 
 
Step 13 : Select Report wizard and choose Cross-Tab from the Choose an Expert and click 'Ok'.
 
 
 
Step 14 : Complete the SQL database connection as in the following screenshot,
 
 
Step 15 : Now select the stored procedure 'Empdetails' from the Cross-tab Report Creation Wizard.
 
 
 
Step 16: 

We will add 'EmpFirstName' and 'EmpLastName' field in Rows Field which will be displayed in Report's Rowarea. A
dd DeptName in Column area and fields like salary we will add in Values Area and click on 'Next'.
 
 
 
Step 17 : We are not going to use any chart now, so please select No Chart option and click 'Next' button.
 
 
 
Step 18 : We can add 'deptname' in 'Fielterfields' and click on finish.
 
 
 
Step 19 : Select Crystal Cross-Tab Report style from available style, we will use Original here.
 
 
Step 20 : Click on 'Finish' and run the report by clicking on 'Main Report Preview' tab. 
 
 
It will display 'Empname' in row area, 'DeptName' in column area and summary in Footer section.
 
Hope you liked this article.


Similar Articles