Background colour

hi there ,
if a question abot ms report server.
i have two fields ,
field1 : name = gender value = 1 for men and 2 for woman
field 2 : name = lastname value = for example "jenkins"
so what i want to do is if gender = 1 (men) then i want to set the background colour of the field2(name) blue
else (gender = 2) i want to set the background colour of the field2(name) red.
do anyone know how i could do this ??
i'am a "newcommer" in the world of reporting services ,i always used crystal report for creating my reports.
thanks
patrickHi,
Here is a trick but most of programmers do not recommend it (Me too).
You fire the query as
Select [Name]=(Case When Gender=1 then '<font style=''backgroundcolor:blue;''>' + lastname + '<font>' Else '<font style=''backgroundcolor:red;''>' + lastname + '<font>' End )
Remember this is not recommended but you can use it in urgency.
Regards,
Hemchand.|||

Hello:
Is Gender ALWAYS a 1 or a 2?

In the field (Field2:name=LastName) within Report Layout - Select Properties (Right Side under Solutions Explorer) -

Click on BackGround (you will get a down arrow and scroll and look for Expression - Expressions is always at the top of the list)

In the Expression Box enter the following:

=iif(Field!:Gender.Value = "1", "Blue", "Red")
I probably do not have your field named correctly but you can enter =iif(THEN SELECT YOUR FIELD FROM THE DATASET and then append the remainder of the above statement.
Hope this helps!
Best Regards,
Joe

AVG function on an integer column- truncation

When I use the AVG Function on an integer column, the result is truncated

Example:

Select AVG(field1) from table1

Field1 is an int field and has 4 rows with the values 114,115,115 and 115. This will return 114.

I can get the correct result by using the following SELECT:

SELECT CAST(AVG(CAST (field1 as decimal(18,1)))+ .5 as int) from table1

Am I missing something here? Is there an simpler way to do this?

Any help will be appreciated.

Steve D.

You are correct on the problem, but I am certain you can do it a little easier. Try this example:

DECLARE @.v_test TABLE ([num] INT)

INSERT INTO @.v_test VALUES(1)

INSERT INTO @.v_test VALUES(2)

INSERT INTO @.v_test VALUES(2)

INSERT INTO @.v_test VALUES(2)

SELECT AVG(CONVERT(DECIMAL,num))

FROM @.v_Test

|||

The problem is that I need the resultant value to be an integer, not a decimal value. The query from above will return 1.75000.

I need the result back as an integer, which was why I had to cast it back to an integer after adding .5. Just looking for a simpler way to do this

|||

I'm not sure I understand. Would your answer be rounded to 2? Converting it to an INT is what it is doing for you.

You could use this to round it. If you want to recast it to INT, that is possible at this point.

SELECT ROUND(AVG(CONVERT(DECIMAL,num)),0)

|||

OR

SELECT CEILING(AVG(CONVERT(DECIMAL,num))) from yourTable

|||

More info.

I want the Average value to round to the nearest integer.

1.8 would round to 2

1.2 would round to 1

Ceiling looks like it would make 1.2 convert to 2.

Edit-

Tested this further- it looks like ROUND is what I need. It returns a decimal number (ex 1.0000), but I can convert that back to int.