IIF Statements

On Apr 24, 8:42 am, RSub <R...@.discussions.microsoft.com> wrote:
> Hi All,
> The below IIF statement is not working for me.
> =IIf(Trim(Fields!BillType.Value)= "IN" AND (Fields!User9.Value)= 1,
> "Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value & " " &
> Fields!City.Value & " " & Fields!State.Value & " " & Fields!Zip.Value & " " &
> Fields!Country.Value) OR Format(Fields!Addr1_2.Value & " " &
> Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " & Fields!State_2.Value
> & " " & Fields!Zip_2.Value & " " & Fields!Country_2.Value), Trim("Address")
> <> "US" and Trim("Address") <> "USA" and Trim("Address") <> "United States",
> "Address")
>
> Could you please let me know where I am going wrong. I tried several other
> options such as writing custom code, switch, choose statements..I am
> migrating the report from crystal reports to Reporting services. Instead of
> the format in the above expression it was AddressLine1 in Crystal reports
> that had worked fine. Also does anybody know of an alternative for the
> NameFlip function of crystal rpts to use in Reporting svcs'
>
> Thanks in advance,
> RS
That IIF() call is pretty complex - is it possible for you to put some
of this logic in the database layer (e.g. by calling a view)?Thank you for your reply. I actually removed the variable "Address" and the
OR from the IIF statement and it is working fine now. Looks like IIF doesn't
work well with variables and those logical operators.
"Tokes" wrote:
> On Apr 24, 8:42 am, RSub <R...@.discussions.microsoft.com> wrote:
> > Hi All,
> > The below IIF statement is not working for me.
> > =IIf(Trim(Fields!BillType.Value)= "IN" AND (Fields!User9.Value)= 1,
> > "Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value & " " &
> > Fields!City.Value & " " & Fields!State.Value & " " & Fields!Zip.Value & " " &
> > Fields!Country.Value) OR Format(Fields!Addr1_2.Value & " " &
> > Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " & Fields!State_2.Value
> > & " " & Fields!Zip_2.Value & " " & Fields!Country_2.Value), Trim("Address")
> > <> "US" and Trim("Address") <> "USA" and Trim("Address") <> "United States",
> > "Address")
> >
> > Could you please let me know where I am going wrong. I tried several other
> > options such as writing custom code, switch, choose statements..I am
> > migrating the report from crystal reports to Reporting services. Instead of
> > the format in the above expression it was AddressLine1 in Crystal reports
> > that had worked fine. Also does anybody know of an alternative for the
> > NameFlip function of crystal rpts to use in Reporting svcs'
> >
> > Thanks in advance,
> > RS
> That IIF() call is pretty complex - is it possible for you to put some
> of this logic in the database layer (e.g. by calling a view)?
>|||IIF() works well "with logical operators and variables", FWIW.
So, here's a guess about why it didn't work, without reading your expression
very closely:
Assuming there was no actual error on your part, it's possible that Crystal
Reports interpreted the segments of your expression in a different order
than RS is doing. (Different compilers are like that <g>.)
To resolve this you can usually add some nested parentheses to make sure
that the order of evaluation is exactly what you expect, explicitly defined,
even though you got this order by default in your old environment.
However... a piece of advice: if you find yourself writing something like
this you may find it worth your while to write a little VB custom function
instead (embed it in the report) and then invoke the function
(=Code.MyFunc()) rather than writing the expression correctly. It's a lot
easier to read and maintain.
Also, you asked a second question about NameFlip... Does this flip two
values based on the appearance of a comma or something? I'm just guessing by
the name, but if so, something like this should work for you:
Function NameFlip(ByVal LastFirst As String) As String
Dim Result As String, Results As String()
Results = LastFirst.Split(",")
If Results.Length = 2 Then
Result = Results(1).Trim() & " " & Results(0).Trim()
Else
' don't make any assumptions if there are
' no commas or more than one comma
Result = LastFirst
End If
Results = Nothing
Return Result
End Function
If I guessed wrong, ask again, and I'll try to write something appropriate
<s>.
Hope this helps,
>L<
"RSub" <RSub@.discussions.microsoft.com> wrote in message
news:EDEAAEF8-8CD1-4518-9AE3-56FE9E714C50@.microsoft.com...
> Thank you for your reply. I actually removed the variable "Address" and
> the
> OR from the IIF statement and it is working fine now. Looks like IIF
> doesn't
> work well with variables and those logical operators.
> "Tokes" wrote:
>> On Apr 24, 8:42 am, RSub <R...@.discussions.microsoft.com> wrote:
>> > Hi All,
>> > The below IIF statement is not working for me.
>> > =IIf(Trim(Fields!BillType.Value)= "IN" AND (Fields!User9.Value)= 1,
>> > "Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value & " "
>> > &
>> > Fields!City.Value & " " & Fields!State.Value & " " & Fields!Zip.Value &
>> > " " &
>> > Fields!Country.Value) OR Format(Fields!Addr1_2.Value & " " &
>> > Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " &
>> > Fields!State_2.Value
>> > & " " & Fields!Zip_2.Value & " " & Fields!Country_2.Value),
>> > Trim("Address")
>> > <> "US" and Trim("Address") <> "USA" and Trim("Address") <> "United
>> > States",
>> > "Address")
>> >
>> > Could you please let me know where I am going wrong. I tried several
>> > other
>> > options such as writing custom code, switch, choose statements..I am
>> > migrating the report from crystal reports to Reporting services.
>> > Instead of
>> > the format in the above expression it was AddressLine1 in Crystal
>> > reports
>> > that had worked fine. Also does anybody know of an alternative for the
>> > NameFlip function of crystal rpts to use in Reporting svcs'
>> >
>> > Thanks in advance,
>> > RS
>> That IIF() call is pretty complex - is it possible for you to put some
>> of this logic in the database layer (e.g. by calling a view)?
>>