Manage SSRS Report Parameter(s) and Report Subscription in SharePoint Integrated Mode – Part 1

As promised in my previous article, we shall spend next few minutes on few good brownies that are available in SharePoint Integrated Mode SQL Reporting Services 2008  – Report Parameters and Subscription model. Before we start, would like to thank my good old friend Ashok for pushing me to do this article with his honey coated comments in my previous article.

In Part 1 of this series, we’ll delve deep in to Report Parameters by exploring few interesting options.

Report Parameters – SSRS in SharePoint Integrated Mode

Parameters are used when data needs to be narrowed down. Not all the data source in SSRS support parameters, in such scenario we can use Report Parameters to help users at Report level. These report parameters can be managed after being published based on how it is configured. There are many ways to narrow down, lets categorize it by: Smart, Smarter and Smartest.

Smart mode

• Add Report Parameter Properties in BIDS, which in-turn will display a user prompt at runtime to capture the parameter input

• Can auto generate the report with predefined input parameters & later user can be allowed to change the input parameter and regenerate the report. This can be achieved by selecting Specify Values or Get Values from a query of Default Values section or by selecting Specify Values in Available Values tab. This mode will be used often in SSRS subscription mode

Smarter mode

• Cascading parameters can be used when multiple query parameters are used. For example, lets consider having Specialization, Company Name and Contact Name, where Contact Name is dependent on Company Name and Company Name dependent on Specialization. In this scenario, when a user selects a value in Report Parameter Specialization, values in Company Name report parameter will be populated based on Specialization selection and values in Contact Name report parameter will be populated based on Company Name selection.

To generate a report with cascading style parameters, follow these steps:

1. Dataset for the report body with three parameters defined

2. Dataset for Specialization with Specialization list column in the query & no parameters defined

3. Dataset for Company Name with Specialization and Company Name in the query and one parameter i.e. Specialization

4. Dataset for Contact Name with Specialization, Company Name and Contact Name in the query with two parameters i.e.Specialization and Company Name

5. Define three parameters in the Report with dependency on the parameters mapped

6. Define the Filters in the report by using the above defined parameters

 

Dataset for the report body with three parameters defined

 

For this sample we’ll pick the data from SharePoint list. Have used the Query Designer to design the below query

Define three parameters in this dataset

Dataset for Specialization with Specialization list column in the query and no parameters defined

Define the query

 

Dataset for Company Name with Specialization and Company Name in the query and one parameter i.e. Specialization

Define the Query

Define the Parameter in this dataset

 

Dataset for Contact Name with Specialization, Company Name and Contact Name in the query with two parameters i.e.Specialization and Company Name

Define the Query

Define the Parameter in this dataset

 

Define three parameters in the Report with dependency on the parameters mapped

· Specialization with no mapping as this is the first level parameter

· Company Name with Default Values mapped to Specialization

· Contact Name with Default Values mapped to Company Name

 

Define the Filters in the report by using the above defined parameters

Now we are ready to run the report & see how the cascading falls in-place. Specialization filter will have a dropdown with values from dsSpecialization dataset populated and on selecting a value; Company Name will get populated based on Specialization value & same with Contact Name

After choosing values from all three filters, click on View Report. Report will be generated based on the chosen filter values

 

This completes our Part 1 series, very soon we can dig much deeper & see the Smartest mode of Report Parameters and spent few minutes on Report Subscription.