November 12, 2007 at 6:03 am
Hi All,
We are currently moving from Crystal Reports to SSRS 2005. Currently we have an expression to format the background colour of our details section of the report. It looks like this; if RecordNumber mod 2 =1 then color(220,220,220) else white.
How could I replicate this behaviour in SSRS 2005?
Thanks
November 12, 2007 at 6:13 am
Add an expression to the row like this:
=IIf((RowNumber("table1") Mod 2), "lightblue", "blue")
Just replace the colours with thise you want.
Markus
[font="Verdana"]Markus Bohse[/font]
April 8, 2008 at 9:57 am
I'm new at this, but I'm told the solution supplied above only works with ungrouped data. I've been supplied with a formula that's supposed to work with grouped data, but it doesn't. (I get a message about the TruePart of the statement.) Does anyone know if there is a solution to the alternate row shading question in grouped data?
Here are the two Iif statements that were supplied to us by a MS Partner consultant/trainer, neither of which work. He said he'd research it and get back to us several days ago. I haven't heard from him yet.
= IIF(RunningValue(Fields!Some_Field.Value, CountDistinct, "Group_Name") Mod 2, "Gainsboro", "White")
= IIF(RunningValue(Fields!Some_Field.Value, Count, "Group_Name") Mod 2, "Gainsboro", "White")
April 9, 2008 at 12:53 am
Sorry can't test your version now, but my original formular works on grouped data as well.
All you have to do is in the layout window in VS apply the expression to the row which contains the grouped values.
[font="Verdana"]Markus Bohse[/font]
April 10, 2008 at 8:05 am
I use the same code Markus posted all the time on grouped data without issue. The code you reference does the same thing but it works on the group level.
for example, Markus code does this:
GRP1
Detail1 (White)
Detail2 (Blue)
Detail3 (White)
GRP2
Detail1 (Blue)
Detail2 (White)
Detail3 (Blue)
GRP3
Detail1 (White)
Detail2 (Blue)
While your code would do this:
GRP1
Detail1 (White)
Detail2 (Blue)
Detail3 (White)
GRP2
Detail1 (White)
Detail2 (Blue)
Detail3 (White)
GRP3
Detail1 (White)
Detail2 (Blue)
Basically it resets the row shading at the start of each group.
This code: = IIF(RunningValue(Fields!Some_Field.Value, CountDistinct, "Group_Name") Mod 2, "Gainsboro", "White") is used do do shading on a Matrix and not a table and that is why you are probably getting an error.
April 10, 2008 at 8:10 am
Thanks Mark for the clarification. If I can remember back that far (one week!) 😉 it seems like I tried them all and got errors. I'll test them again.
PS Still haven't heard back from our con$ultant who provided them...
April 10, 2008 at 8:07 pm
How about try this way?
Step 1: Add following code your
'*************************************************************************
' -- 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.
'*************************************************************************
Private bOddRow As Boolean
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
Step 2:
'***********************************
'Change the BackgroundColor for the first column
'************************************
=Code.AlternateColor ("WhiteSmoke", "White", True)
'Change the rest columns's BackgroundColor
=Code.AlternateColor ("WhiteSmoke", "White", false)
Usually I set different background color at group summary level.
Let me know whether it works for you.
April 11, 2008 at 2:15 am
Old SQL Newbie (4/8/2008)
I'm new at this, but I'm told the solution supplied above only works with ungrouped data...
EDIT: Just re-read Mike's post and it sounds like his formula works with matrixes - haven't tried that one myself...
This may be a mistaken reference to matrix reports. The iif(RowNumber(Nothing) mod 2... approach doesn't work properly with a matrix report as the extra groupings in the horizontal dimension cause RowNumber() to be incremented as the report is populated, so you end up with a patchwork of shaded cells rather than every other row shaded. There may be a tricky way aroundd with by fiddling with the scope parameter in RowNumber() to get it to work but I haven't found it yet.
Regards,
Jacob Luebbers
April 11, 2008 at 6:04 am
Just to give credit where credit is due...the code for the matrix was from Chris Hay's and his blog.
-Mike
April 11, 2008 at 6:29 am
Thanks, all for the information. I won't have time to try it for a few days, but will make some tests next week and report back to you.
Thanks bunches! 🙂
April 11, 2008 at 1:02 pm
Yes, it is from Chris's Blog. It works for table with group also. I only have the code from previous project, and forget where I got it from. Do you have the link?
April 11, 2008 at 1:05 pm
April 28, 2008 at 1:46 pm
I've run into this problem a few times while using matrices, and many of the solutions fail.
I tried the given expression using runningvalues, and it came up with '1' every time (so it's always modding 1). Changing the scope variable to the entire matrix seemed to work:
=iif(RunningValue(Fields!YOUR_FIELD.Value, CountDistinct, "matrix1") mod 2, "Gainsboro", "White")
So just replace matrix1 with the name of your matrix and insert your colors.
>>>
If you're grouping on columns and rows, then the background coloring will get messed up where both are grouped.
The only fix I found is to put "RunningValue(Fields!YOUR_FIELD.Value, CountDistinct, "matrix1")" into a hidden textbox and use "=iif(ReportItems("myHiddenRunningValue").Value mod 2, "Color1", "Color2")" for the background color expression.
April 23, 2010 at 9:39 am
Thanks a Lot All,
This is quite informative
Thanks a lot,
Hary
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply