none
SSRS 2008 R2: Passing multiple values to a drill through report parameter

    Question

  • Hi Everyone,

    I have a main report and a drill through report.  The data set for the main report is as follows:

    MajorGroup      Group      SubGroup      DetailGroup     Region     Site      Date               Type      Count

    MajorGroup1     Group1     SubGroup1     DetailGroup1    Region1   Site1     2012-02-01     Type1    1

    MajorGroup1     Group1     SubGroup1     DetailGroup1    Region1   Site1     2012-08-01     Type2    1

    The main report is summarized so that the data looks like the following

    MajorGroup      Group      SubGroup      DetailGroup     Region     Site      Count

    MajorGroup1     Group1     SubGroup1     DetailGroup1    Region1   Site1     2

    I have a "go to report action" set at the Count level which passes 4 parameters to the drill through report.

    Site               Site_ID

    Type              Type_ID

    Date              Date_Key

    DetailGroup   DetailGroup_ID

    The drill through report runs but the problem is that only the first record in the data set gets passed to the drill through report rather than sending multiple values for Type and Date.  I've made sure that the parameters in the drill through report accept multiple values but it seems like the drill through parameters in the main report aren't passing multiple values.

    Please let me know what I'm doing wrong or if there's a work around so I can pass some kind of an array to the drill through report for the Type and Date field.

    Thanks for your help,

    Dan

    Wednesday, September 04, 2013 9:54 PM

All replies

  • Hi Danpal,

    It seems you are passing least granular level of your data to sub report parameter that's why it is showing single record. As per your data , dont pass date or Date_Key as input parameter to sub report.

    Sub report is used to show detailed report(lower level granular data) based on some higher granular level attributes/aggregations.

    Follow the below link that will help you in resolving your issue:

    http://www.sqlcircuit.com/2012/11/ssrs-how-to-create-subreport.html

    Thanks,

    Vishal

    Thursday, September 05, 2013 2:35 AM
  • Hi Vishal,

    Thanks for the reply. 

    The drill through report is actually getting values from the main report for Date and Type but only one value is getting pass for each field. 

    What's being passed is:

    DetailGroup1   Site1   Type1   2012-02-01

    What I would like to be passed is:

    DetailGroup1   Site1   Type1,Type2   2012-02-01,2012-08-01

    If there is no way for the main report to pass multiple values to the drill through report using the parameters in the drill through action, is there another way to access the values in the data set row where the drill through action exists.  Maybe it's possible to access the values using the Fields collection and passing it to the drill through report using the join method.

    Dan

    • Edited by Danpal Thursday, September 05, 2013 2:24 PM
    Thursday, September 05, 2013 1:23 PM
  • What is the parameter type of the Date and Type parameters? Are they Multivalue parameters ("Allow multiple values" checked on General tab of parameter properties)?

    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Thursday, September 05, 2013 6:08 PM
  • From your example I would think you would only pass the Site_ID and the DetailGroup_ID to the drill through report.  To capture the corresponding Date_Key and Type_ID , you can create a data set in your drill through report to set the default values of those two parameters.  Use a query similar to the one used in your main report, that only captures the distinct values of Date and Type.  Simply create two data sets in your drill through report to capture the default Date_Key and default Type_ID based on the Site_ID and DetailGroup_ID.  Include a WHERE clause in the data sets to filter on the Site_ID and the DetailGroup_ID parameter values being passed in.  Use these data sets as the default values for the Date and the Type parameters. 

    Martina White

    Monday, September 23, 2013 9:39 PM