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,","")
more ▼

asked Jul 28, 2015 at 09:34 AM in Default

avatar image

1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Jul 28, 2015 at 12:28 PM

avatar image

4.9k 41 51 58

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 28, 2015 at 09:34 AM

Seen: 95 times

Last Updated: Jul 28, 2015 at 12:31 PM

Copyright 2017 Redgate Software. Privacy Policy