reporting-servicesssrs-2012

SSRS Drillthrough in New Tab with Parameters - Link opens but report doesn't automatically run


I'm trying to set one of our reports to open a drillthrough report in a new tab instead of overwriting the existing window. I'm not super familiar with SSRS or Visual Basic, I mostly do SQL development, but I've been trying to figure it out from online forums and this is what I have so far:

="javascript:void(window.open('http://server/Reports/Pages/Report.aspx?ItemPath=%2fProgram+Directors+Report%2fTransaction+Detail+Report&rs:Command=Render&rc:Parameters=true&BeginDate="&Parameters!BeginDate.Value &"&EndDate="&Parameters!EndDate.Value &"&AvailRU="&Fields!sCodeIDf_2.Value &"&AvailAccnt="&Fields!sCodeIDf_0.Value &"&secoverride="&Parameters!secoverride.Value &"','_blank'))"

After a lot of trial and error this attempt finally opens a working link to the drillthrough report and I can see the correct parameter values in the address, however the drillthrough report doesn't actually run.

In the current version of the report where the drillthrough overwrites the original report window, clicking on the drilldown link opens the Transaction Detail Report and automatically runs it with the passed parameters, so it looks like this, with no option to change the parameters: Transaction Detail Report current

In the new version the drillthrough opens a new tab with this address, so all of the parameters are passing:

http://server/Reports/Pages/Report.aspx?ItemPath=/Program+Directors+Report/Transaction+Detail+Report&rs:Command=Render&rc:Parameters=true&BeginDate=7/8/2023&EndDate=7/15/2023&AvailRU=1000&AvailAccnt=50010&secoverride=1

But the report looks like this, so it hasn't actually run and the parameter dropdowns are visible and set to their defaults: Transaction Detail Report that hasn't run

Can anyone tell me what I'm doing wrong or what I need to do to make the report actually run when you click the drillthrough link? Thanks!


Solution

  • I think you need to use the reportserver URL not "reports".

    This example does not use the JS wrapper to open in a new tab but it sounds like it's the actual URL that is wrong.

    I typically build up the URL as an expression something like this (this in the URL expression in the textbox action properties)

    = Globals.ReportServerUrl
    + "?/My+Report+Folder/My+Report+Name" 
    + "&CountryID=" + cStr(Fields!CountryID.Value)
    + "&CategoryID=" + cStr(Fields!CategoryID.Value)
    + "&RecordedPeriodID=" + cStr(Parameters!PeriodID.Value)
    + "&TMB=" + cStr(Fields!TrademarkBrandID.Value)
    + "&PriceStage=" + cStr(IIF(Fields!IsActualprice.Value = 1, 10, 11))
    + "&pm=" + cStr(Fields!PackMaterialID.Value)
    + "&pt=" + cStr(Fields!PackTypeID.Value)
    + "&ps=" + cStr(Fields!PackSizeID.Value)
    + "&psu=" + cStr(Fields!PackSizeUnitID.Value)
    + "&upp=" + cStr(Fields!UnitsPerPack.Value)
    + "&rc:Parameters=Collapsed"
    

    Which will give you something like this.

    http://mySSRSServerName/ReportServer?/My+Report+Folder/My+Report+Name&CountryID=9&CategoryID=2&RecordedPeriodID=2022000&TMB=3351&PriceStage=10&pm=1&pt=1&ps=34&psu=1&upp=1&rc:Parameters=Collapsed
    

    IMPORTANT NOTE: I use Globals.ReportServerURL so it works across different servers but beware that this will only work once the report is deployed to SSRS, not from visual studio (not tested in Report Builder - might work if connected to server).

    The only reason the report will not run automatically is if one or more parameters have not been passed.

    The other advantage of this is the the links work even if you export this to excel, it will still open the drill report in your browser.

    Finally you can see I hide the parameter section but this is typically always there, you just need to click the border above the main report area to see them again.