dimanche 11 janvier 2015

Triple Nested IIF with SSRS

I'm using SQL Server 2012. I currently have the following nested IIF statement



=IIF(RowNumber("Period") = 1
, IIF(ISNOTHING(Fields!Row2.Value),""," "&FormatNumber(Fields!Row2.Value,1) & " %")
, Fields!Row2.Value
)


The purpose of this is that the first row of the result set requires a % sign to appear after the Row2.Value for example



client1 1 % 2 % 3 %
client2 4 5 6
client3 7 8 9


That part is working fine. Client1,Client2, Client3 as you see above is a field named Fields!Composite.Value


If Composite = 'Client10' I need to display 'n/a' instead of the Row2.Value, for all others I want to show Row2.Value. The desired result is below.



client1 1 % 2 % 3 %
client2 4 5 6
client3 7 8 9
client10 10 11 n/a


I'm having trouble extending the IIF statement so that this third condition is nested. If I did not need to nest the other IIF conditions the IIF statement would look like this



=IIF(Fields!Composite.Value ="client10","n/a",Fields!Row2.Value)


So I need to combine all three. I was also thinking a SWITCH statement may be better but I'm not yet that adept using SWITCH.


Aucun commentaire:

Enregistrer un commentaire