SSRS IIF expression

Question

In my SSRS report I want PolDiscountPerc textbox from my query to make blank when PolStockCode in a row is empty or empty string, however if PolStockCode contains a value then format PolDiscountPerc textbox to display it as a percentage.

=IIF(IsNothing(Fields!PolStockCode.Value) or Fields!PolStockCode.Value is "", "",(Format(Fields!PolDiscountPerc.Value, "0.00") & "%"))

The problem with following query is that it either does nothing or sets the textbox to blank for every row in the table despite the fact that most of them do contain the PolStockCode. Where have i gone wrong?

score 0 · Answer 1 · answered Oct 07 '16 at 14:51

0

is can only be used for comparing to nothing. you need to use =.

=IIF(IsNothing(Fields!PolStockCode.Value) or Fields!PolStockCode.Value = "", "",(Format(Fields!PolDiscountPerc.Value, "0.00") & "%"))

answered Oct 07 '16 at 14:51

SMM

2,225
1
19
30

I tried that before and it doesn't blank records where PolStockCode is blank. I forgot to mention that I've got a parent group on PolStockCode but i dont think that matters. – user1800674 Oct 07 '16 at 15:02

score 0 · Answer 2 · answered Oct 07 '16 at 14:54

Can you try it by removing is to = and make the expression to single unit ().

=IIF
(
  (IsNothing(Fields!PolStockCode.Value) or Fields!PolStockCode.Value = "")
  , ""
  ,(Format(Fields!PolDiscountPerc.Value, "0.00") & "%")
)

OR you can format the percentage externally:

=IIF
(
  (IsNothing(Fields!PolStockCode.Value) or Fields!PolStockCode.Value = "")
  , ""
  ,Fields!PolDiscountPerc.Value)
)

score 0 · Answer 3 · answered Oct 07 '16 at 15:26

I completely forgot this is an nchar field in the database and it wouldn't be an empty string.

Therefore this solved my problem

=IIF((IsNothing(Fields!PolPMStockCode.Value) or Len(Trim(Fields!PolPMStockCode.Value)) = 0), "", Fields!PolDiscountPerc.Value)

SSRS IIF expression

3 Answers3