Data Driven Subscription

Note: This feature requires SQL 2008 R2 Enterprise.

A data-driven subscription will send individual reports based on the report setup. Each subscription should be configured as a separate report record in netFORUM .

Example: Invoices by Batch Report can be filtered by individual and each individual's invoice can be e-mailed to them.

If you check the Data Driven Subscription flag, you will get additional fields that will need to be populated on the Add - Report form.

Subscription Query: This will define a query that will filter the report into smaller individual report that can be sent. Data that is contained within curly braces will be populated from report parameters. The parameter name should have the same value as what is in the braces. In the screen shot above, the report would have a parameter named @bat_key which will populate {@bat_key} in the query. The query will need to return an e-mail address. This is used to determine where to send the individual reports. This can be a static e-mail or driven by data.

Map Email To: This is the e-mail address field from the subscription query.

Map Email CC: This is also mapped from the subscription query. This is an e-mail address that will be CC'd on the report. If this is not mapped, the user will have the option to choose a static value at run-time.

Map Reply-to: This can be mapped from the subscription query if a different reply to other than the user's e-mail is required. If this is not mapped, the user will have the option to choose a static value at run-time.

Map Email Subject: The subject of the e-mail can also be mapped from the subscription query. If this is not mapped, the user will have the option to choose a static value at run-time.

Report Parameter Mapping

If the report SP has parameters that need to be passed, these will need to be mapped from the subscription query. In the example above of an Invoices by Batch Report, the user would be entering a batch key. This batch key would be passed into the subscription query. Since the report is actually running individual invoices and e-mailing them to the individuals, the report SP would not require a batch key. It would most likely require an invoice key (this could also be set up with a customer key if you wanted to group the report by customer and send one file per customer instead of one per invoice).

To set up the parameters that will be passed to the report SP, there is an additional mapping that is required. This is done in the Report Parameter Mapping child form. Instructions on completing the fields are included in the Instructions drop-down.

Report Parameter: This is the name of the parameter in the report SP.

Map To Column: This is the name of the column from the subscription query.

Requirements

This is a standard feature in netFORUM , but it will only work if the Reports Server is on the correct edition. Per MSDN, this feature is only available on the Developer, Evaluation, and Enterprise editions of Reporting Services. Also, if the Reports Server is a different server from the SQL server, the license for the SQL server may also need to be upgraded.