Change Color of Textbox Depending on Value

He is my question depending on the code field from my database I need to display a different color. The field is a detail in my table with just text displaying the title of the report. I have the actual code value in another column. I then go to the background color expression and am using this code:

=Iif(First(Fields!Code.Value, "CodeDataset") = 3, "Green", Iif(First(Fields!Code.Value, "CodeDataset") = 4, "Blue", "Red" ))

They all work if you pull the report up one at a time, but when selecting multi-values you get the color from the first record on each report page no matter what the second, third or so on values are. The code field does display each correct code. Is this being hard-coded to the first record?

Any Idea's ?|||

if your statement:

=Iif(First(Fields!Code.Value, "CodeDataset") = 3, "Green", Iif(First(Fields!Code.Value, "CodeDataset") = 4, "Blue", "Red" ))

You are evaluting the First Fields value "Literally" if you change it to

=Iif(Fields!Code.Value= 3, "Green", Iif(Fields!Code.Value = 4, "Blue", "Red" )) - red will be the default value I think you will see the proper results.

|||

Great thanks it was simple good I tried it with out the first,but I did still have the "DataSet" in there. What is first called is it a function so I can do some research. Also how do you do is null in an expression, and convert data types for a value? Thanks so much for your help!!!

=Iif(Fields!Days.Value is Null, "AliceBlue", "White")

|||

I tried this code for my returntime field: Do I need to do a data conversion?

=Iif(Fields!ReturnTime.Value <= 0, "AliceBlue", "Transparent")

Build complete -- 0 errors, 0 warnings

[rsRuntimeErrorInExpression] The BackgroundColor expression for the textbox ‘Days’ contains an error: Operator '<=' is not defined for type 'Date' and type 'Integer'.

Preview complete -- 0 errors, 1 warnings

|||

Humm,

I think its the "<=" should work try Fields!ReturnTime.Value < 0 or Fields!ReturnTime.Value = 0 instead.

Change Color of Textbox Depending on Value

I have the following:

=IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") < 10.0, "Yellow", "Black")

Doesnt appear to be changing color to the numbers, actually its just putting the work black in all the cells in the report, the nubmer are % numbers, like 10.53% of 3.45%, and if its great than 10.00 % I want the textbox to show up yellow, if its >10% i want it to show up normal. Any help would be greatly appreciated.

Hi Duane,

You have the right idea, but the comparison may be not what you are looking for. I think a link to RS Expressions will help you out a good bit here. The link should explain better then I can how to use different expresssions.

http://msdn2.microsoft.com/en-us/library/ms157328.aspx

|||

Hi Duane! Are you putting your expression in the BackgroundColor property of the textbox. From what you described, it sounds like you are putting it in the value property.

Jennifer

|||

Getting much closer, not it changes all the textboxes to yellow:

=IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") >= 10, "Yellow", "White")

I only want the ones above 10% to be yellow, otherwise white.

|||

I was placing it in the wrong place @. first, now I realized it, and am getting closer if you read my previous post.

Thanks

|||

Duane Haas wrote:

Getting much closer, not it changes all the textboxes to yellow:

=IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") >= 10, "Yellow", "White")

I only want the ones above 10% to be yellow, otherwise white.

Any ideas anyone on why the entire range of cells are yellow and not just the one above 10%?

|||

Getting much closer, not it changes all the textboxes to yellow:

=IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") >= 10, "Yellow", "White")

I only want the ones above 10% to be yellow, otherwise white.

Any ideas anyone on why the entire range of cells are yellow and not just the one above 10%?

|||

Duane,

Could it be summing this value for all rows in dataset and since the total value is greater than 10, all detail rows will be yellow? Maybe remove the sum function and only evaluate the value itself for each row.

T

|||

I tried that and than this is what I get:

h:\sqlreports\snaps\Report1.rdl The background color expression for the textbox ‘textbox2’ references a field outside an aggregate function. Value expressions in matrix cells should be aggregates, to allow for subtotaling.

h:\sqlreports\snaps\Report1.rdl The background color expression for the textbox ‘textbox2’ contains an error: [BC30455] Argument not specified for parameter 'FalsePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.

Not sure where to go from here, this is a matrix style report

|||

Sorry Duane, I have yet to build a matrix style report, so I have no more suggestions..

T

|||anybody have any ideas?

Cell comments in Excel Export

Is it possible to include a cell comment when exporting a report to Excel? My first guess was that the Textbox.ToolTip property might do the trick, but no luck.

Example: I am calculating a textbox based on the age of a particular value in days and conditionally formatting it. In the Excel export, I would like to have a cell comment (i.e., "insert comment" from the context menu in Excel) that shows how many days past the age limit the item is when it is formatted.

Thanks much for any help!

Through conventional export methodologies? No. But ofcourse through VBA/ADO anything is possible.

Adamus