RSS

SSRS – Understanding Report Parameter Visibility

23 Apr

Report Parameters in SQL Server Reporting Services (SSRS) form a very powerful component. Report Parameters offer following capabilities:

  • Allows end users to interact with the report by allowing them to
    • Filter the data in the report
    • Drill-down or drill-up a report/data item (using cascading parameters)
    • Carry out other custom capabilities like show or hide specific components/fields within a report, to control the initial view of a drill-down report (collapsed/expanded) etc.
  • Allows the reporting applications to capture the user related information like User Id etc. for implementing data level security related capabilities and other capabilities.
  • Allows implementation of drill-through reports by passing parameters from parent report to child report.
  • and the list goes on…

A Parameter in a SSRS Report has various properties like Name/Prompt, Data Type, Visibility, Available and Default Values, and a bunch of other parameters under the advanced section of the Report Properties Window.

One of the most important among these properties is the Parameter Visibility, which controls whether a parameter is visible to the end user or is invisible. If invisible, whether it is accessible to other applications/in other interfaces or not. Following are the 3 visibility options available for a Report Parameter:

  • Visible
  • Hidden
  • Internal

Following table provides a comparison between each type of parameter visibility.

Visible Hidden Internal
Accessible for read/write within the report Accessible for read/write within the report Accessible for read/write within the report
Values can be passed to this type of parameter (when present in the child report) in case of a drill-through report implementation Values can be passed to this type of parameter (when present in the child report) in case of a drill-through report implementation Values cannot be passed to this type of parameter (when present in the child report) in case of a drill-through report implementation. This type of parameter is read-only and not accessible in parent report.
Accessible for read/write by the end-user Not accessible to the end-user directly (but if some visible parameter is linked to this parameter, then any changes to visible parameter will impact the hidden parameter state) Not accessible to the end-user
Accessible to applications (for read/write) as part of integration May or may not be accessible to applications (for read/write) as part of integration Not accessible to applications as part of integration
Accessible in Report Manager for modifications to properties like Default Value, Visibility, and Prompt etc. Accessible in Report Manager for modifications to properties like Default Value, Visibility, and Prompt etc. Accessible in Report Manager for modifications to properties like Default Value, Visibility, and Prompt etc.
Use this parameter if this needs to be accessible to the end-user and also accessible to other application as part of integration Use this parameter if this needs to be hidden from the end-user but would be required as part of drill-through report, implementation of reporting logic etc. Use this parameter if this needs to be hidden from the end-user, should not be accessible in drill through reports or other application as part of integration etc. Use this if it is required for implementation of any reporting logic.

Following screenshot shows the state of parameters in the Report Manager (with different type of visibility set).

About these ads
 
2 Comments

Posted by on April 23, 2012 in SQL Server, SSRS

 

Tags: , , ,

2 responses to “SSRS – Understanding Report Parameter Visibility

  1. Anonymous

    April 19, 2013 at 7:06 PM

    Good one ..

     
  2. Anonymous

    May 16, 2013 at 6:42 AM

    Very good post. Like your comparison table.

     

What are your thoughts?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 304 other followers