asked
SSRS iif expression
In the following SSRS expression, I need to remove the comma for the last one...but i don't know which one comes first. Ex: A,B,C,D, I am getting the result like above. I don't know which one comes first and which comes last. But I don't want the last comma. Below is the expression I have written: =IIf(Fields!Assignee_VZID.Value = "{Blank}" And (Fields!Assigned_User.Value = "{Blank}" OR Fields!Assigned_User.Value = "blank" OR Fields!Assigned_User.Value = "" ),"",IIf(Fields!Assignee_VZID.Value = "{Blank}" OR Fields!Assignee_VZID.Value = “*badvzID” ,"Bad Assignee_VZID,","")) & IIf(Fields!Assigned_Skill.Value = "{Blank}" OR Fields!Assigned_Skill.Value = "*NA" , "Bad Assigned_Skill,","") & IIf(Fields!Customer_Rollup.Value = "blank" OR Fields!Customer_Rollup.Value = "No match" , "Bad Customer_Rollup,","") & IIf(Fields!Assigned_Team.Value = "{Blank}" , " Bad Assigned_Team,","") & IIf(Fields!Assigned_Tier.Value = "{Blank}" OR Fields!Assigned_Tier.Value = "*NA" OR Fields!Assigned_Tier.Value = "No match" , "Bad Assigned_Tier,","") & IIf(Fields!Owner_VZID.Value = "{Blank}" And (Fields!Owner_User.Value = "{Blank}" OR Fields!Owner_User.Value = "Blank" OR Fields!Owner_User.Value = "" ),"",IIf(Fields!Owner_VZID.Value = "{Blank}" OR Fields!Owner_VZID.Value = “*badvzID” ,"Bad Owner_VZID,","")) & IIf(Fields!Owner_Team.Value = "{Blank}" , "Bad Owner_Team,","") & IIf(Fields!Owner_Tier.Value = "{Blank}" OR Fields!Owner_Tier.Value = "*NA" , "Bad Owner_Tier,","") & IIf(Fields!FTR.Value = "FTR Yes" And Fields!Assignee_VZID.Value<>Fields!RB_User_VZID.Value,"Bad Ftr,","")
Ok... wow! It looks to me like you have about 9 iif() tests whose output is concatenated to the next. So IIf(a = b or a = c, ""," problem a,") & IIf(b = c or b = d, ""," problem b,") & [and so on] If that's the case you won't know where the final comma is coming from because it could be any one of those tests as long as all the subsequent tests return an empty string (a ""). A quick and dirty solution is to wrap the entire expression in another iif that trims off the final character if it is a comma. Another option might be to change to a leading comma instead of a trailing comma but that might move your surplus comma to the front. So IIf(a = b or a = c, "","problem a") & IIf(b = c or b = d, "",", problem b") & [and so on] The real solution though, is better data design. Complex business logic does not belong hidden away in the report. This stuff belongs in a business rules table that you join your data to. You can use TSQL to output the final string. One problem is that it's very hard to read. Sooner or later this code is going to be inherited by someone like me (not so bright) and they are going to curse you for it.
16 People are following this question.
