Paginated Reports eBook Introduction, Resource and Sample Data Downloads
The Microsoft Word function – Mail Merge – is a well-known function, which will email form letters to multiple recipients. Using this function, you can easily customize form letters for individual recipients in bulk. Paginated reports support this function as well, and it will help you deliver customized reports to email recipients. In this manner, you will be able to personalize content per recipient and send this content to a recipient (or multiple recipients).
Data-driven subscription in Reporting service will be the key element to successfully delivering the customized Paginated report content to recipient email.
Recipe Ingredients
Product Versions (one of the following):
- SQL Server Reporting Services 2016 (on-prem) or SQL Server Power BI Report Server 2016 (on-prem) or newer
- SQL Server 2016 or later
What You’ll Need
- Connection to SQL Server database
- SQL Server Paginated reports service
- Permissions to create SSRS subscription
- Mailing set in Report server configuration
- Moderate SQL Skills
Difficulty Level: 200
Contributed by Tomaž Kaštrun
Creating Customized Paginated Report
Generating sample dataset and publishing report
Using your SQL Server Database, create this exemplary datasets and populate them with sample data for this demo.
Open SQL Server Management Studio and open new query window.
Open Chapter5_SendingCustomizedPaginatedReports.sql file and it will guide you through the process.
Query will create ETL database and populate two tables dbo.PurchaseOrder and dbo.PurchaseCustomer.
In table dbo.PurchaseCustomer you can alter the emails, so that you will receive the emails:
INSERT INTO dbo.PurchaseCustomer SELECT 33,'Tomaz Gmail','Tomaz.xxxxxyyyy@gmail.com' UNION ALL SELECT 52,'Tomaz Outlook' ,'Tomaz.xxxxxxyyy@outlook.com' UNION ALL SELECT 67,'Tomaz TSQL','Tomaz.xxxxyyyy@gmail.com'
Open file SendingCustomizedPaginatedReports.rdl in Report Builder.
Figure 1-1 shows the Report which has a Customer parameter set (will be used later on for personalized content sending).
Figure 1-1. A sample report for personalized sending
Edit the Data Source, called myDatabase and edit the Connection string:
Data Source=SNTK;Initial Catalog=ETL
Use the Save As… option on the File menu to save the report to a folder on your
report server (e.g.: https://sntk/ReportServer) where you have access to.
Hit save and close the Report Builder
Note: Depending on the Report Builder menu option and deployment target, the terms “save” and “publish” are used interchangeably, meaning that the report has been deployed to a server or service where it can be ran and viewed by users.
Setting up data-driven subscription on Report Server
- Open Report server where your report is published.
Figure 1-2 shows the published report as Paginated report on your Report Server.
Figure 1-2. Published paginated
With your mouse hover over Report click the ellipsis (…). Select Manage option. A report properties page will be opened.
Go to Data sources. Double check if the connections to your SQL Server database is working.
In addition to data sources, click also on Security and check if the user, you are running and browsing the report, has sufficient roles assigned.
Click on »+ New Subscription« as shown in figure 1-3.
Figure 1-3. Adding new subscription to paginated report
Note: Depending on your granted access and roles, creating new subscription might be disabled. If the button in disabled, you should contact your administrator to grant you privilege. Each subscription creates a SQL Server job that is triggered based on the schedule you define in subscription. This is why, it is important to have access granted.
Give the subscription name. Under Description type “Customer personalized data-drive subscription”
Under section Type of subscription, select the Data-driven subscription radio button.
Site puts focus automatically on section “Dataset”.
Figure 1-4 shows the dataset section, where you will specify how your data-driven subscription will use dataset. In addition, fields marked with exclamation mark (marked with red border) are mandatory and you must define them. All others are optional, but should be defined, as these fields will define how your email will look.
Figure 1-4. Adding new subscription to paginated report
Click “Edit Dataset” and you will get a new screen to define data source.
Figure 1-5 shows all the fields that can be defined with as part data-driven subscription for sending paginated reports on email.
Figure 1-5. Defining the data-driven dataset
In this case, you will select “A Custom data source” radio button.
Under section “Connection” use:
| Type | Microsoft SQL Server |
| Connection String | Data Source=SNTK;Initial Catalog=ETL |
| Credentials | Type: Windows (or database if you have different setup)
UserName: type your username Password: your password |
Under section “Query” you can copy paste the query
SELECT C.C_name AS CustomerName ,C.C_Email AS CustomerEmail ,C.C_ID FROM dbo.PurchaseCustomer AS C GROUP BY C.C_name ,C.C_Email ,C.C_ID
Click validate query and click apply.
Now you have to map the parameters for report with columns from the query you have inserter in step 11. With this mapping, the fields will be populated directly from the dataset, because we want the report server to loop over the list of emails. Mandatory fields (as marked with exclamation mark) are also seen in Figure 1-4.
| Parameter | Source of Value | Value/Field |
| To | Get value from dataset | CustomerEmail |
| Customer | Get Value from dataset | C_ID |
Note: Data fields must have same data type and should be using same collation The names of the parameters in paginated report (RDL file) can differ from the query where you will get the list of emails, because these fields are mapped.
Click “Create Subscription” and wait for the subscription to be created.
Edit this same subscription to change the schedule. Under the schedule, click edit schedule, and define how often (Hour, Day, Week, Month, Once) you want this subscription to be triggered. After setting this, click apply.
Check if the delivery method – under “Destination” is selected as “E-mail”. At the bottom, click Apply once again.
Run the report and check your mailbox
To run the subscription, check the subscription and click »Run now«.
Depending on your list of recepients, it might take a while to process all the email. In your case, this should be fairly quick task to accomplish. In the result column for the subscription, you should get same result set as shown on Figure 1-6.
Figure 1-6. Result for the data-driven dataset
Emails will be generated and sent to all recipients. Figure 1-7 shows how email is viewed and shown. Also taking into consideration that the Value of orders and number of orders statistics in the report itself, also resemble the values for this particular customer (and only this customer).
Figure 1-7. Email viewed on gmail sent from Report server
Conclusion
Sending customized paginated reports to multiple emails is not only a great feature, but a must have feature for any enterprise business. This feature gives Paginated reports also a powerful ability and advantage of any other data visualization and data exploration tool.
Understanding data in this context – receiving personalized report on email – is also additional teaser and nice-to-have for any billing, CRM, sales business that do not want to use external or proprietary services, but rather extend the enterprise tools that are already available in-house.
Worth mentioning, are couple of limitations, when using this technique. One and foremost is the number of recipients, you are sending emails to. This should be consolidated with your Exchange expert to check the outbound and throughput limitations.