Alternate Fill Color

Question

I would like to be able to alternate the fill color between white and gray as the data changes in COLUMN1.

The data in this column can be duplicated

For example

COLUMN1  COLUMN2

A 1 >>this should white
A 2 >>white
A 3 >>white
B 1 >> gray
C 1 >> white
C 2 >> white
D 1 >> gray
E 1 >> white
E 2 >> white
E 3 >> white
E 4 >> white
F 1 >> gray
F 2 >> gray

this is the detail of a tablix control

using ssrs 2008

thanks

Answer 1

Hello,

in the property pane put this expression in background color  or where you want

=IIF(RunningValue(left(Fields!ID.Value,1),CountDistinct, Nothing) MOD 2 = 1, "Gainsboro", "White")  

---

blog:My Blog/

Hope this will help you !!!
Sanjeewan

Answer 2

This was close but didnt quite work out.

Sorry but I over simplified. Column1 above is not just a single letter, it is a customer name (Customer), that also could be an address.

Also there is grouping going on (Master Customer), My group value changes for each page and when I go to the next page the customer record is always gray. If there is only one customer record for that group I want it to be white.

I tried using =IIF(Fields!Customer.Value<>Previous(Fields!Customer.Value) MOD 2 = 1, "Gray", "White") but that too didnt quite work

My report is structured as follows:

Master Customer: James >> Page 1
Customer: Account:
James 11111 >>white
Bill 2345 >>gray
Bill 7777 >>gray
Ed 2222 >>white

Master Customer: Pete >>Page 2
Customer: Account:
Pete 8976 >>white

Master Customer: Brian >>Page 3
Customer: Account:
Brian 5555 >>white
Brian 8888 >>white
Joe 1212 >>gray
Tom 3434 >>white

Thanks for the help

Answer 3

Hello,

Will you try this one as per your description

=IIF(RunningValue((System.Text.RegularExpressions.Regex.Replace(Fields!ID.Value,"[0-9]", "").Trim(";")),CountDistinct, Nothing) MOD 2 = 1, "Gainsboro", "White")

---

blog:My Blog/

Hope this will help you !!!
Sanjeewan

Answer 4

When I try to do what you suggested I am getting an error. The replace function is underlined indicating an error

Also I am not sure what this statement is doing. Can you explain this to me?

What is the point of the Replace and Trim methods?

Thanks

Answer 5

Hello,

This System.Text.RegularExpressions.Regex.Replace(Fields!ID.Value,"[0-9]", "").Trim(";"))  is replacing numeric values from string values  for more detail

see this article

http://www.bi-rootdata.com/2012/11/how-to-remove-all-numericnon-numeric.html

I have reproduced your problem at my end and this function was working to me

---

blog:My Blog/

Hope this will help you !!!
Sanjeewan

Answer 6

I'm not sure what the point is replacing numeric values with string values? The goal is to change the backgorund color every time the data changes. This does not to solve that.

Answer 7

Hi,

You need to use analytic function in your SQL, LAG or LEAD for comparison against the current row then either return a 1 or 0 for the column. Then use this column as the basis for your fill color. In the example below, the dataset will have a field change_indicator either 1 or 0:

  SELECT lastname,
         CASE WHEN lastname <> prev_lastname THEN 1 ELSE 0 END change_indicator
    FROM (SELECT lastname, LAG(lastname) OVER (ORDER BY lastname) prev_lastname
            FROM users)
ORDER BY lastname

---

Hope this helps!

k r o o t z

Answer 8

I am not familar with "LAG"

also where do I put this code?

in my text box (in my fill property) cant I just do something like:

if current field value = previous field value then

set current field backgroundcolor to previous field backgroundcolor

else

if previous field backgroundcolor = white then

"gray"

else

white

thanks

Answer 9

Hi,

Right click on your report, goto properties and add this in the code:

Dim Public switch as integer

public function FillColor(v1 as string, v2 as string,g1 as string, g2 as string) as integer
if v1<>v2 then 
   switch=iif(switch=0,1,0)
end if
if g1<>g2 then
switch=0
end if
return switch
end function

It will accept 4 parameters. 

