Alternating Backgrounds for groups

[skalina]

I know its possible to alternate background colors within rows using =iif(rownmumber("dataset") MOD 2,"White","Silver")...However, what I really want to do is altnerate colors for each group -- but I havent quite figured that out just yet...

Anyone able to do this?

thanks

 

[SQLBI]

Hi,

The only way i can think of doing this would be to add a column to your dataset called GroupID and as part of your query assign each row a GroupID value starting at 1 and incrementing for each group change.

Then you could use

 =iif(Fields!GroupID.Vlaue MOD 2, "White", "Silver")

There is now equivelant of RowNumber for groups... Would be handy though.

Hope this helps.

Cheers,
Leigh

"If you had one shot, one opportunity, to seize everything you ever wanted, in one moment, would you capture it, or let it slip" - Eminem

 

[SgtJarrow]

Don't remember where I found this bit of 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

Put that in to the code section for the report. Then, using the Background property for the row where you want the changes to take place, put:

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

The first two parameters are the two colors to alternate through. The last parameter determines the color flipping - set it to true if the cell should alternate the color and false if not.

As stated in the comment at the top of the code, this needs to go into each cell in the row where the color changes...True in the first field and false in all others.

Works great for me

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer