[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1225
  • Last Modified:

rdl if expression

how do i code an expression in a rdl such as:

=iif(Fields!OSTATE.Value = "CA" Fields!Amount.Value = 0)

Open in new window

0
fwstealer
Asked:
fwstealer
  • 7
  • 7
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
In the Amount textbox,, enter this as an expression, replacing ? with whatever you want it to be if <> "CA":

=iif(Fields!OSTATE.Value = "CA", 0, ?)
0
 
fwstealerAuthor Commented:
i just need to set amount should the state be CA - if the state is not CA then it can be whatever the value is
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>if the state is not CA then it can be whatever the value is
in that case, In the Amount textbox, enter this as an expression:

=iif(Fields!OSTATE.Value = "CA", Fields!Amount.Value, "")
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
fwstealerAuthor Commented:
i tried this but no luck.

=iif(Fields!OSTATE.Value = "CA", 0, Fields!Amount.Value)

Open in new window


my state is CA and i was hoping the amount field would be 0 but it is the value from the table
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
=iif(expression, what to do if true, what to do if false)

so

=iif(Fields!OSTATE.Value = "CA", 0, Fields!Amount.Value)

essentially says if the state is CA then show a zero, otherwise show the amount.
Make sure we have that correct.

Is Amount some kind of text value?  If not, you'd need "0" instead of 0.
Is everything spelled correctly?
0
 
fwstealerAuthor Commented:
yes it is a text value and everything is correct.

here is a part of the report:
Date      Trip#      FAR      Tail#      Departure      Arrival      Hours      Credits      Charges
05/09/2011      NNF373      FAR 135      N147SL      Oakland, CA (KOAK)      Medford, OR (KMFR)      1.5      0.0      
                  California Property Tax
            $118.16


CA is part of the Departure Expression. Below is the expression for that:
=Fields!OCity.Value + ", " +
iif(len(Fields!OSTATE.Value)<1,Fields!OCountry.Value,Fields!OSTATE.Value) + " ("+Fields!OICAO.Value +")"

Charges is where the 118.16 fits in and I'd like that to be 0 if the OSTATE is CA.


So i tried the following and it is still showing the 118.16 instead of 0.

=iif(Fields!OSTATE.Value = "CA", "0", Fields!Amount.Value)
and
=iif(Fields!OSTATE.Value = "CA", 0, Fields!Amount.Value)
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Nothing is coming to me, other than it would be worthwhile to display OSTATE separately once, just so you can view it and insure that there are no leading/trailing commas, lower case, etc. that would not make an exact match with "CA"
0
 
fwstealerAuthor Commented:
so i replace the expression for departure and ostate comes in as CA; even then the amount is still not 0. don't understand unless it is the order the report is being put together at run time
0
 
fwstealerAuthor Commented:
so i can't get this to work so I thought about modifying the sql code:

--118.16 as Amount,
		--implement no ca prop tax on departure state of ca --> OAPSTATE.StateAbbreviation
		--implement ca prop tax on arrival state of ca --> DAPSTATE.StateAbbreviation
		Amount =
      CASE OAPSTATE.StateAbbreviation
         WHEN 'CA' THEN '0'
         ELSE 118.16
      END,

Open in new window


the above works fine when the departure state is CA it applies the 0

but it doesn't apply it to the arrival when both are CA.

How do i code this so it works for both conditions?
0
 
fwstealerAuthor Commented:
apparently this doesn't work:

   if(OAPSTATE.StateAbbreviation = 'CA')
      begin
            Amount = 0
            else
            Amount = 118.16
        end
        
        if(DAPSTATE.StateAbbreviation = 'CA')
        begin
            Amount = 118.16
        end,
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Not entirely following the logic here, but in case it helps in T-SQL you can have a nested CASE block to evaluate two columns

SELECT
  CASE WHEN DAPSTATE StateAbbreviation = 'CA' THEN
      CASE WHEN OAPSTATE StateAbbreviation = 'CA' THEN
         -- what you want if both are 'CA'
      ELSE
         -- what you want if DAPSTATE = 'CA' and OAPSTATE <> 'CA'
      END
   ELSE
        -- what you want if neither are 'CA'
   END as column_name
0
 
fwstealerAuthor Commented:
i did this and it seems to be good:

Amount =
       CASE DAPSTATE.StateAbbreviation --arrival
         WHEN 'CA' THEN 118.16
         ELSE 0
       END,

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Good deal.

Most developers I know of have a strong preference towards any logic like this should be in a Stored Proc, and not on the SSRS report, such that SSRS only has to cosmetically display the data provided to it.  

Mostly because it can be made into a function/component that can be used by other reports, and also for impact analysis, i.e. 'if we change x1 to x2 what would all need to change to accommodate it', as this can be done all in the SQL Server without going fishing in .rdl files.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now