Create Cross Tab Report Using RDLC And Crystal Reports

Ankur Mistry

.NET Crystal ReportsRDLCCross Tab Report

14 Comments

Join the conversation! Your thoughts help the community grow.

Pradeep Sahoo10y ago
Nice article...Thanks for sharing....
Sonu Chaudhary10y ago
nice share
Sneha Purohit10y ago
Useful stuff
Ankur Mistry10y ago
thank you all
Rushabh Parekh10y ago
Awesome Article must say very good information
Nikhil Rajput10y ago
Nice one...........
Harish Agrawal10y ago
Nice Article...
sagar nathe10y ago
Nice article....Sir...
sagar nathe10y ago
Nice article....Sir...
Ankur Mistry10y ago
Thank you, Mannan Bahelim, MaYank ShArma, Dhrumit Patel, Rupesh Kahane
Rupesh Kahane10y ago
Good one
Former member10y ago
Nice article....Sir...
Mayank Sharma10y ago
Good knowledgeable Post on Report...
Mannan10y ago
nice article ...

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,

/****** Object: Table [dbo].[Department] Script Date: 07-12-2015 23:33:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Department](
[DeptId] [int] IDENTITY(1,1) NOT NULL,
[DeptName] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Employee] Script Date: 07-12-2015 23:33:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employee](
[EmpId] [int] IDENTITY(1,1) NOT NULL,
[EmpFirstName] [nvarchar](50) NULL,
[EmpLastName] [nvarchar](50) NULL,
[DeptId] [int] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Salary] Script Date: 07-12-2015 23:33:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Salary](
[EmpId] [int] NULL,
[Salary] [numeric](18, 2) NULL
) ON [PRIMARY]
GO

Step 2: Run the following script to create sample stored procedure using that we will create report.

CREATE PROCEDURE [dbo].[EmpDetails]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT e.EmpFirstName, e.EmpLastName, d.DeptName,s.Salary
FROM Department d INNER JOIN
Employee e ON d.DeptId = e.DeptId
INNER JOIN [dbo].[Salary] s
ON e.EmpId = s.EmpId
END
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.