Dynamically provide parameter values on a scheduled report in Microsoft Reporting Services

For those who have already discovered the power of Microsoft reporting services in formatting, summarizing reports now may wonder how to make use of the report subscriptions.

Simple subscription offers setting the report parameters statically and a schedule to run on a predetermined time with those parameters set. But for setting the parameters dynamically you may be wondering going to data-driven subscriptions.

Before diving into let me remind you that when you are scheduling a report you can leave some parameters unspecified those have default values set. reporting service will take those default values for running the report on the scheduled time.

Change the order of report parameters when you have a dependent parameter that is listed before the parameter it is dependent on. Parameter order is important when you have cascading parameters, or when you want to show users the default value for one parameter before they choose values for other parameters. A dependent report parameter contains a reference, in either its default values query or valid values query, to a query parameter that points to a report parameter that is after it in the parameter list in the Report Data pane.

In report builder, the order that you see parameters display on the report viewer toolbar is determined by the order of the parameters in the Report Data pane.

To change the order of report parameters

• In the Report Data pane, expand the Parameters node.

• Click a parameter and use the up and down

By using these powerful two features of Reporting service, here is a case for a brokerhouse firm application where you can avoid data-driven subscription by only using cascaded parameters:

Suppose you have a report with three parameters:

1. clientId
2. fromDate
3. toDate

Which shows trading report from and to the specified date for the specified clientId.

Now you want to schedule the report to show last 5 days or only last day's trades to a

client.

For this, you can take another parameter named NoOfDaysBackward

Note the order of parameters is important

1. clientId
2. NoOfDaysBackward
3. fromDate
4. toDate

Set from parameter fromDate properties->default values->specify values with expression

=DateAdd("d", -Parameters! NoOfDaysBackward.Value, Today())

And set toDate

=Today()

Also set NoOfDaysBackward default value to like 5. This will spare you from specifying explicitly the value of NoOfDaysBackward when you are only providing clientId, fromDate and toDate values.

Now you can schedule the report by only setting clientId and NoOfDaysBackward(may be omitted because it has a default value).