title: SSRS Combining values within columns from multiple rows when grouped
body: <p>I feel like this should be relatively easy to do in a SSRS report. Using VS 2010. I have a table that comes in from a basic sql query. Just dropping the columns into the a table in visual studio. I want to group the table by company first, which I do via the row group properties. I have a table that looks like this. </p>
  
  <pre><code> Company     Contact     ContactSub    SubCert    Year
  
    Bank3       Joey        Steven.B      A         2010
    Bank2       Dave        James         A         2010
    Bank2       Dave        Steve         B         2010
    Bank2       Dave        Mark          B         2010
    Bank2       Dave        James         A         2011
    Bank2       Dave        Steve         A         2011
    Bank2       Dave        Mark          B         2011
    Bank2       Dave        James         A         2012
    Bank2       Dave        Steve         A         2012
    Bank2       Dave        Mark          A         2012
  </code></pre>
  
  <p>I now want to combine the Contact Subs and their subcert joined into one row. BUT only using the most recent year. Because some ContactSub may have had their SubCert upgraded to an A from a B.</p>
  
  <pre><code> Company       Contact            ContactSub            SubCert     Year
  
    Bank3         Joey             Steven.B               A           2010
    Bank2         Dave             James,Steve,Mark       A,A,A       2012
  </code></pre>
  
  <p>I added an additional gorup by property, the "Year" column to the row and used this formula for the ContactSub and SubCert columns in the table:</p>
  
  <pre><code>=Join(LookupSet(Fields!Company.Value,Fields!Company.Value,Fields!SubCert.Value,"DataSet Name"),",")
  </code></pre>
  
  <p>But this returned me:</p>
  
  <pre><code> Company     Contact     ContactSub                 SubCert     Year
  
    Bank3       Joey        Steven.B                   A          2010
    Bank2       Dave        James,Steve,Mark,James     A,B,B,A,   2012
                            Steve,Mark,James, Steve    A,B,A,A,
                            Mark                       A
  </code></pre>
  
  <p>How could I clarify my formula to make it say for only the newest year instead of using the values for all years?</p>
  
  <p>Hope this makes sense.</p>