SharePoint Pals
 | Sign In
Manage SSRS Report Parameter(s) and Report Subscription in SharePoint Integrated Mode – Part 1
by Moses Pakkianathan 30 May 2013
Author
SharePoint Consultant
Visits   
Today  :   5     Total  :    19846

 

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

image

 

  • 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

image

 

 

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

image

Define three parameters in this dataset

image

 

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

Define the query

image

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

Define the Query

image

Define the Parameter in this dataset

image

 

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

image

Define the Parameter in this dataset

clip_image001

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

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

clip_image001[4]

· Company Name with Default Values mapped to Specialization

clip_image002

clip_image003

· Contact Name with Default Values mapped to Company Name

image

 

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

clip_image001[6]

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

clip_image002[4]

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

clip_image003[4]

 

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.

blog comments powered by Disqus

SharePoint Pals

Pals
SharePoint Pals, a community portal for SharePoint developers, Administrators and End Users. Let's join hands and share the point together.
Read this on mobile

Training

Angular Js Training In Chennai
Advanced Angular Js training with real world developer scenarios
Angular Js, Web Api and Ionic for .Net Developers
All in one client side application development for .Net developers
Angular Js For SharePoint Developers
Get ready for the future. Its no more just C#

Get Connected

SharePoint Resources

SharePoint 2013 and 2010 Web Parts
Free Web Parts with Source Code for SharePoint Community




SharePoint 2013 Books and Tutorials
Collection of free SharePoint 2013 books and tutorials (eBooks, pdfs)

Supported By

Contribute your article and be eligible for a one month Free Subscription for Plural Sight. The Author of the most popular New Article (published in the previous month) will be awarded with a Free One month Plural Sight Subscription. Article can be sent to articles@sharepointpals.com in a word document.

Related Resources

Recent Tweets

Twitter January 15, 00:25
How To Enable Target Value And Actual Value In #D3 Gauge Chart https://t.co/VxSi4QnNrC

Twitter January 15, 00:24
How To Open #SharePoint List Hyperlink Column In Modal #Popup Window https://t.co/EQ7HkoZDkX

Twitter January 15, 00:24
Quick Introduction To #Asp.NetCore And It’s Features https://t.co/zAXObHCFpH

Twitter January 15, 00:22
How To Configure #PerformancePoint Services To Use Secure Store In #SharePoint 2013 https://t.co/LEwnUoI7EY

Twitter January 15, 00:21
How To Block Or Disable #Office365 Services https://t.co/Yvp2VPFIRP

Follow us @SharePointPals
Note: For Customization and Configuration, CheckOutRecent Tweets Documentation