Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1049
  • Last Modified:

SSRS Multi Select Parameter Drill Through

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?  

Kyle Abrahams
Kyle Abrahams
  • 3
  • 2
1 Solution
Tim HumphriesDirectorCommented:
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.

Kyle AbrahamsSenior .Net DeveloperAuthor Commented:
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.
Tim HumphriesDirectorCommented:
What do you have your default set to for the members paramter in the second report?
Kyle AbrahamsSenior .Net DeveloperAuthor Commented:
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.
Kyle AbrahamsSenior .Net DeveloperAuthor Commented:
Found answer via a colleague at work.

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now