none
(SSDT 2015) Not sure how i can remove empty newlines in a string, which was created using Join function. RRS feed

  • Question

  • Hello, Please refer to the image i created below. 

    So far, I was able to group by ID and Name and use Join function to consolidate Comment1 column. However, my issue is getting to remove the empty newline that's in there. 

    My code is the following for 'Comment1' column:

    =Join(LookupSet(
      Fields!ID.Value & Fields!Name.Value
    , Fields!ID.Value & Fields!Name.Value  
    , Fields!Comment1.Value
        , "DataSet1"), " " + chr(10) )

    I tried to use a following code i camee across but not able to do so since the column 'Comment1' already is using Formula/Expression. 

    Could someone please assist on how i can proceed? Than you 

    Edit: Should have said "... remove empty newlines in a string Column" instead of just string. 




    • Edited by SpicySoftTofuSoup Wednesday, July 8, 2020 6:31 PM Added an image. Forgot a
    Wednesday, July 8, 2020 6:13 PM

Answers

  • Hello, below is a solution for anyone that might need help in the future. 

    So given you have something like this, 

    =Join(LookupSet(
      Fields!ID.Value & Fields!Name.Value
    , Fields!ID.Value & Fields!Name.Value  
    , Fields!Comment1.Value
        , "DataSet1"), " " + chr(10) )

    Combine it with 

     IIF( IsNothing(Fields!Comment1.Value), " " Char(10), " "). 

    (Combine it as in just replace the last parameter of the first code with the IIF on the bottom. 

    Cya 

    Thursday, July 9, 2020 4:53 AM

All replies

  • Hi SpicySoftTofuSoup,

    This issue is caused by the blank comment value in the original table.

    You can try to add order by clause in dataset so that the blank comment value can appear at the end

    order by Comment1 desc


    Result:

    Best Regards,

    Amelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, July 9, 2020 3:09 AM
  • Hello, below is a solution for anyone that might need help in the future. 

    So given you have something like this, 

    =Join(LookupSet(
      Fields!ID.Value & Fields!Name.Value
    , Fields!ID.Value & Fields!Name.Value  
    , Fields!Comment1.Value
        , "DataSet1"), " " + chr(10) )

    Combine it with 

     IIF( IsNothing(Fields!Comment1.Value), " " Char(10), " "). 

    (Combine it as in just replace the last parameter of the first code with the IIF on the bottom. 

    Cya 

    Thursday, July 9, 2020 4:53 AM