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.
2. Drag and drop the list report item into the design area from the item panel.
3. Respective list properties will be listed in the properties panel.
4. In the
DataSet drop-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.
2. Then, drag and drop the image item in the left rectangle to display the employee image in the report.
3. Bind the
Photo field from the database to the image item.
4. To display the employee name, add textbox in the bottom position of image report item.
5. Now, right click in the textbox and click on
6. In the expression editor, choose
FirstName field from the dataset.
7. Similarly, assign
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.
9. On report preview, each employee details will be displayed as list. To separate the each employee information you can drag and drop a line report item in the inside bottom position of the list cell.
Headerto add a title to the report.
Refer Show or hide header and footer section to add or remove header/footer in the report
3. Set the report title text in the textbox and customize the appearance of the title using the textbox and rectangle properties in properties panel as required.
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