Then on your row, fill color, enter this expression:

=iif(Code.FillColor(Fields!Customer.Value,Previous(Fields!Customer.Value),Fields!MasterCustomer.Value,Previous(Fields!MasterCustomer.Value))=0,"White","Gray")

I've tested this and it works. It will also make White as the 1st fill color everytime the group changes, that's why you are also passing the MasterCustomer current and previous value.

---

Hope this helps!

k r o o t z

Answer 10

I copied the function exactly into the report properties code as directed and copied the expression into the fill color as directed.

However the expression has an error, the FillColor has a red squiggle line underneath it.

If I type =iif(Code and hit period FillColor is not available, only Equals, GetHashCode,GetType,ReferenceEquals,ToString

Thanks

Answer 11

Hi,

If you type the expression anyway, ignoring the red squiggle, when you run the report, do you get an error? What are you using for report development? I used Report Builder and everything worked well.

---

Hope this helps!

k r o o t z

Answer 12

I am using SQL Server 2008 Business Intelligence Development Studio

I tried running the report anyway and got the error message: Error: Subreport could not be shown.

Note: I have a main report and a subreport. It is the subreport where I am trying to get the alternating color.

I put your code in the sub report properties code and got the error. I added it to the main report properties code as well and still got the error. The main report previews fine

Thanks

Answer 13

Hi James,

 

Please refer to the below link with different options to represent alternate colors.

http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/thread/69ce64e8-9b14-4e64-9882-b8a5141fcfa8

 

Hope it'll help in resolving your issue,thank you...

---

- Arun Gangumalla, Please mark as helpful or answered if it resolves your issue to help others in finding solutions easily.

Answer 14

Hi James,

If you run the subreport by itself, what error do you get? also make sure the fields names in the fill expression are correct. I just assumed you have the field "Customer" and "MasterCustomer".

Also is it possible that the subreport is not shown because of below:

http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/74e51434-1d14-4fb1-9b1c-1a394c187b2f

---

Hope this helps!

k r o o t z

Answer 15

ok I resolved the error.

the problem now is the expression works but only if I add it to the 1st cell in the row (customer name)

trying to get the whole row (the rest of the text boxes in the row) alternating causes the logic not to work

these are my text boxes for the row:

customer name, customer account, source sysem, balance amount, lc amount, exposure amount

If I put the expression in the fill property of the customer account field then the alternating color for the customer name field stops working. I need to have the whole row shaded, not just the 1st cell

thanks

Answer 16

Hi,

I was able to set it against the entire row. See below as sample. 

Of course, the expression using the Code.FillColor(...)

If this doesn't work, you can set always use the expression for each textbox on your row as you did with the first cell.

---

Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

k r o o t z

Answer 17

I am still getting unexpected results.

First I notice you are using a different expression than the one you originally sent me, =iif(rownumber...

originally I was entering it in the individual cells, now as you describe I am entering it in the entire row.

however the alternating colors are not right

Answer 18

Hi James,

I see what's happening. The code is being called several times and switching the variable back and forth to 0 and 1.

Hopefully, this will get it.

1. Modify the code like below. Notice the additional function:

Dim Public switch as integer

public function FillColor(v1 as string, v2 as string,g1 as string, g2 as string) as integer
if v1<>v2 then 
   switch=iif(switch=0,1,0)
end if
if g1<>g2 then
switch=0
end if
return switch
end function

public function getSwitch() as integer
     return switch
end function

2. Highlight the entire row as in previous instruction and enter this expression:

=iif(Code.getSwitch()=0,"White","Gray")

3. Goto the first cell only, and enter the expression that calls the Code.FillColor(...) on the 1st cell only. The code.ColorFill only needs to be called once in a row. This was my previous code, not sure if you made changes to the field names.

=iif(Code.FillColor(Fields!Customer.Value,Previous(Fields!Customer.Value),Fields!MasterCustomer.Value,Previous(Fields!MasterCustomer.Value))=0,"White","Gray")

This should do the trick! Cross fingers!

---

Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

k r o o t z

Answer 19

Bingo! worked like a charm! Exactly what I was looking for!

Thanks for all the support and sticking with this!