ssrs expression help

Sue Taylor
Sue Taylor used Ask the Experts™
on
I'm wanting to set a background color in my report to turn red if one condition is like "DOM" and a second condition is either "USA" or "SEECERT".  I have tried to get the syntax correct and haven't figured it out.

=iif(Fields!item_id.Value) Like "%DOM%" AND (Fields!coo.Value <> "USA" OR Fields!coo.Value <> "SEECERT"), 'Red', 'No Color'

Using Visual Studio 2012
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Vaibhav GoelMSBI , SQL Consultant
Commented:
Hello ITworks

Please make an attempt to use changed code ->

=iif(Fields!item_id.Value Like "*DOM*" AND (Fields!coo.Value <> "USA" OR Fields!coo.Value <> "SEECERT"), 'Red', 'No Color')


Or Make an attempt for below


=iif(Fields!item_id.Value Like "%DOM%" AND (Fields!coo.Value <> "USA" OR Fields!coo.Value <> "SEECERT"), 'Red', 'No Color')

Vaibhav
ValentinoVBI Consultant
Most Valuable Expert 2011
Commented:
SSRS expressions and SQL are two different languages. Both the Q and the first A contain a mix of both. SSRS expressions are actually based on VB.NET.  Try using InStr instead of LIKE.

Also, that OR condition in combination with <> doesn't make sense. Depending on what you want it should either be AND or <> should be =.

=iif(InStr(Fields!item_id.Value, "DOM") AND (Fields!coo.Value <> "USA" AND Fields!coo.Value <> "SEECERT"), 'Red', 'No Color')

Open in new window


or

=iif(InStr(Fields!item_id.Value, "DOM") AND (Fields!coo.Value = "USA" OR Fields!coo.Value = "SEECERT"), 'Red', 'No Color')

Open in new window

Sue TaylorProject Manager

Author

Commented:
None of the solutions provided worked.  I would always get an error BC30201.

The ItemID field is a varchar40
The coo field is a varchar255

I'm using Visual Studio 2012

I am entering this expression in the FILL section of Text Box Properties

I want to be able to change the color if the ITEMID contains the characters "DOM" in it and the coo is NOT either "USA" or "SEECERT"

"DOM" is an abbreviation for domestic.  "DOM" can be anywhere within the field.  Examples are:
     62C225BA2G/DOM/LW
     62C225BLIM/DOM
     50F300HCS8/DOM-NP

The coo field stands for country of origin.  Examples of this field are:
CHINA
TAIWAN
USA
SEECERT
JAPAN
INDIA

My goal is to be able to quickly show the report reader an Item ID that is supposed to be domestic having a coo something other than USA or SEECERT.
Sue TaylorProject Manager
Commented:
I'm closer.  I don't have errors but I'm not getting the results how I expected them to be.   I have the following expression in my FILL

=iif(Fields!item_id.Value Like ("*DOM*")  AND
(Fields!coo.Value <> "USA" or Fields!coo.Value <> "SEECERT"), "Red", "No Color")

My results show RED Fill on these two items:

150C450B3N/DOM  COO = CHINA so I expect that to be Red
but
150C475B3N/DOM COO-USA is also Red but this shouldn't be Red because it lists USA as the COO
BI Consultant
Most Valuable Expert 2011
Commented:
Try replacing or with and

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial