| Groups | Blog | Home |
sql server reporting services : Alternating background color
Got a dataset that is used to populate a table. Want to alternate the
background color on every other row in the displayed detail group. Easy enough right? Here's the catch: the output is grouped at display time. A query output might be: KEY Value1 Value2 A 0 1 A 1 0 B 5 0 C 3 0 C 0 7 etc... The DISPLAY output is grouped on the KEY, and the two values are summed to give me a display such as: KEY Value1 Value2 A 1 1 B 5 0 C 3 7 Problem. When I use the standard "=iif(RowNumber(Nothing) MOD 2, "White", "Grey")", it counts EVERY row returned from the original query, not the grouped output, so I don't get a uniform white-grey-white pattern. Anyone know a workaround for this? TIA, Brian
Ok, I found my workaround. Someone is bound to have this issue sometime in
the future, so I'll put the workaround here. I created a little routine in the custom Code area of the report that simply toggles and returns an integer value: Dim Public bgColor As Integer = 0 Public Function alternateColor As Integer If bgColor = 0 bgColor = 1 return bgColor else bgColor = 0 return bgColor end if End Function When i put my method call in the background color on the entire table ROW, the result was alternating COLUMN colors. This is because the method was called for every cell (column) in the row. In order to get alternating ROW color, I only called the alternateColor routine in the FIRST column in the table row (iif(Code.alternateColor() = 0, "white", "grey")). Each subsequent column in the row would simply check the "Code.bgColor" value for its current value, and base its color on that (iif(Code.bgColor = 0, "white", "grey")). Maybe this will come in handy for someone else someday..... Brian [quoted text, click to view] "G" <brian.grant@si-intl-kc.com> wrote in message news:OSJrwjtYFHA.1152@tk2msftngp13.phx.gbl... > Got a dataset that is used to populate a table. Want to alternate the > background color on every other row in the displayed detail group. Easy > enough right? Here's the catch: the output is grouped at display time. A > query output might be: > > KEY Value1 Value2 > A 0 1 > A 1 0 > B 5 0 > C 3 0 > C 0 7 > > etc... > > The DISPLAY output is grouped on the KEY, and the two values are summed to > give me a display such as: > > KEY Value1 Value2 > A 1 1 > B 5 0 > C 3 7 > > Problem. When I use the standard "=iif(RowNumber(Nothing) MOD 2, "White", > "Grey")", it counts EVERY row returned from the original query, not the > grouped output, so I don't get a uniform white-grey-white pattern. Anyone > know a workaround for this? > > TIA, > > Brian >
Great solution, I've been playing around with RowNumber for ages - this is
much better!!! Thanks Brian!!! [quoted text, click to view] "G" wrote:
> Ok, I found my workaround. Someone is bound to have this issue sometime in > the future, so I'll put the workaround here. > > I created a little routine in the custom Code area of the report that simply > toggles and returns an integer value: > > Dim Public bgColor As Integer = 0 > > Public Function alternateColor As Integer > If bgColor = 0 > bgColor = 1 > return bgColor > else > bgColor = 0 > return bgColor > end if > End Function > > When i put my method call in the background color on the entire table ROW, > the result was alternating COLUMN colors. This is because the method was > called for every cell (column) in the row. In order to get alternating ROW > color, I only called the alternateColor routine in the FIRST column in the > table row (iif(Code.alternateColor() = 0, "white", "grey")). Each subsequent > column in the row would simply check the "Code.bgColor" value for its > current value, and base its color on that (iif(Code.bgColor = 0, "white", > "grey")). > > Maybe this will come in handy for someone else someday..... > > Brian > > "G" <brian.grant@si-intl-kc.com> wrote in message > news:OSJrwjtYFHA.1152@tk2msftngp13.phx.gbl... > > Got a dataset that is used to populate a table. Want to alternate the > > background color on every other row in the displayed detail group. Easy > > enough right? Here's the catch: the output is grouped at display time. A > > query output might be: > > > > KEY Value1 Value2 > > A 0 1 > > A 1 0 > > B 5 0 > > C 3 0 > > C 0 7 > > > > etc... > > > > The DISPLAY output is grouped on the KEY, and the two values are summed to > > give me a display such as: > > > > KEY Value1 Value2 > > A 1 1 > > B 5 0 > > C 3 7 > > > > Problem. When I use the standard "=iif(RowNumber(Nothing) MOD 2, "White", > > "Grey")", it counts EVERY row returned from the original query, not the > > grouped output, so I don't get a uniform white-grey-white pattern. Anyone > > know a workaround for this? > > > > TIA, > > > > Brian > > > >
This was just what i was looking for. My returned dataset sometimes Groups
so that rownumbers aren't in a consecutive order, giving some strange alternate highlighting results using the conventional method. This should work nicely, cheers [quoted text, click to view] "G" wrote:
> Ok, I found my workaround. Someone is bound to have this issue sometime in > the future, so I'll put the workaround here. > > I created a little routine in the custom Code area of the report that simply > toggles and returns an integer value: > > Dim Public bgColor As Integer = 0 > > Public Function alternateColor As Integer > If bgColor = 0 > bgColor = 1 > return bgColor > else > bgColor = 0 > return bgColor > end if > End Function > > When i put my method call in the background color on the entire table ROW, > the result was alternating COLUMN colors. This is because the method was > called for every cell (column) in the row. In order to get alternating ROW > color, I only called the alternateColor routine in the FIRST column in the > table row (iif(Code.alternateColor() = 0, "white", "grey")). Each subsequent > column in the row would simply check the "Code.bgColor" value for its > current value, and base its color on that (iif(Code.bgColor = 0, "white", > "grey")). > > Maybe this will come in handy for someone else someday..... > > Brian > > "G" <brian.grant@si-intl-kc.com> wrote in message > news:OSJrwjtYFHA.1152@tk2msftngp13.phx.gbl... > > Got a dataset that is used to populate a table. Want to alternate the > > background color on every other row in the displayed detail group. Easy > > enough right? Here's the catch: the output is grouped at display time. A > > query output might be: > > > > KEY Value1 Value2 > > A 0 1 > > A 1 0 > > B 5 0 > > C 3 0 > > C 0 7 > > > > etc... > > > > The DISPLAY output is grouped on the KEY, and the two values are summed to > > give me a display such as: > > > > KEY Value1 Value2 > > A 1 1 > > B 5 0 > > C 3 7 > > > > Problem. When I use the standard "=iif(RowNumber(Nothing) MOD 2, "White", > > "Grey")", it counts EVERY row returned from the original query, not the > > grouped output, so I don't get a uniform white-grey-white pattern. Anyone > > know a workaround for this? > > > > TIA, > > > > Brian > > > >
This didn't work for me since I am setting a row to hidden based on a value
in that row. SQL RS thinks that row is still there and displays two back to back colors instead of alternating the colors. Any ideas? Thanks, Don [quoted text, click to view] "G" wrote:
> Ok, I found my workaround. Someone is bound to have this issue sometime in > the future, so I'll put the workaround here. > > I created a little routine in the custom Code area of the report that simply > toggles and returns an integer value: > > Dim Public bgColor As Integer = 0 > > Public Function alternateColor As Integer > If bgColor = 0 > bgColor = 1 > return bgColor > else > bgColor = 0 > return bgColor > end if > End Function > > When i put my method call in the background color on the entire table ROW, > the result was alternating COLUMN colors. This is because the method was > called for every cell (column) in the row. In order to get alternating ROW > color, I only called the alternateColor routine in the FIRST column in the > table row (iif(Code.alternateColor() = 0, "white", "grey")). Each subsequent > column in the row would simply check the "Code.bgColor" value for its > current value, and base its color on that (iif(Code.bgColor = 0, "white", > "grey")). > > Maybe this will come in handy for someone else someday..... > > Brian > > "G" <brian.grant@si-intl-kc.com> wrote in message > news:OSJrwjtYFHA.1152@tk2msftngp13.phx.gbl... > > Got a dataset that is used to populate a table. Want to alternate the > > background color on every other row in the displayed detail group. Easy > > enough right? Here's the catch: the output is grouped at display time. A > > query output might be: > > > > KEY Value1 Value2 > > A 0 1 > > A 1 0 > > B 5 0 > > C 3 0 > > C 0 7 > > > > etc... > > > > The DISPLAY output is grouped on the KEY, and the two values are summed to > > give me a display such as: > > > > KEY Value1 Value2 > > A 1 1 > > B 5 0 > > C 3 7 > > > > Problem. When I use the standard "=iif(RowNumber(Nothing) MOD 2, "White", > > "Grey")", it counts EVERY row returned from the original query, not the > > grouped output, so I don't get a uniform white-grey-white pattern. Anyone > > know a workaround for this? > > > > TIA, > > > > Brian > > > >
Thanks for this Brian. Even a couple of years later, your code piece has come in handy.
From http://www.developmentnow.com/g/115_2005_5_0_0_528879/Alternating-background-color.htm Posted via DevelopmentNow.com Groups
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |