IIF Statements

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,
RSAfter seeing the full syntax I think you have to use some more "iif's " in
between before "Address:" , if you can explain in plain language what exactly
you are trying to display. ie something like if the first conditions is true
then what and if false then what...
Amarnath
"RSub" 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|||Hi Amarnath,
My report uses a SQL query which is very complex and it has joins from
several diff tables. I was trying to add a calculated field(embedded) to the
data source and I need that to display the address which is addressline1, 2,
city state, zip etc based on some criteria which is the first part of the IIF
statement. I removed the variable Address and the OR and it is working fine.
The latter false part of the statement needs to remove US if it finds it in
the address and not display in the report. I'm trying to make that work now.
Thanks,
Roopa
"Amarnath" wrote:
> After seeing the full syntax I think you have to use some more "iif's " in
> between before "Address:" , if you can explain in plain language what exactly
> you are trying to display. ie something like if the first conditions is true
> then what and if false then what...
> Amarnath
>
> "RSub" 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|||ok, so infact you can nest the iif as well, to get the desired results.
Amarnath
"RSub" wrote:
> Hi Amarnath,
> My report uses a SQL query which is very complex and it has joins from
> several diff tables. I was trying to add a calculated field(embedded) to the
> data source and I need that to display the address which is addressline1, 2,
> city state, zip etc based on some criteria which is the first part of the IIF
> statement. I removed the variable Address and the OR and it is working fine.
> The latter false part of the statement needs to remove US if it finds it in
> the address and not display in the report. I'm trying to make that work now.
> Thanks,
> Roopa
>
> "Amarnath" wrote:
> > After seeing the full syntax I think you have to use some more "iif's " in
> > between before "Address:" , if you can explain in plain language what exactly
> > you are trying to display. ie something like if the first conditions is true
> > then what and if false then what...
> >
> > Amarnath
> >
> >
> > "RSub" 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|||Your first problem is that the IIf currently contains four parameters:
1: Trim(Fields!BillType.Value) = "IN" AND (Fields!User9.Value)= 1
2: "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)
3: Trim("Address") <> "US" and Trim("Address") <> "USA" and
Trim("Address") <> "United States"
4: "Address"
Second, parameter 2 is altogether meaningless for several reasons:
* "Address:" = Format(... is testing if the result of your format
statement matches the string "Address:", which it almost certainly
won't.
* Format() takes two parameters, the object and the format type, and
you only pass one parameter each time.
* Format() is generally used to convert numbers, dates, etc to a
string: for example, Format(1.5, "C") returns $1.50 in the US. You
probably don't even need it for the addresses you're putting together.
* OR operates on two boolean values. Though you have one boolean value
from the "Address:" = Format(... comparison (by accident, I suspect),
I don't see what you're hoping to accomplish with the statement.
And third, parameter 3 will always return true - Trim("Address") will
always return "Address" which will never match the variations on "US".
On Apr 23, 5:42 pm, 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