Search code examples
reporting-servicesssrs-2008

SSRS Drilling through on dependent grouped parameters

I have been asked to add a group to a drill-through report depending on the parent report's multi-value parameter called Projects.

This group works perfectly on the parent chart however when drilling through into the child other parameters dependent on Projects are not being selected despite appearing in the list.

For instance the project parameter controls the available options for another multi-select Team parameter. When multiple projects are selected you can run the report across multiple teams, when selecting a single project from the grouped report this limits the team filter in the child, despite some of the original parameters from the main report being available in the child none are selected.

When passing the parameters to the drill-through report can I produce a sublist from the parent Team parameter filtered on the grouped Project parameter?

Pseudo-LINQ example:

 =Parameters!TeamIds.Where(t => t.ProjectId == Fields!ProjectId.Value)

Alternatively is there any way to pass through the full list of teams to the child report and for the report to select the teams that match the selected project?

Edit

Available values for the Project parameter in both parent and child reports:

select id, header from projects

Available values for the Team parameter in both parent and child reports: 

select t.id, t.header from teams t where t.ProjectId = @ProjectId

Parameters

When running the report we display a lot of data in grouped grids with actions to run another report.

Drillthrough

This Drillthrough passes the full list of teams. This stops the child report because some of the teams passed in are not valid values. Then the child report does not automatically run and forces the user to reselect the team.

enter image description here

Despite the Drillthrough implicitly setting the team from the main parameter. I believe this is breaking due to the child report not being able to handle the multiple team projects originally selected in the main report.

Action

My question is how do I:

a) Filter the team parameter to only include the project I have selected to drillthrough

or

b) Make the child report accept the full list of teams passed into it and only select the appropriate ones from the new filtered team list based on the single Project

Edit 2

Public Function GetTeamsForProject(ByVal ItemWithProjectInHeader as Parameter, ByVal ProjectHeader as String) as Collection
Dim s as new Collection

For i as integer = 0 to ItemWithProjectInHeader.Count-1
    if ItemWithProjectInHeader.Label(i).Contains(ProjectHeader)
        s.Add(ItemWithProjectInHeader.Value(i))
    End If
Next
Return s
End Function

Started trying to put together some code to cherry pick the teams but I can't seem to get the collection return to work, for now i am calling it like

=Code.GetTeamsForProject(Parameters!TeamIds,CSTR(Parameters!ProjectId.Label(0)))

This just returns #Error in the text box i am using. Hopefully someone could at least spot the 'deliberate' mistake on this. Thanks

Edit 3

I am also trying something like this

Public Function GetItemsForProject(ByVal ItemWithProjectInHeader as Parameter, ByVal ProjectHeader as String) as Collection
Dim s as new Collection

For i as integer = 0 to ItemWithProjectInHeader.Count-1
    if INSTR(1,ItemWithProjectInHeader.Label(i), ProjectHeader,1) > 0
        s.Add(ItemWithProjectInHeader.Value(i))
    End If
Next
Return s
End Function

and calling it like...

=Code.GetItemsForProject(Parameters!TeamIds,Fields!ProjectId.Value)
Solution

  • Finally got a working solution, I added the Project header to the team parameter so that I can use the team's label in the pass through.

    Some problems with the original code:

    Missing the 'Then' from the ifs

    Potential that the instr or contains were missing references so moved to using the startsWith function

    Public Function startsWith(str As String, prefix As String) As Boolean
    startsWith = Left(str, Len(prefix)) = prefix
End Function

Public Function GetItemsForProject(ByVal ItemWithProjectInHeader as Parameter, ByVal ProjectHeader as String) as Collection

   Dim s as new Collection

   If ItemWithProjectInHeader.IsMultiValue Then
       For i as integer = 0 to ItemWithProjectInHeader.Count-1
            If startsWith(ItemWithProjectInHeader.Label(i), ProjectHeader) Or startsWith(ItemWithProjectInHeader.Label(i), "(Not") Then
                  s.Add(ItemWithProjectInHeader.Value(i))
            End If
        Next
    Else
        If startsWith(ItemWithProjectInHeader.Label(0), ProjectHeader) Then
             s.Add(ItemWithProjectInHeader.Value(0))
        End If
    End If

Return s
End Function