Hide a row from the details group, but include it in parent group subtotals

Question

I'm tinkering with a sales report in which I need to have the ability to hide certain rows from the details, but still include them in group subtotals. If I set a filter on the details group to eliminate these rows based on the appropriate criteria, they're no longer included in the group subtotals. If I adjust the row visibility for the detail row, I can hide them using the same criteria, and the numbers are included in the subtotals, but this messes up the counting of RowNumber(), and the banded rows look like a mess. Is there another clever way of doing this that I'm overlooking?

What expression/code are you using for alternate row color? – Bryan Feb 09 '12 at 20:22 — Bryan, Feb 09 '12 at 20:22
Just the standard `RowNumber(Nothing) Mod 2 = 1`. – db2 Feb 09 '12 at 20:33 — db2, Feb 09 '12 at 20:33

score 0 · Accepted Answer · edited May 23 '17 at 11:48

See this SO post.

I prefer Catch22's answer due to simplicity of the expression needed for the BackgroundColor property. It also seems to be more robust when dealing with grouping than the =IIF(RunningValue... approach.

Summary

Add this code:

Private bOddRow As Boolean
'*************************************************************************
' -- Display green-bar type color banding in detail rows
' -- Call from BackGroundColor property of all detail row textboxes
' -- Set Toggle True for first item, False for others.
'*************************************************************************
Function AlternateColor(ByVal OddColor As String, _
         ByVal EvenColor As String, ByVal Toggle As Boolean) As String
    If Toggle Then bOddRow = Not bOddRow
    If bOddRow Then
        Return OddColor
    Else
        Return EvenColor
    End If
End Function

Then use this expression for the BackgroundColor property value on detail cells:

=Code.AlternateColor("AliceBlue", "White", True)

In lieu of anything more declarative, this seems like a decent way to do it. Though I think more elaborate trickery would be necessary for a matrix with row and column groups. — db2, Feb 14 '12 at 13:06

Hide a row from the details group, but include it in parent group subtotals

1 Answers1

Summary