none
City,state,country not coming properly when one column not Exist

    Question

  • Hi,

    i am using below exprssion for loation of Supplier.

    =SWITCH(
    IsNothing(Fields!MSUPCITY.Value) AND IsNothing(Fields!MSUPSTATE.Value) AND IsNothing(Fields!MSUPCOUNTRY.Value),"",
    IsNothing(Fields!MSUPCITY.Value),Fields!MSUPCOUNTRY.Value,
    IsNothing(Fields!MSUPCITY.Value),Fields!MSUPSTATE.Value,
    IsNothing(Fields!MSUPCOUNTRY.Value),Fields!MSUPSTATE.Value,
    True,Fields!MSUPCITY.Value+", "+Fields!MSUPSTATE.Value + ", " + Fields!MSUPCOUNTRY.Value
    )

    but when ever City is not there in Database output is coming with one extra (,) like----    ,state, Country

    how to Avoid this Extra comma somtimes it's coming in between when sate is not there.

    Any Suggestion plz  let me know.

    Monday, April 22, 2013 10:20 AM

Answers

  • Hello Akhil,

    My previous expression didn't take all situations into account (sorry about that), here's an updated version:

    =Fields!MSUPCITY.Value
    	& IIF(Len(Fields!MSUPCITY.Value) > 0 and Len(Fields!MSUPSTATE.Value) > 0, ", ", "")
    	& Fields!MSUPSTATE.Value
    	& IIF(Len(Fields!MSUPCOUNTRY.Value) > 0 and Len(Fields!MSUPSTATE.Value) + Len(Fields!MSUPCITY.Value) > 0, ", ", "")
    	& Fields!MSUPCOUNTRY.Value

    From a maintenance perspective, I believe this is the cleanest possible solution.


    MCITP SQL Server 2008 (BI & DEV); MCTS SQL Server 2008 (BI, DEV & DBA)
    Check out my articles at BI: Beer Intelligence?

    • Marked as answer by Akhil_ji Tuesday, April 23, 2013 10:29 AM
    Tuesday, April 23, 2013 9:55 AM

