SSRS  Multi Select Parameter Drill Through

Posted on 2011-10-21
Last Modified: 2012-08-13
Hi All,

I'm using SSRS 2008 and come across an issue:

Assume I have this mapping:

GROUP     Members
    A              1, 2, 3
    B              3, 4, 5

Users in the main report can filter by group, or members.  (Dataset for members is select distinct members from <table> where dbGroup in (select value dbo.fnSplit( @group))  . . . this way if Someone selects A they can only choose 1,2,3, but if A,B are both there they get 1,2,3,4,5).

Now lets say I want to drill through to B . . . so I'm passing B as the parameter, as well as them members of the main report (eg: 1,2,3,4,5).  

in my Child Report, the only members available are 3,4,5 and SSRS doesn't select anything causing data not to show.  Users have to click the check all box and then view report.

Is there a better way of doing this?  

Question by:Kyle Abrahams
    LVL 15

    Expert Comment

    Hi, why are you passing the members as a parmeter - just pass the selection criteria (B) and replicate the member selection query in the child report. If you cannot specify a default value for members in the chil report, you could add an 'All' option and default to that.

    LVL 39

    Author Comment

    by:Kyle Abrahams
    Hi Tim,

    Because I need to replicate the data.

    If they run it for the 3rd member only and they want to see the details of that grouped by the respective group, they don't want to see all of the members in the drill down.
    LVL 15

    Expert Comment

    What do you have your default set to for the members paramter in the second report?
    LVL 39

    Accepted Solution

    Found the answer.

    The trick was to do a union.

    Essentially I had to get the Valid Members for the group as the available entries, and then find the valid members within that list to get my default values.

    Essentially Parameters became:

    Group drove available members
    Then another stored proc Given group and ParentMembers returned available members.
    LVL 39

    Author Closing Comment

    by:Kyle Abrahams
    Found answer via a colleague at work.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now