question

jones avatar image
jones 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,","")
ssrsexpression
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
GPO avatar image
GPO answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.