sql server reporting services: expression based on multiple values in a dataset -- sql server reporting services

You're in the

sql server reporting services

group:

expression based on multiple values in a dataset

l.holmes
10/24/2005 2:34:07 PM

sql server reporting services: Hi

I am trying to seth the background colour of a textbox to either red or
green depending on the multiple values in my dataset.

Each row of my dataset has a boolean value for Pass. The background colour
of the text box needs to be green if all rows in my dataset have a Pass
value of True but if only one row in the dataset has a Pass value of False,
the background colour needs to be set to red.

I have tries the obvious expression =IIF(Fields!Pass.Value = True, "Green",
"Red") but this will set the background to green again if their is a row in
the dataset with a Pass value of True after the row which had the Pass value
of False.

I have also tried counting the number of rows with a specific value but this
does not seem to work.

=IIF(count(Fields!Pass.Value = False) = 0, "Green", "Red")

Does anyone have any ideas how to get what I want?

Thanks

Lewis Holmes
eNate

Re: expression based on multiple values in a dataset

Robert Bruckner [MSFT]
10/24/2005 9:18:22 PM

Try this for multiple values:
=iif(Sum(iif(Fields!Pass.Value, 1, 0)) > 0, "Green", "Red")

-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.

[quoted text, click to view]

Re: expression based on multiple values in a dataset

l.holmes
10/25/2005 11:45:40 AM

Hi Robert

Thanks for the reply.

I do not think this will work still. For example say my dataset has three
rows which have Pass values of True, False and True.

As i understand, using this expression for the first row, the expression
will evaluate to Green as Value is True. Then for the second row the
expression will evaluate to Red as value is False (this is all correct).
However, my problem is that now after evaluating the expression for third
row, the value returned will be Green as Value is true and so sum returns 1.

This is not the behaviour I want as the background colour should be Red if
one or more of the Pass values is false.Using this expression, the result
from the third row is setting the background colour back to green.

Hope this explains the problem better.

Kind Regards

Lewis Holmes
eNate

[quoted text, click to view]

Re: expression based on multiple values in a dataset

v-mingqc NO[at]SPAM online.microsoft.com
10/26/2005 6:59:47 AM

Hi Lewis,

In you case, I understood if there is one backgroup to be Red (false), you
want all following backgroup to be shown as Red (false). If I have
misunderstood your concern, please feel free to point it out.

I am afraid there won't be an easy to accomplish this. You may try using
..net assembly to identify the color according to row number with custom
function.

Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.

home · blog · groups

Questions? Comments? Contact the dn