SSRS Combining values within columns from multiple rows when grouped

Question

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.

 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

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.

 Company       Contact            ContactSub            SubCert     Year

  Bank3         Joey             Steven.B               A           2010
  Bank2         Dave             James,Steve,Mark       A,A,A       2012

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:

=Join(LookupSet(Fields!Company.Value,Fields!Company.Value,Fields!SubCert.Value,"DataSet Name"),",")

But this returned me:

 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

How could I clarify my formula to make it say for only the newest year instead of using the values for all years?

Hope this makes sense.

Answer 1

With your data:

And a table grouped on Company:

I use the following expressions:

ContactSub

=Join(LookupSet(Fields!Company.Value & Max(Fields!Year.Value)
    , Fields!Company.Value & Fields!Year.Value
    , Fields!ContactSub.Value
    , "DataSet1"), ",")

SubCert

=Join(LookupSet(Fields!Company.Value & Max(Fields!Year.Value)
    , Fields!Company.Value & Fields!Year.Value
    , Fields!SubCert.Value
    , "DataSet1"), ",")

You can see I'm using Max(Fields!Year.Value) as well as Fields!Company.Value to only match on the highest year in the LookupSet expression.

This gives the required results: