Figure 11-11: Defining the delivering settings to complete

3. Specify parameters values. You can indicate that parameters are determined by either specifying a static value or as values from a database. For either choice, you are presented with a drop-down list of static values as well as a drop-down list to select the default value option for each parameter you would like to use for the data-driven subscription. You can select which method to use and which static value or database value to use. This is shown in Figure 11-12. Then click Next. 4. Define the schedule and select Recipients from the options. You can select whether you want to notify recipients when the report data is updated on the report server or on a schedule created for this subscription. These options are shown in Figure 11-13. 5. Click Finish. Figure 11-12: Defining the delivery settings to complete the specification for the fileshare delivery data-driven subscription. 216 Part III: Publishing, Accessing, and Subscribing to Reports

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

Figure 11-11: Defining the delivering settings to complete

Figure 11-11: Defining the delivering settings to complete the specification for the fileshare delivery data-driven subscription. Figure 11-10: Defining the query to return the required fields to complete the specification for the fileshare delivery data-driven subscription. Chapter 11: Delivering Your Reports 215

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

creating a table-driven approach to specifying your subscriptions,

CREATE TABLE [SubscriptionFileshareReport] ( [ReportID] [smallint] [Filename] [nvarchar] (50) NULL, [FileExt] [nvarchar] (5) NULL, [Path] [nvarchar] (50) NULL, [RenderFormat] [nvarchar] (50) NULL, [UserName] [nvarchar] (50) NULL, [Password] [nvarchar] (50) NULL, [Writemode] [bit] NULL, [ReportYear] [nvarchar] (50) NULL, [ReportMonth] [nvarchar] (50) NULL, [EmpID] [nvarchar] (10) NULL ) Then in the data-driven subscription process 3 for a report with a ReportID of 20, you can simply use the SQL statement: SELECT * FROM SubscriptionFileshareReport WHERE ReportID = 20 Fileshare delivery of data-driven subscriptions Beginning with Step 5 of the data driven subscription process, I review the steps to complete a fileshare subscription delivery. For this method, the server will refresh the report located somewhere on the network directory that is usually referenced by some intranet page hyperlink to display the report. Follow these steps: 1. Define the query for the fileshare delivery method. You should enter a query that will return all the key fields required for the remaining specification of the subscription. If you use the definitions outlined in the previous section, you can enter the query SELECT * FROM SubscriptionFileshareReport WHERE ReportID = 20. This is shown in Figure 11-10. Then click Next. 2. Define delivery settings. Configure the properties required by the fileshare data driven specification as shown in Figure 11-11. All the values you need to provide come from the database and should be available from the drop-down list box for each field you need to specify. Then click Next. 214 Part III: Publishing, Accessing, and Subscribing to Reports

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Inexpensive Web Hosting services

creating a table-driven approach to specifying your subscriptions,

creating a table-driven approach to specifying your subscriptions, you can merely edit (or modify) the subscriptions information table to make any changes in the process rather than editing the subscription definition. Furthermore, this is the only technique for truly specifying multiple parameter values across a diverse group of users requiring unique views (and even rendering formats) of a single report. As an example, a data-driven subscription that will send an e-mail on a schedule basis to a variety of users requires the following information: To e-mail address, CC address(es), BCC address(es), ReplyTo address, include the report flag, rendering format specification, priority, subject text, comment text, include a link flag, send e-mail to a user alias flag and the parameters appropriate to the report. One approach is to create a table for which you can populate all these parameters to ensure good maintainability. The create table statement could be as follows: CREATE TABLE [SubscriptionEMailReport] ( [ReportID] [smallint] [To] [nvarchar] (50) NULL, [CC] [nvarchar] (50) NULL, [BCC] [nvarchar] (50) NULL, [ReplyTo] [nvarchar] (50) NULL, [IncludeReport] [bit] NULL , [RenderFormat] [nvarchar] (50) NULL, [Priority] [bit] NULL, [SUBJECT] [nvarchar] (50) NULL, [COMMENT] [nvarchar] (50) NULL, [IncludeLink] [bit] NULL , [ReportYear] [nvarchar] (50) NULL, [ReportMonth] [nvarchar] (50) NULL, [EmpID] [nvarchar] (10) NULL ) This provides the delivery extension methods for all reports like the Employee Sales Summary report where there are three parameters (ReportYear, ReportMonth, and EmpID). Then in the data-driven subscription process for a report with ReportID of 10, you can simply use the SQL statement: SELECT * FROM SubscriptionEmailReport WHERE ReportID = 10 For a fileshare delivery, the expected columns are FILENAME, FILEEXTN, PATH, RENDER_FORMAT, USERNAME, PASSWORD, WRITEMODE, and the parameter values. The Create Table statement for this delivery method would be as follows: Chapter 11: Delivering Your Reports 213

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Inexpensive Web Hosting services

A data-driven subscription provides a way to deliver

5. Select a shared data source to use with the report. Expand the folders to find the shared data source appropriate to your report. For the Employee Sales Summary report, you would select the shared data source AdventureWorks, as shown in Figure 11-9. 6. Click Next. 7. Create a data-driven subscription. Based upon the selection of the method of delivery, you will have a different sequence of steps to complete the data-driven subscription setup. I consider each option in the sections that follow. Supporting a fluctuating list of subscribers You can effectively support the wide distribution of a report with a fluctuating list of subscribers. For example, you can use data-driven subscriptions to distribute a report throughout a large organization where subscribers vary from one month to the next, distribute a report on retirement benefits to all employees who are retiring at the end of the month, or use other criteria that determines group membership from an existing set of users. In order to best support data-driven subscriptions for a changing subscriber list, you should consider creating a table that can be queried for particular subscriptions to provide all the parameters required to get your information in the hands of the people who need it quickly when the information is available. By Figure 11-9: Navigating to a folder in Report Manager containing the shared data source for a report. 212 Part III: Publishing, Accessing, and Subscribing to Reports

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Clan Web Hosting services

