Fun SSRs Article 8-Advanced Report subscription

The previous article describes how to subscribe to reports in the form of shared directories in SSRS. Because SSRs has some special features in mail subscription, this article describes how to implement Report email subscription in a flexible way.

To subscribe to a report by email, we split it into three parts.

 

We use the SQL Server Agent to generate a job to trigger a process at a fixed cycle. Then, in this process, first implement an app to generate the report to a specified directory, and then call anotherProgramSends the Report to the user of the specified contact group according to the configuration file.

 

First, create an application that generates a report PDF.

The main idea of generating PDF is to add a reportviewer control with a winform application, and then generate the report in pdf format directly.

Open Visual Studio 2012 and create a winform application.

Add the reportviewer control to the form.

Then, create several tables in the database to save the configuration information of the report control and the configuration information for sending emails. The structure of the three tables can be defined according to the actual situation. In this film, reportbase mainly stores two pieces of information, reportserver and reportpath, to locate a server report. Emailbase is used to record all the user names and passwords for sending emails and the address of the SMTP server. Reportto is used to record who the email is sent.

After creating the basic table, return to the project and add a LINQ to SQL classes (of course, you can select Entity Data Model ).

Connect to the database you just created.

Drag a table with a report definition to the LINQ to SQL design interface. Here, the system generates the corresponding class based on the structure of the data table.

Return to the data table and add a row of data to the table that saves the report configuration information. The configuration of reportserver and reportpath is consistent with that of the reportviewer Control described earlier. Here we connect the reports created in the previous articles. Of course, you can select another report that you have deployed.

Return to the project and add the followingCode.

The code first reads the configuration data in the table and then assigns it to the relevant attributes of the reportviewer control (which is really useful for LINQ ).

After setting properties for a report, call the export method to make the reportviewer control generate the report content as a PDF file.

At this point, the report generation PDF is developed, and then the mail sending program is created.

The email sending program basically does not require a UI, so you can directly create a console application.

Add the LINQ to SQL file for the project, and drag the mail configuration table and the Send configuration table to the LINQ to SQL interface.

Add the following code to the main method.

After reading the configuration information, use SMTP-related classes to send emails.

After the related application is created, create the corresponding job to "string" the two programs.

Open SSMs, connect to the Data Engine service, expand SQL Server Agent, right-click the job directory, and select new job... Create a new job.

In the general interface, enter a name for the job.

On the steps page, click New to create two steps.

Place the preceding two programs in the specified directory and configure the steps of the job.

The first step of a job is to generate a PDF file and run the first program created in this chapter.

The second step of the job is to send an email according to the configuration.

The results after the two steps are configured are roughly shown in.

Next, click schedules, create a running cycle for the job, and click New.

In the new job schedule, you can see that schedule of any cycle can be generated.

After creating a job, you can manually trigger the test. Right-click the created job name and click Start job at step ....

The pop-up interface specifies the start step. Click Start by default.

After running, log on to your mailbox and you will see the sent email.

The specific content of the email and the attached report.

 

The method of mail subscription report is described above. You can add more configuration information in the project based on the actual situation. In the configuration of the recipient list, it is usually very troublesome and cumbersome to add a user as a user. Therefore, it is recommended to maintain it in the form of a contact group.

If there are parameters in the report, you can select either of the two methods. One is to set the default values for both parameters. This is the simplest method, so that the report is generated by default with the latest data. You can also add parameters to the reportviewer control by encoding. For details, refer to the previous two articles.

In addition, you can use SQL Server database mail instead of automatically sending a program. For example:

Exec MSDB.DBO.Sp_send_dbmail

@ Profile_name = 'Default',

@ Recipients = 'Microsoftbi @ 163.com',

@ Subject = 'Daily report',

@ File_attachments='D: \ reportresponse'

 

 

 

Appendix:

DEMO code and database file download:

Http://files.cnblogs.com/aspnetx/DEMO.zip