Issue Related to IIF Condition in SSRS Expression

0
votes

I have ASP.NET MVC Application having SSRS Reports..

I have written following code in an Expression :

=iif(Fields!Aggr3.Value is nothing,"", iif(cStr(Fields!Aggr3.Value).IndexOf(".") >=0 , Format(Fields!Aggr3.Value,"C"),Fields!Aggr3.Value))

Field Aggr3 contains Dynamic Values it can be either "Null" or Decimal Value or Integer Value.. If value is Decimal I want to Format it with $ Currency. otherwise if value is interger value then i want to saw it as normal value.

so i written above expression.. but when generating Report column value displays #Error

i changed iif Condition to Switch condition as follows :

=Switch(IsNothing(Fields!Aggr2.Value), "", 
not IsNothing(Fields!Aggr2.Value),
Switch(cstr(Fields!Aggr2.Value).IndexOf(".") >=0,Format(Fields!Aggr2.Value,"C"),
cstr(Fields!Aggr2.Value).IndexOf(".") <0,Fields!Aggr2.Value))

but it also throws an Error..

what is the error and how to solve it ??

Thanks

2
c#asp.net-mvcreporting-servicesssrs-2008

2 Answers

1
votes

Your issue here is with using the .IndexOf method to test for a "." - this causes an error when used on a null value. You should use the SSRS function inStr() instead, like this:

inStr(cStr(Fields!Aggr3.Value), ".") >=0

This should avoid the errors - I tested this using some sample data and it seemed fine.

0
votes

Many people have misunderstanding of the way IIF works. In IIF, all the parameters are evaluated whether the first parameter is true or false, so in your case, if the Fields!Aggr3.Value is nothing, it will still evaluate the expression "iif(cStr(Fields!Aggr3.Value).IndexOf(".") >=0 , Format(Fields!Aggr3.Value,"C"),Fields!Aggr3.Value)", and definitely, it will show an error if it tries to evaluate this expression (when Aggr3 is null). Please see this article for more info: http://softwarecafeblog.blogspot.com/2010/04/iff-statement-in-vbnet.html

One suggestion would be to put your condition as a normal "if" in the report code. for information on how to add report code see this article https://msdn.microsoft.com/en-us/library/ms156028.aspx