A data-driven subscription provides a way to deliver

A data-driven subscription provides a way to deliver reports to a list of subscribers that is determined when the subscription executes. You can use data-driven subscriptions to customize report output for each recipient of a delivery. A data-driven subscription gets some settings from a data source at run time, and other settings from the subscription definition. Fixed aspects of a datadriven subscription include the report that is delivered, the delivery extension, connection information to an external data source that contains subscriber data, and a query. Dynamic settings of the subscription are obtained from the row set produced by the query that includes the subscriber list and userspecific delivery extension preferences or parameter values. This data is retrieved from a data source each time the subscription is processed. To create a data-driven subscription, follow these steps: 1. In Report Manager, navigate to the report for which you want to create a subscription and then click the report to open it. 2. Click the Subscriptions tab. If the report you selected uses a data source configured to use stored credentials or one requiring no credentials you will see a New Data Driven Subscription button. Click this button. 3. Select the method of delivery. You can select from three options: Report Server File Share, Report Server E-mail, or Null Delivery Provider, as shown in Figure 11-8. 4. Click Next. Figure 11-8: Create a data-driven subscription page. Chapter 11: Delivering Your Reports 211

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Clan Web Hosting services

3. Select the subscription processing option. You can

5. Click OK. You get a subscription and return to the Subscriptions tab with your subscription listed on the page, as shown in Figure 11-7. If you create a scheduled subscription for a report that runs as a snapshot, the report will be delivered at the time you specify in the subscription, but the snapshot delivered will be based on the most recent refresh of the snapshot. For example, if a Report Server administrator specifies that a report snapshot is refreshed at midnight, subscriptions that schedule that same report for 6 a.m. receive the report that was generated at midnight. Creating Data-Driven Subscriptions You may notice the Data-driven Subscription button on the toolbar. Datadriven subscriptions deliver a single report in many rendered formats to many destinations. They are dynamic in that the values used for specifying presentation, delivery, and parameter values are retrieved at run time from a data source, and then used to complete subscription processing. You can use data-driven subscriptions if you have a very large recipient list (for example, all employees in an organization), or if you want to vary report output for each recipient. Data-driven subscriptions generate subscription data from a data source. To use data-driven subscriptions, you must have expertise in building queries and an understanding of how parameters are used. Report server administrators typically create and manage these subscriptions. Figure 11-7: See subscriptions defined for a report on the Subscriptions tab. 210 Part III: Publishing, Accessing, and Subscribing to Reports

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Clan Web Hosting services

3. Select the subscription processing option. You can

3. Select the subscription processing option. You can choose between when the report is refreshed (for snapshots only) and processing according to a schedule that you either create or specify a shared schedule. a. For snapshots when report content is refreshed. If you prefer this method, click the radio button to indicate the that you want to run the subscription when the report content is refreshed. This subscription processing option is available only for snapshots that are already associated with an update schedule. b. Scheduled Subscription. If you prefer this method, click the radio button to creating a schedule; then click the Select Schedule button and define a specific delivery schedule. This will bring up the Schedule page as shown in Figure 11-6. The schedule used to update a report snapshot determines when your subscription is processed. For reports that run only on demand or from cache, schedules are the only option for subscription definition. 4. Specify any parameters required by the report. For reports with parameters, you must specify the values to provide the parameters when the subscription executes this report. Figure 11-6: Subscription schedule page for specifying on what regular basis a subscription report will run. Chapter 11: Delivering Your Reports 209

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Clan Web Hosting services

To create a standard subscription, follow these steps:

Figure 11-5: New Subscription page in Report Manager to define a Report Server Fileshare to be delivered for the report. Figure 11-4: New Subscription page in Report Manager to define a Report Server E-mail to be delivered for the Employee Sales Summary report. 208 Part III: Publishing, Accessing, and Subscribing to Reports

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Inexpensive Web Hosting services

To create a standard subscription, follow these steps:

To create a standard subscription, follow these steps: 1. Click the New Subscription button. The New Subscription page appears, prompting you to select a method of delivery (see Figure 11-3). 2. Select a method of delivery. You have choices of e-mail or file share delivery (or Null delivery for report snapshots). E-mail delivers an e-mail, whereas file share deposits a file on a shared directory. Choose one of the options in the following list based on the delivery method of the subscription: Complete the delivery specification for the e-mail subscription. The e-mail delivery subscription page is shown in Figure 11-4. You will note that you need to specify the key information for an e-mail (which user, who is copied or blind copied, the text of the e-mail, and how the file is added to the message). Complete the delivery specification for a Fileshare subscription. The fileshare subscription page is shown in Figure 11-5. Options you need to define include the filename, the path at which the file should be saved, the credentials used to access the file, and any overwrite options. Complete the delivery specification for a NULL delivery. This amounts to caching a snapshot. Figure 11-3: New subscription page in Report Manager showing the options for creating a standard subscription for the Product Line Sales report. Chapter 11: Delivering Your Reports 207

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Inexpensive Web Hosting services