This section describes the steps to design
Mail Merge Report using SSRS list report item.
To present data in the list format, create a dataset and bind data to the list data region. In this designing section, the following dataset query is used for dataset creation.
SELECT Emp.Photo as Photo, Emp.FirstName, Emp.LastName, (DATENAME(WEEKDAY,Emp.BirthDate)+', '+ DATENAME(DAY,Emp.BirthDate)+' '+DATENAME(MONTH,Emp.BirthDate)+' '+DATENAME(YEAR,Emp.BirthDate)) as BirthDate, Emp.Address, Emp.Title, Emp.Region, Emp.City, Emp.Country, Emp.TitleOfCourtesy, Rep.FirstName as ReportingFirstName, Rep.LastName as ReportingLastName, (DATENAME(WEEKDAY,Emp.HireDate)+', '+DATENAME(DAY,Emp.HireDate)+' '+DATENAME(MONTH,Emp.HireDate)+' '+DATENAME(YEAR,Emp.HireDate))as HireDate, Emp.Notes, Emp.HomePhone FROM Employees Emp INNER JOIN Employees Rep ON Rep.EmployeeID=emp.ReportsTo
Refer Create Data section and create dataset using the above query.
Northwind database is used here.
DataSetdrop-down list choose the required dataset.
Once you drop the list item, the
Table renders with a single cell in a row associated with the detail group in the design area. By default, the cell contains a rectangle that acts as a container.
The cell in a list contains a rectangle. You can replace a default report item in a cell with any other report item.
In list data regions, you can place the report items anywhere instead of being limited to a table layout. To create a mail merge template place the images, rectangles and textboxes inside of the list report item in a free-form manner.
Photofield from the database to the image item.
FirstNamefield from the dataset.
LastName field and create another expression. Then, customize the text appearance using the textbox properties in properties panel.
Refer Display dynamic text using expression section to assign expression in textbox report item.
Enable the report
Header to add a title to the report.
Refer Show or hide header and footer section to add or remove header/footer in the report
A final design of Mail Merge Report will look like below.
On report preview, the mail merge report will be displayed like below,
Download the above report design from the link