• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3370
  • Last Modified:

iif condition in ssrs

Hi,
I have used the following expression in ssrs

=IIf((Fields!ASSET_ID_.Value) LIKE ("*LAB*","*SIGNUP*", "*QDISPLAY*", "*PUB*"),
"Public",
IIf(Fields!DEPARTMENT.Value = "Library"
AND (Fields!ASSET_ID_.Value) LIKE ("*LAP*"),
"Public","Not Public")

and it is giving the error
the value expression for the field ('=IIf((Fields!ASSET_ID_.Value) LIKE ("*LAB*","*SIGNUP*", "*QDISPLAY*", "*PUB*"),
"Public",
IIf(Fields!DEPARTMENT.Value = "Library"
AND (Fields!ASSET_ID_.Value) LIKE ("*LAP*"),
"Public","Not Public")
[BC30198]') expected.


Please help me!!
0
Mounikaa
Asked:
Mounikaa
  • 4
  • 3
  • 2
2 Solutions
 
MounikaaAuthor Commented:
Hello..

Is there any bode to help me!!!!
0
 
CRJ2000Commented:
Is it possible that you're running into problems with null values? Try putting a test for IsNothing before everything else.... For example:

IIF(IsNothing(Fields!ASSET_ID.value),"Nothing",IIF(...

Chris
0
 
MounikaaAuthor Commented:
The following expression worked for me.

=IIf((Fields!ASSET_ID_.Value) LIKE  ("*LAB*") AND (Fields!ASSET_ID_.Value) LIKE  ("*SIGNUP*")
AND (Fields!ASSET_ID_.Value) LIKE  ("*QDISPLAY*") AND (Fields!ASSET_ID_.Value) LIKE  ("*PUB*"), "Public",
IIf(Fields!DEPARTMENT.Value = "Library"
AND (Fields!ASSET_ID_.Value) LIKE ("%LAP%"),
"Public?" , "Not Public"))

But is there any simple way to do this.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
MounikaaAuthor Commented:
for all reesults I got "Not Public"

I don't know whether workin ina right way or not
0
 
CRJ2000Commented:
Your latest has "%" as the wildcards, rather than the "*" that may be causing your issue with the "Not Public" always appearing. To check if your query is working correctly, make sure to pull back the Asset_Id field along with your calculated field.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
try this please :

=IIf((Fields!ASSET_ID_.Value) LIKE  ("*LAB*") OR (Fields!ASSET_ID_.Value) LIKE  ("*SIGNUP*")
OR (Fields!ASSET_ID_.Value) LIKE  ("*QDISPLAY*") OR (Fields!ASSET_ID_.Value) LIKE  ("*PUB*"), "Public",
IIf(Fields!DEPARTMENT.Value = "Library" AND (Fields!ASSET_ID_.Value) LIKE ("%LAP%"),
"Public?" , "Not Public"))

But one question, do we not use % along with the LIKE everwhere in your query as similar to how you have to used in "%LAP%"

i am not sure, whether the below can be right... we need to use % as the wild card matching operator with LIKE..not sure why you had used * for some and % for only one. Test it out.

IIf((Fields!ASSET_ID_.Value) LIKE  ("%LAB%") OR (Fields!ASSET_ID_.Value) LIKE  ("%SIGNUP%")
OR (Fields!ASSET_ID_.Value) LIKE  ("%QDISPLAY%") OR (Fields!ASSET_ID_.Value) LIKE  ("%PUB%"), "Public",
IIf(Fields!DEPARTMENT.Value = "Library" AND (Fields!ASSET_ID_.Value) LIKE ("%LAP%"),
"Public?" , "Not Public"))

Thanks
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
if * is the wild card character which needs to be used in SSRS then replace the % with * in all the occurrances in the given query and give it a try.
0
 
MounikaaAuthor Commented:
Your comments helped me and I got a result.
0
 
CRJ2000Commented:
Glad I could help, Mounikaa.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now