SSRS: Mail Merge

Using SQL Server Reporting Services to generate form letters to users.

As businesses grow, so does their customer base and the need to communicate with them.
SSRS provides an easy way to automate the communication process by generated form letters utilizing user-based data from a centralized data source.

In this example, I’ll begin with a data source configured for the Northwind database.
First, I’ll drag a list item to report design surface.

1-mail-merge

Next, I’ll turn on rulers to help keep fields in the report aligned as I build it.

2-mail-merge

To begin my report, I’ll drag CompanyName into the list item.
Then, I’ll drag size of CompanyName textbox so it fills list item – now I have a large area populated with the CompanyName field and enough room to build the rest of my letter.
First I’ll pace a couple of line returns after the CompanyName field and begin typing my letter.
I’ll use the other fields to customize the contents of each letter so that it’s personalized to each customer by dragging them into the list item like I did with the CompanyName field.
When I’ve completed my letter, each customer will see the date of their order, which product they purchased along with the quantity along with my gratitude.
When I preview the report, i see letters to my customer.

3-mail-merge

As you can see, multiple records are displayed per page, but since this is a letter to be sent to users, we only want one letter to display per page.
Therefore, I’ll configure a page break to appear after each letter by selecting the group properties next to “Details” below the design surface and select to add a break “Between each instance of a group.”

4-mail-merge

Now when I preview the report, each user’s letter displays on its own page.

5-mail-merge

You may have noticed the date and time are displayed in a very unattractive format.
I’ve decided to apply a format to date values as mm/dd/yyyy.

6-mail-merge

Also, I want the database values to be displayed in BOLD.

7-mail-merge

Now when I run the report, it’s formatted perfectly.

8-mail-merge

rptCustomerOrders
(right-click, Save As)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s