Dynamic Grouping with Report Designer

I've got this working -- I allow users to have 2 groups, and choose what they want to group by. I'd like to add one extra bit of functionality -- for the inner grouping, I would like my users to have the option "None" -- i.e. don't have an inner group.

I've tried setting the group expression of the second (inner) group to "" when the user chooses the "None" option but the report errors out. Any suggestions as to how to dynamically get rid of the inner group?

Thx

Helen

Helen:

I had a similar requirement and here's how I solved it. For the "None" entry at each grouping level you'll need to include a value for it in the combobox that's the following functions will recognize. Include the following in the "Code" and call the appropriate function. You're probably most interested in the "GetSubGrouping" function but I'm including "GetGrouping" so you can also use it at the main grouping level. Include the call to GetSubGrouping in the "Group on"; you'll also find that "GetField" is useful instead of coding a lot of iif's for controlling the visibility of the groupings.

Hope this helps

Glenn L

SharedFunction GetGrouping(ByVal Parameters AsObject, ByVal Fields AsObject) AsObject

Return GetField(Fields, Parameters("GroupBy").Value, "no_grouping")

EndFunction

SharedFunction GetSubGrouping(ByVal Parameters AsObject, ByVal Fields AsObject) AsObject

Return IIf(Parameters("GroupBy").Value = "no_grouping" _

Or Parameters("GroupBy").Value = Parameters("SubGroupBy").Value, Nothing, GetField(Fields, Parameters("SubGroupBy").Value, "no_sub_grouping"))

EndFunction

SharedFunction GetField(ByVal Fields AsObject, ByVal FieldName AsString, ByVal NoGroupingValue AsString) AsObject

If Trim$(FieldName) = Trim$(NoGroupingValue) Then

ReturnNothing

ElseIf IsDate(Fields(Trim$(FieldName)).Value) Then

Return FormatDateTime(Fields(Trim$(FieldName)).Value, 2)

Else

Return Fields(Trim$(FieldName)).Value

EndIf

EndFunction

|||

Thank you Glenn! That did the trick!

Helen