Search results

Design ssrs rdl report using list

This section describes the steps to design Mail Merge Report using SSRS list report item.

Create dataset

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.

Configure a list

The list report item is listed in the item panel under the Data Regions category.

List item listed in item panel 2. Drag and drop the list report item into the design area from the item panel. Drag and drop list report item into design area 3. Respective list properties will be listed in the properties panel. List item with properties view 4. In the DataSet drop-down list choose the required dataset. Assign dataset

Initial design

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.

List basic view

The cell in a list contains a rectangle. You can replace a default report item in a cell with any other report item.

Add report items

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.

Drag and drop two rectangle report items inside the list, to display the employee image and personal details side-by-side. Resize the list report item width and height to the required size.

Add rectangle item 2. Then, drag and drop the image item in the left rectangle to display the employee image in the report. Add image item 3. Bind the Photo field from the database to the image item. Bind data to the image 4. To display the employee name, add textbox in the bottom position of image report item. Add textbox item 5. Now, right click in the textbox and click on Expression option. 6. In the expression editor, choose FirstName field from the dataset. Bind data to the textbox 7. Similarly, assign LastName field and create another expression. Then, customize the text appearance using the textbox properties in properties panel. Bind data field to the textbox

Refer Display dynamic text using expression section to assign expression in textbox report item.

To display other employee details place other report items in the list item. In the below snap, the employee information are placed in the textboxes and the values are bound as expression to fetch dynamic data from the database.

Mail merge template 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.

Report header

Enable the report Header to add a title to the report.

Enable header tag

Refer Show or hide header and footer section to add or remove header/footer in the report

Now, add a rectangle report item in the report header area and a textbox within the rectangle.

Add report items in header area 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.