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?

Chain Multiplication on a column

Can someone point me out a function that return the multiplication of all row contents from a column?

It should work like the sum() function, but instead of the summary.. it will return the chain multiplication result. Here is what I need it to do.

x
---
4
2
3
7

I need a function "multiply" so that when I issued "Select multiply(x) ...group by..." It would return 168 which is 4*2*3*7

I need to do this in DB2, I checked the DB2 UDF, but looks like there is no simple way to create a customized column function like this.

I'll be really appreciated if someone can help.

Quote:

Originally Posted by janet04

Can someone point me out a function that return the multiplication of all row contents from a column?

It should work like the sum() function, but instead of the summary.. it will return the chain multiplication result. Here is what I need it to do.

x
---
4
2
3
7

I need a function "multiply" so that when I issued "Select multiply(x) ...group by..." It would return 168 which is 4*2*3*7

I need to do this in DB2, I checked the DB2 UDF, but looks like there is no simple way to create a customized column function like this.

I'll be really appreciated if someone can help.

--------------
Reply : select exp(sum(ln(val_num))) from test_multiply;|||select exp(sum(ln(val_num))) from test_multiply;

It will solve your purpose

CAST function problem

Hi,

I am facing problem while using CAST function. Here i am using SUM function inside CAST function and assigning the returned value to a variable which is of varchar datatype of length 100. The variable which i am using inside SUM function is big int.

I am doing like this:

@.hour = (select CAST(SUM(totaltime) as varchar(100) from 'Table name'

It was getting error like:

Unable to convert varchar datatype to bigint.

How can i resolve this problem..
Regards,
SaratStart by posting code that will actually compile... Also, give us the data types of each column you are looking at.|||Actually the following code compiles and works as I would expect:

Create Table #X(x bigint)

Insert Into #X Values(1)

Insert Into #X Values(2)

Declare @.hour varchar(100)

Set @.hour = (select CAST(SUM(x) as varchar(100)) from #X)

Select @.hour

Drop Table #X

So I don't really see the problem... I pretty-much copied and pasted your line

|||Just wondering - why not use:

select @.hour = CAST(SUM(x) as varchar(100)) from #X

rather than: SET @.hour = (SELECT ....

CAST function problem

Hi,

I am facing problem while using CAST function. Here i am using SUM function inside CAST function and assigning the returned value to a variable which is of varchar datatype of length 100. The variable which i am using inside SUM function is big int.

I am doing like this:

@.hour = (select CAST(SUM(totaltime) as varchar(100) from 'Table name'

It was getting error like:

Unable to convert varchar datatype to bigint.

How can i resolve this problem..
Regards,
SaratStart by posting code that will actually compile... Also, give us the data types of each column you are looking at.|||Actually the following code compiles and works as I would expect:

Create Table #X(x bigint)

Insert Into #X Values(1)

Insert Into #X Values(2)

Declare @.hour varchar(100)

Set @.hour = (select CAST(SUM(x) as varchar(100)) from #X)

Select @.hour

Drop Table #X

So I don't really see the problem... I pretty-much copied and pasted your line

|||Just wondering - why not use:

select @.hour = CAST(SUM(x) as varchar(100)) from #X

rather than: SET @.hour = (SELECT ....