Background

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

Step 1: Create table ‘Department’.

  1. CREATE TABLE [dbo].[Department](
  2. [DeptId] [int] IDENTITY(1,1) NOT NULL,
  3. [DeptName] [nvarchar](50) NULL,
  4. CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED
  5. (
  6. [DeptId] ASC
  7. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  8. ) ON [PRIMARY]
Create table ‘Employee’.
  1. CREATE TABLE [dbo].[Employee](
  2. [EmpId] [int] IDENTITY(1,1) NOT NULL,
  3. [EmpFirstName] [nvarchar](50) NULL,
  4. [EmpLastName] [nvarchar](50) NULL,
  5. [DeptId] [int] NULL,
  6. CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
  7. (
  8. [EmpId] ASC
  9. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  10. ) ON [PRIMARY]
Department Table

Department Table

Employee Table

table
Step 2: Open Visual Studio and create Crystal Report.

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.

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.

Standard Report Creation Wizard

Department

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

DeptId

Foreign key

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

Select the fields

Next I will implement Grouping on Department.

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

Select Deptname Wise

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

Main Report

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

Main Report Preview

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.

Drag and Drop DeptName

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’.

Workshop

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

errors

checking for errors

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.

report