Help with Nested IIF expression? 1

[dianemarie]

Hello, we do a lot of If Then Else statements in Crystal, and I need to start converting them to SSRS. An example of (what I think of) as a nested if in Crystal:

if {ordhist.qtyopt} = "1" then "MAX" else
if {ordhist.qtyopt} = "2" then "ALL" else
if {ordhist.qtyopt} = "3" then "APPROX" else
if {ordhist.qtyopt} = "4" then "REUSE"

I've been working with the iif function in SSRS. I tried duplicating it similar to the way I would do it in Excel, but it doesn't like it. Ex: (it's only 2 of the 4 criteria but I stopped once it choked on me)

=iif(Fields!qtyopt.Value=1,"MAX",iif(Fields!qtyopt.Value=2,"ALL"))


Any help would be appreciated. I'm still searching Help as well but not having much luck. Thank you.

 

[dianemarie]

I think I should have included that I am going to Add the nested IF statement as a calculated field, and then drop it on the report. Thanks again.

 

[dianemarie]

I finally found it in Help, under Common Expressions. And it did actually call it a "nested iif". I was dropping off the "else" part of the expression. I just threw in a blank.

=Iif(Fields!qtyopt.Value = "1", "Max",
Iif (Fields!qtyopt.Value = "2", "All",
Iif (Fields!qtyopt.Value = "3", "Approx",
Iif (Fields!qtyopt.Value = "4", "Reuse",""))))

 

[xlbo]

The other way to do this is to use a bit of code

In the code window you could create:

Function Qty_Trans(byVal strVal as string)
dim retVal
Select Case strVal
Case "1"
retVal = "Max"
Case "2"
retVal = "All"
Case "3"
retVal = "Approx"
Case "4"
retVal = "Reuse"
Case Else
retVal = "Invalid"
End Select

return retVal
End Function

You can call this by using

=Code.Qty_Trans(Fields!qtyopt.Value)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[dianemarie]

Thank you Geoff. This worked well and it was a good exercise for me. What advantages do you feel this offers, if any? If I want to add more code, do I just add it to the end of the code window?

While I have you and we're on this track. We stack fields on our reports using multiple detail rows in a table. I'm trying to get stacked headings also.

I can do a page header, but our reports tend to be flexible and we often allow the user to select different options via a parameter. For example, do you want to see order quantity or invoice quantity? Then the heading for that field is an expression (Crystal ex):

if {?Type of Quantities} = "Ordered" then "ORDER QTY" else
if {?Type of Quantities} = "Invoiced" then "INV QTY"

Apparently I can't use an expression in a page header. So I created the expression below and tried to add it to a table header or a group header, but I'm getting #error. Any guidance on how I can go about this? I'm going to try adding it in the code window and calling it as you showed with previous example, see if it likes that better. Thanks again Geoff. Here's my current expression giving me an error - I'm just trying to get nicely stacked field labels at this time, not worrying about parameter options yet:

="LIST NAME" + chr(13) +
"ORDER #" + chr(13) +
"ORDER DATE" + chr(13) +
"CLIENT PO"

 

[xlbo]

Hi

the advantage it offers is resuability and readability - you can use the same common code for various objects. you can also include other options so that you could have a set of translations which are controlled by a translationtype argument e.g.

Function Translate(byVal theValue as string, byVal TranslationType as string)

Select case TranslationType

Case "A"

Select Case TheValue

Case "1"
retVal = "X"
Case "2"
retVal = "Y"
Case else
retVal = "Z"
Case "B"

Select Case TheValue

Case "a"
retVal = "something"
Case "b"
retVal = "something else"
Case else
retVal = "other"

Case "C"

etc etc

End Select

Return retVal
End Function

and yes - you just keep adding more functions to the end of the code window. You can also create your own assemblies that host sets of "common functions" so that they are available to all your reports

not sure about page headers as I don't tend to use them much but for a column header, I have used something like:

=IIF(Parameters!ParameterName.Value = "Orders","ORD QTY","INV QTY)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question