All replies

  • Try this:

    =SWITCH(
    IsNothing(Fields!MSUPCITY.Value) AND IsNothing(Fields!MSUPSTATE.Value) AND IsNothing(Fields!MSUPCOUNTRY.Value),"",
    IsNothing(Fields!MSUPCITY.Value),Fields!MSUPCOUNTRY.Value,
    IsNothing(Fields!MSUPCITY.Value),Fields!MSUPSTATE.Value,
    IsNothing(Fields!MSUPCOUNTRY.Value),Fields!MSUPSTATE.Value,
    True,Fields!MSUPCITY.Value+", "+Fields!MSUPSTATE.Value + IIF(IsNothing(Fields!MSUPSTATE.Value), "", ", ") + Fields!MSUPCOUNTRY.Value
    )


    MCITP SQL Server 2008 (BI & DEV); MCTS SQL Server 2008 (BI, DEV & DBA)
    Check out my articles at BI: Beer Intelligence?

    Monday, April 22, 2013 12:52 PM
  • ow btw, your expression has two identical conditions, I don't think that's correct?  These are the lines I'm referring to:

    IsNothing(Fields!MSUPCITY.Value), Fields!MSUPCOUNTRY.Value,
    IsNothing(Fields!MSUPCITY.Value), Fields!MSUPSTATE.Value,


    MCITP SQL Server 2008 (BI & DEV); MCTS SQL Server 2008 (BI, DEV & DBA)
    Check out my articles at BI: Beer Intelligence?

    Monday, April 22, 2013 12:54 PM
  • If you explain in words and with some examples what the expected outcome should be, perhaps we can help to write a cleaner expression.

    MCITP SQL Server 2008 (BI & DEV); MCTS SQL Server 2008 (BI, DEV & DBA)
    Check out my articles at BI: Beer Intelligence?

    Monday, April 22, 2013 12:56 PM
  • Hi Valentino,

    please see below outputs of report:--(extra comma's are coming in report preview)

    Report  preview
    ID Name Location
    1 qcv city,state,country
    IF any   supplier not fill his state  than out   put
    ID Name Location
    1 qcv city,country
    But output   is coming like this
    ID Name Location
    1 qcv city,,country
    IF any   supplier not fill his City (means in DB city column is null) than out put
    ID Name Location
    1 qcv , Sate,country
    Monday, April 22, 2013 1:06 PM
  • Hi Akhil,

    USe the below expression this will work for you

    =iif(isnothing(Fields!a.Value) and isnothing(Fields!b.Value) and isnothing(Fields!c.Value),"",
    iif(isnothing(Fields!a.Value),
                                 iif(isnothing(Fields!b.Value),
                                                              Fields!c.Value,Fields!b.Value & iif(isnothing(Fields!c.Value),nothing,", " & Fields!c.Value)),
    Fields!a.Value & iif(isnothing(Fields!b.Value),
                                                   iif(isnothing(Fields!c.Value),nothing,", " & Fields!c.Value),", " & Fields!b.Value
                                                   & iif(isnothing(Fields!c.Value),nothing,", " & Fields!c.Value))))

    Note: a->city   b->State  c-> country change accordingly with your fields 


    Sreekanth Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.*****

    Monday, April 22, 2013 1:17 PM
  • Hi Sree

    i m using below expression  error coming (Type char "&" does not match declared data type object.)

    i changed this & to AND but still getting error , plz let me know if Any!!

     


    =iif(isnothing(Fields!MSUPCITY.Value) and isnothing(Fields!MSUPSTATE.Value) and isnothing(Fields!MSUPCOUNTRY.Value),"",
     iif(isnothing(Fields!MSUPCITY.Value),
                                  iif(isnothing(Fields!MSUPSTATE.Value),
                                                               Fields!c.Value,Fields!MSUPSTATE.Value & iif(isnothing(Fields!MSUPCOUNTRY.Value),nothing,", " & Fields!MSUPCOUNTRY.Value)),
     Fields!MSUPCITY.Value & iif(isnothing(Fields!MSUPSTATE.Value),
                                                    iif(isnothing(Fields!MSUPCOUNTRY.Value),nothing,", " & Fields!MSUPCOUNTRY.Value),", " & Fields!MSUPSTATE.Value
                                                    & iif(isnothing(Fields!MSUPCOUNTRY.Value),nothing,", " & Fields!MSUPCOUNTRY.Value))))

    Monday, April 22, 2013 1:37 PM
  • check with this and let me know

    =iif(isnothing(Fields!MSUPCITY.Value) and isnothing(Fields!MSUPSTATE.VALUE) and isnothing(Fields!MSUPCOUNTRY.VALUE),"",
    iif(isnothing(Fields!MSUPCITY.VALUE),
                                 iif(isnothing(Fields!MSUPSTATE.VALUE),
                                                              Fields!MSUPCOUNTRY.VALUE,Fields!MSUPSTATE.VALUE & iif(isnothing(Fields!MSUPCOUNTRY.VALUE),nothing,", " & Fields!MSUPCOUNTRY.VALUE)),
    Fields!MSUPCITY.VALUE & iif(isnothing(Fields!MSUPSTATE.VALUE),
                                                   iif(isnothing(Fields!MSUPCOUNTRY.VALUE),nothing,", " & Fields!MSUPCOUNTRY.VALUE),", " & Fields!MSUPSTATE.VALUE
                                                   & iif(isnothing(Fields!MSUPCOUNTRY.VALUE),nothing,", " & Fields!MSUPCOUNTRY.VALUE))))

    or 

    =iif(isnothing(Fields!MSUPCITY.Value) and isnothing(Fields!MSUPSTATE.VALUE) and isnothing(Fields!MSUPCOUNTRY.VALUE),"",
    iif(isnothing(Fields!MSUPCITY.VALUE),
                                 iif(isnothing(Fields!MSUPSTATE.VALUE),
                                                              Fields!MSUPCOUNTRY.VALUE,Fields!MSUPSTATE.VALUE + iif(isnothing(Fields!MSUPCOUNTRY.VALUE),nothing,", " + Fields!MSUPCOUNTRY.VALUE)),
    Fields!MSUPCITY.VALUE + iif(isnothing(Fields!MSUPSTATE.VALUE),
                                                   iif(isnothing(Fields!MSUPCOUNTRY.VALUE),nothing,", " + Fields!MSUPCOUNTRY.VALUE),", " + Fields!MSUPSTATE.VALUE
                                                   + iif(isnothing(Fields!MSUPCOUNTRY.VALUE),nothing,", " + Fields!MSUPCOUNTRY.VALUE))))


    Sreekanth Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.*****

    Monday, April 22, 2013 1:43 PM
  • Still same Issue before state (,) extra comma is coming :(

    Monday, April 22, 2013 2:01 PM
  • Hi Akhil,

    Is your city,state,country are combined together in a single column or they come from three different columns.If they are from different columns what are the datatypes used for those column?


    Sreekanth Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.*****

    Monday, April 22, 2013 2:22 PM
  • Hi Shree,

    These are three different Column which i m adding in Report Side in one column as  a Location(city+state+Country).

    data Type is varchar for all this column.

    Tuesday, April 23, 2013 7:32 AM
  • Okay, how about this:

    =Fields!MSUPCITY.Value
    	& IIF(Len(Fields!MSUPSTATE.Value) > 0, Fields!MSUPSTATE.Value & ", ", "")
    	& IIF(Len(Fields!MSUPCOUNTRY.Value) > 0, Fields!MSUPCOUNTRY.Value & ", ", "")


    MCITP SQL Server 2008 (BI & DEV); MCTS SQL Server 2008 (BI, DEV & DBA)
    Check out my articles at BI: Beer Intelligence?

    Tuesday, April 23, 2013 8:29 AM
  • Hi Akhil,

    I had checked the same in my example everything is working fine for by using the expression what i send earlier. You can check below.


    Sreekanth Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.*****

    Tuesday, April 23, 2013 9:22 AM
  • Hi Akhil,

    Can you add below code in your data set query as Full address column.

    (CASE WHEN LEFT(CASE WHEN LEFT(ISNULL(address,'')+','+ISNULL(State,'')+','+ ISNULL(City,'') ,1) =',' THEN STUFF(ISNULL(address,'')+','+ISNULL(State,'')+','+ ISNULL(City,''),1,1,'') ELSE ISNULL(address,'')+','+ISNULL(State,'')+','+ ISNULL(City,'')END,1) =',' THEN STUFF(CASE WHEN LEFT(ISNULL(address,'')+','+ISNULL(State,'')+','+ ISNULL(City,'') ,1) =',' THEN STUFF(ISNULL(address,'')+','+ISNULL(State,'')+','+ ISNULL(City,''),1,1,'') ELSE ISNULL(address,'')+','+ISNULL(State,'')+','+ ISNULL(City,'')END,1,1,'') ELSE CASE WHEN LEFT(ISNULL(address,'')+','+ISNULL(State,'')+','+ ISNULL(City,'') ,1) =',' THEN STUFF(ISNULL(address,'')+','+ISNULL(State,'')+','+ ISNULL(City,''),1,1,'') ELSE ISNULL(address,'')+','+ISNULL(State,'')+','+ ISNULL(City,'')  END END)as 'FullAddress'

    FullAddress column have Address+State+City data.

    Let me know if you have any questions.

    Vaishu Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem



    Vaishu

    Tuesday, April 23, 2013 9:26 AM
  • Hello Akhil,

    My previous expression didn't take all situations into account (sorry about that), here's an updated version:

    =Fields!MSUPCITY.Value
    	& IIF(Len(Fields!MSUPCITY.Value) > 0 and Len(Fields!MSUPSTATE.Value) > 0, ", ", "")
    	& Fields!MSUPSTATE.Value
    	& IIF(Len(Fields!MSUPCOUNTRY.Value) > 0 and Len(Fields!MSUPSTATE.Value) + Len(Fields!MSUPCITY.Value) > 0, ", ", "")
    	& Fields!MSUPCOUNTRY.Value

    From a maintenance perspective, I believe this is the cleanest possible solution.


    MCITP SQL Server 2008 (BI & DEV); MCTS SQL Server 2008 (BI, DEV & DBA)
    Check out my articles at BI: Beer Intelligence?

    • Marked as answer by Akhil_ji Tuesday, April 23, 2013 10:29 AM
    Tuesday, April 23, 2013 9:55 AM
  • Hi,

    when only State in DB and city and country is NULL than Out put is  --------> Sate, (extra Comma is There at the End)

    Regards,

    Akhilesh

    Tuesday, April 23, 2013 10:25 AM
  • Hi Valentino,

    Fields!MSUPCITY.Value
    & IIF(Len(Fields!MSUPCITY.Value) > 0 and Len(Fields!MSUPSTATE.Value) > 0, ", ", "")
    & Fields!MSUPSTATE.Value
    & IIF(Len(Fields!MSUPCOUNTRY.Value) > 0 and Len(Fields!MSUPSTATE.Value) + Len(Fields!MSUPCITY.Value) > 0, ", ", "")
    & Fields!MSUPCOUNTRY.Value

    It;s working fine in All cases. Thanks A Lot!! , last 2-3 days i trying to resolve this

    Thanku :) :)

    Tuesday, April 23, 2013 10:57 AM
  • Nice, glad to hear I could help!

    PS: don't forget to vote up :)


    MCITP SQL Server 2008 (BI & DEV); MCTS SQL Server 2008 (BI, DEV & DBA)
    Check out my articles at BI: Beer Intelligence?


    Tuesday, April 23, 2013 11:04 AM
  • Hi Akhil,

    Can you add below code in your data set query as Full address column.

    (CASE WHEN LEFT(CASE WHEN LEFT(ISNULL(address,'')+','+ISNULL(State,'')+','+ ISNULL(City,'') ,1) =',' THEN STUFF(ISNULL(address,'')+','+ISNULL(State,'')+','+ ISNULL(City,''),1,1,'') ELSE ISNULL(address,'')+','+ISNULL(State,'')+','+ ISNULL(City,'')END,1) =',' THEN STUFF(CASE WHEN LEFT(ISNULL(address,'')+','+ISNULL(State,'')+','+ ISNULL(City,'') ,1) =',' THEN STUFF(ISNULL(address,'')+','+ISNULL(State,'')+','+ ISNULL(City,''),1,1,'') ELSE ISNULL(address,'')+','+ISNULL(State,'')+','+ ISNULL(City,'')END,1,1,'') ELSE CASE WHEN LEFT(ISNULL(address,'')+','+ISNULL(State,'')+','+ ISNULL(City,'') ,1) =',' THEN STUFF(ISNULL(address,'')+','+ISNULL(State,'')+','+ ISNULL(City,''),1,1,'') ELSE ISNULL(address,'')+','+ISNULL(State,'')+','+ ISNULL(City,'')  END END)as 'FullAddress'

    FullAddress column have Address+State+City data.

    Let me know if you have any questions.

    Vaishu Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem


    Vaishu

    Tuesday, April 23, 2013 1:49 PM