The Problem
I am using dynamic matrix groups as in Dynamic Grouping from Chris Hays's Reporting Services Sleazy Hacks Weblog (which has some great stuff and is worth checking out, by the way). I have my row and column groups bound to two multi-value parameters and everything is working great, except for one thing. Even though I am suppressing unused groups from displaying, they are still taking up space.
Here's what the report looks like now when I choose only one row group and one column group:
Notice how even though the undesired groups have property hidden True (as set by an expression) they still take up space. Here's what I'd like it to look like (I cut the blank areas out of the picture by hand):
The Question
Does anyone have any ideas how to make the blank areas shrink when the corresponding matrix groups are hidden?
Just For the Curious (Not part of the question)
For reference, here is what the matrix looks like in Layout mode:
And if anyone else wants to do dynamic matrix groups like this, here's the code section that does the heavy lifting:
Public Function ValueIsInMultiParameter(MultiParameter As Parameter, Value As String) As Boolean
Dim i as Long
For i = 0 to MultiParameter.Count - 1
If MultiParameter.Value(i) = Value Then Return True
Next
Return False
End Function
Public Function DynamicFieldValue(CurrentFields As Fields, MultiParameter As Parameter, Index As Long) As Object
If ParameterCount(MultiParameter) - 1 < Index Then Return ""
Return CurrentFields(MultiParameter.Value(Index)).Value
End Function
Public Function DynamicFieldFormattedValue(CurrentFields As Fields, MultiParameter As Parameter, Index As Long) As Object
If ParameterCount(MultiParameter) - 1 < Index Then Return ""
Return CustomFormat(CurrentFields(MultiParameter.Value(Index)).Value, MultiParameter.Value(Index))
End Function
Public Function DynamicGrouping(MultiParameter As Parameter, Index As Long) As Object
If ParameterCount(MultiParameter) - 1 < Index Then Return "None"
Return MultiParameter.Value(Index)
End Function
Public Function DynamicGroupingLabel(CurrentFields As Fields, MultiParameter As Parameter, Index As Long) As String
If Index = 0 Then Return "Grand Total"
If ParameterCount(MultiParameter) - 1 < Index Then Return ""
Return CustomFormat(CurrentFields(MultiParameter.Value(Index - 1)).Value, MultiParameter.Value(Index - 1)) & " Total"
End Function
Public Function CustomFormat(Value As Object, ValueType As String) As String
Select Case ValueType
Case "DayOf"
Return Format(Value, "M/d/yyyy")
Case "WeekOf"
Return Format(Value, "M/d/yyyy")
Case "WeekDayPart"
Return WeekdayName(Value)
Case "MonthOf"
Return Format(Value, "MMM yyyy")
Case "MonthDayPart"
Return "Day " & Value
Case "MonthWeekPart"
Return "Week " & Value
Case "YearOf"
Return Format(Value, "yyyy")
Case "YearDayPart"
Return "Day " & Value
Case "YearWeekPart"
Return "Week " & Value
Case "YearMonthPart"
Return MonthName(Value)
Case "YearPart"
Return Value
Case Else
Return Value
End Select
End Function
Public Function ParameterCount(MultiParameter As Parameter) As Long
If MultiParameter.Count = 0 OrElse MultiParameter.Value(0) = "1" Then Return 0
Return MultiParameter.Count
End Function
In the parameters, I put a hard-coded list of selectable grouping options, for example the RowGroups parameter has available values:
Label Value
----- -------
Year YearOf
Month MonthOf
Week WeekOf
Day DayOf
Where the Value is the name of the SQL column as returned by the DataSet.