Condividi tramite

Clicking the drillthrough link in a 2008 Multi valued parameter report after exporting to MHTML / Word formats which contains drillthrough action (Go to report) fails

  • Articolo

PROBLEM:

1. Consider a report (rptParent) with a multi value query based parameter and it has links (table cell action “Go to report”) to rptChild.

2. The report (rptChild) renders pased on the parameter value it receives from rptParent.

3. When viewed on report server all the links work, both rptParent and rptChild works too.

4. Now either EXPORT or set up subscription to rptParent to MHTML / WORD etc.

5. When clicking the links on the exported / subscribed report rptParent, the report server complains about one of the parameter missing and would not render the report rptChild.

 

EXPLANATION:

 

This will work perfectly fine in Reporting Service 2005. The issue got introduced only in Reporting Service 2008.

This is because the way the URL is constructed. Notice the Urls generated.

Failing: https://localhost/ReportServer?%2fParametersTest&ParamValue=1%2c2&rs%3aParameterLanguage=&rc%3aParameters=Collapsed

 

Working:  https://localhost/ReportServer?%2fParametersTest&ParamValue=1&ParamValue=2&ParamValue=3aParameterLanguage=&rc%3aParameters=Collapsed

 

WORKAROUND:

 

As there are currently no plans to fix this in Katmai, follow the below workaround.

 

Try to construct the Drillthrough link manually as a hyperlink. The expression might look something like:

=Globals.ReportServerURL & "?" & Uri.EscapeDataString(Globals.ReportFolder & "/" & <<<TargetReportName>>> ) & <<ParametersAsString>>

For example, if the target report name is "ParametersTest" and you wanted to pass values 1, 2 and 3 to parameter "ParamValue", the URL would be:

=Globals.ReportServerURL & "?" & Uri.EscapeDataString(Globals.ReportFolder & "/ParametersTest") & "&ParamValue=1&ParamValue=2&ParamValue=3"

Comments

  • Anonymous
    October 13, 2009
    Hi Selvar Thanks for confirming this limitation of SSRS 2008. This works indeed fine in the browser but not in a report subscription (MHTML). And as you stated it DID work in SSRS 2005. As your workaround only works with hard coded parameters I'm wondering whether you can think of a workaround of passing more flexible parameters... My rptParent has a multi-value parameter named Vertical. This parameters is split and joined to be used in a stored procedure as follows: =split(join(Parameters!Vertical.Value,","),",") rptChild has the same multi valued parameter named Vertical. The action from rptParent to run rptChild are to join the parameters again: =Join(Parameters!Vertical.Value,", ") The parameters passed should match the ones that were selected in the rptParent (subscription) and they vary for different users. Any suggestions are appreciated! Cheers Sander

  • Anonymous
    October 16, 2009
    Hi Sander,         Looks like you want to automate the parameter building process for multi value parameters. For this we need to write a piece of custom code and here is that i've done it for you. Under Report-> Report Properties -> Code, Paste the following Public Shared Function BuildParams(ByVal param As String, ByVal paramname As String) As String        Dim params() As String = Split(param, ",")        Dim url As New System.Text.StringBuilder        For Each val As String In params            url.Append("&" + paramname + "=" + val)        Next        Return url.ToString()    End Function All i'm doing in the above code is just getting the parameter collection and building the url dynamically. Now in the textbox properties, Action -> go to URL, you need to do the following, =Globals.ReportServerURL & "?" & Uri.EscapeDataString(Globals.ReportFolder & "/" & "Child") & Code.BuildParams(Join(Parameters!empid.Value,","),"empid") Where  Code.BuildParams(Join(Parameters!empid.Value,","),"empid") is calling the above written function with the list of values from the parameter and then the actual name of the target report parameter(s). The final built URL will look some thing like, http://machinename/ReportServer?%2FMhtmlDrillThroughBug%2FChild&empid=2&empid=5 HTH! Selva.

  • Anonymous
    November 05, 2009
    Hi Selva Sorry it has taken me some time to get back to you. I have now been able to try your solution and it works a treat! Thanks for your help! Cheers Sander

  • Anonymous
    January 24, 2011
    Hi Selvar, I am facing a similar problem. I am using SQL Server 2008 R2 . My main report has a link to the subreport and when it is exported to Excel and if i click on the link , the sub report is opening up with the Parameters seen at the top . In SSRS 2008 -  http://localhost/ReportServer/Pages/ReportViewer.aspx?%2FPriceBookReports61%2FSingleProduct_Disti&edwMaterialId=901606%20%20%20%20%20%20%20%20%20%20%20%20&GeoCode=APAC&DivisionCode=&rs%3AParameterLanguage= But When i use SSRS 2005 - the url generated is -  http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fPriceBookReports50%2fSingleProduct_Disti&edwMaterialId=901606++++++++++++&GeoCode=APAC&DivisionCode=&rs%3aParameterLanguage=en-US&rc%3aParameters=Collapsed As you can notice at the end of the URL, the Parameters=Collapsed is missing in the URL generated by SSRS 2008 . Is there a way to set this parameter property to Collapsed in any configuration file or any idea how to do this other than manually going for jump to URL instead of Jump to Report ? Thanks' Arun

  • Anonymous
    January 29, 2011
    Hi Arun,             Since this turns out to be a renderer specific device info setting command and in our case it is spefically targetted for HTML viewer (rc:) and hence we need to look for HTML Viewer specific commands. msdn.microsoft.com/.../ms152835(v=SQL.100).aspx Unfortunately for HTML viewer there is no way we can specify the device info setting in the config file. So we need to depend on the URL access. HTH! Selva. [Posted AS-IS with no warranty]