none
Using Join Function for fields (Values from data set ) in SSRS 2008 RRS feed

  • Question

  • we need the list suppliers in a text box,
    if it were a parameter, we can do it by =join(parameters!Supplier_ID.Value,",")
    but
    if the Supplier Id is a field , join(Fields!Supplier_ID.Value,",") doesnt work!

    how to achieve this.

    Tuesday, June 30, 2009 11:10 AM

Answers

  • As you noted, you can't use the Join function directly to accomplish this because Fields!Supplier_ID.Value returns the value for that row rather than an array of values.  I can offer two suggestions:
    1) If you're trying to display values in a textbox outside of any data region, you could add a Hidden, ReadOnly parameter to the report bound to your data set.  You can then use =join(parameters!Supplier_ID.Value, ",") as you noted above.

    2) If you're trying to display values inside of a data region, consider using a nested List or Table control.  This is the easiest way to show the values from a set of rows.

    Hope this helps,
    John


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, June 30, 2009 5:08 PM
    Answerer

All replies

  • As you noted, you can't use the Join function directly to accomplish this because Fields!Supplier_ID.Value returns the value for that row rather than an array of values.  I can offer two suggestions:
    1) If you're trying to display values in a textbox outside of any data region, you could add a Hidden, ReadOnly parameter to the report bound to your data set.  You can then use =join(parameters!Supplier_ID.Value, ",") as you noted above.

    2) If you're trying to display values inside of a data region, consider using a nested List or Table control.  This is the easiest way to show the values from a set of rows.

    Hope this helps,
    John


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, June 30, 2009 5:08 PM
    Answerer
  • Hi,

    I have same problem and same sitiation in SSRS 2005.

    Can you provide me suggestion or let em know how i can achieve in SSRS 2005 using JOIN Function ?

    Thanks in advance.

    Monday, March 22, 2010 8:31 PM
  • Hi,

    I have same problem and same sitiation in SSRS 2005.

    Can you provide me suggestion or let em know how i can achieve in SSRS 2005 using JOIN Function ?

    Thanks in advance.

    Monday, March 22, 2010 8:31 PM
  • You would have to combine the data in your query, rather than in the report design:

    STUFF(

    (

    SELECT ', ' + Supplier_ID

    FROM SomeTable

    join SomeOtherTable

    on Something

    WHERE SomeCondition

    FOR XML PATH('')), 1, 1, '') as AllSuppliers

     

    Tuesday, March 23, 2010 7:12 PM
  • HI Thanks.

    I have 10-15 columns alog with this in one column i need to display comma separated values.

     

    how to achieve? pls help me.

     

    thanks.

    Tuesday, March 23, 2010 8:18 PM
  • You should be able to simply make the destination cell value to be 

    =Fields!Column1.Value & ", " & Fields.Column2.Value & ...........

     

    Wednesday, March 24, 2010 12:38 PM
  • If you need to do this (I have found occasions where it has been of use) then it can be achieved using a combination of the LookupSet function with a condition which is always satisfied and the JOIN function to loop through the array to achieve this.

    Try something like this:

    =Join(LookupSet(1, 1, Fields!FieldYouWishToList.Value,"dsYourDataSetName"),",")

    Hope this helps.

    Jon


    Wednesday, April 3, 2013 9:43 AM
  • Excellent...

    It worked like charm...

    Thank you


    Regards, Guru

    Thursday, June 4, 2015 10:53 AM
  • Thanks Jon.
    Just used this in a report I am working on and it works a treat.

    Thursday, November 5, 2015 7:03 AM
  • It´s posible to do that without repeted values?
    Thursday, February 11, 2016 11:32 PM
  • Thank you so much. It works........
    Wednesday, June 28, 2017 10:15 AM
  • Nice. Now how do we distinct the list?
    Thursday, July 11, 2019 2:41 PM
  • thank you

    Tuesday, July 16, 2019 7:38 PM