Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Salvo(z) SQL

Adam and Jennifer Salvo are IT professionals with over 10 years of diverse experience. Jennifer is a Business Intelligence developer focusing on the Microsoft BI stack (SSIS, SSAS and SSRS). Her prior work experience includes software development, systems analysis, end-user support, training, and SharePoint administration. Adam is a .NET technical lead with a current emphasis on Dev Ops and Windows Azure. His prior work experience includes .NET development, SQL Server administration, and BizTalk development. They also maintain a personal blog at salvoz.com.

SSRS: Report Actions, URL Actions and SharePoint

I recently encountered a scenario involving a SSRS report that contains a report action to launch a sub-report.  The child / sub-report originally had two mandatory parameters and the values for these parameters were passed from the parent report to the child.  I wanted to add a third parameter to the child report that instead of being passed form the parent would default to a specific value that could be modified by the user. 

With Report Actions in SSRS, parameters are not displayed on the sub-report when it is rendered.  Therefore, the values for these parameters must either be passed from the parent report or set to a default value.  Since the parameters are not rendered, it is not possible for a user to adjust their values.

To work around this limitation, I needed to convert the Report Action to a URL Action so the additional parameter is displayed to the user.  URL actions are very simple in that you just specify the URL in the ‘Select URL’ field. 

image

The challenge in my instance we determining the appropriate URL since this report resides in SharePoint and two parameters are passed from the parent to the child report.

I found a blog post that defines the general syntax for a SharePoint URL and was a helpful starting point. 

The general syntax for the URL is displayed below:

http://<SharePoint_site>/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/<SharePoint_Document_Library>/<Report_Name>.rdl&rp:Month=January

To include additional parameters in the URL, simply add on additional &rp:<Param Label>=<Param Value> to the end of the URL expression.

Obviously, hard-coding a URL in the report is less than ideal.  The built-in SSRS Global variables can help make the implementation more flexible.  The following expression worked in my environment, no guarantees it will produce the correct URL in all instances.

=Replace(Globals!ReportServerUrl,”_vti_bin/ReportServer”,”_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=”) & Replace(Globals!ReportFolder,”<SharePoint_site>”,”") & “/” & “<Report Name>.rdl” & “&rp:<Param>=” & CStr(Fields!<Param>.Value)

Comments

Leave a comment on the original post [salvoz.com, opens in a new window]

Loading comments...