Step By Step Creating Grouping In Crystal Report

Background

In this article we will learn about creating Wizard through Crystal Report and applying grouping and display records.

Step 1: Create table ‘Department’.

CREATE TABLE [dbo].[Department](
    [DeptId] [int] IDENTITY(1,1) NOT NULL,
    [DeptName] [nvarchar](50) NULL,
 CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED
(
    [DeptId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Create table ‘Employee’.

CREATE TABLE [dbo].[Employee](
    [EmpId] [int] IDENTITY(1,1) NOT NULL,
    [EmpFirstName] [nvarchar](50) NULL,
    [EmpLastName] [nvarchar](50) NULL,
    [DeptId] [int] NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
    [EmpId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Department Table



Employee Table


Step 2: Open Visual Studio and create Crystal Report.



Step 3: Select Report Wizard and select ‘Standard’ Report and click Ok button.

Enter server name, User ID, Password and select database.



Step 4: Click on Next and select tables ‘Employee’ and ‘Department’ from Standard Report Creation Wizard.

Select both the tables and click ‘>’ button.





Step 5: Now Map ‘DeptId’ (Foreign key) that is common in both the tables and click next.





Step 6: Select the fields you want to Display in Reports (Here I have Selected DeptName, EmpFirstName and EmpLastName).



Next I will implement Grouping on Department.

Step 7: Select Department name wise and in ascending order. After that click on ‘Finish’ button.



So it will automatically create Crystal Report same as in the following screen.



On clicking on Main Report Preview it will bind data from the DataTable.



When you bind this report in your project, please remove the check ‘Save data in the report’.

Otherwise it will not refresh the actual data. It will show saved data in reports.

Step 8: Now I will format the reports and combine EmpFirstName and EmpLastName in 1 field.
Just drag and drop DeptName and place it on groupHeader section, so it will display Department name only once.



Step 9: Now from the Field Explorer, right click on Formula field and give name ‘EmpName’.

Now from the formula Workshop, select Employee table and select EmpFirstName and EmpLastName field and concat it in a single name ‘EmpName’.



Now click on Alt + C for checking errors like the following screen,





Step 10: Delete EmpFirstName and EmpLastName from the Report and place ‘@empName’.

Right click and suppress unnecessary section, and place @Empname field in Details section.

Just little formatting and your DeptWise employee report is ready.