CODEWITHSUNDEEP
sqlvisual-studio-2010visual-studioreporting-services
dcdeez
dcdeez

Reputation: 43

SSRS Combining values within columns from multiple rows when grouped

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.

Upvotes: 4

Views: 36982

Answers (2)

Ian Preston
Ian Preston

Reputation: 39586

With your data:

enter image description here

And a table grouped on Company:

enter image description here

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:

enter image description here

Upvotes: 10

kyzen
kyzen

Reputation: 1609

Your problem is that it's working as intended - the LOOKUPSET() function is returning all records from your dataset where the Company matches. You need to either tighten your criteria in your use of the LOOKUPSET() function, or add some custom code to go through the returned array and purge duplicates.

One option for tightening up the lookup might be to add a calculated field to your dataset that concatenates the Company name and the Year together, which, at least looking at your sample data, would provide the slightly more unique key you're looking for.

Upvotes: 0

Related Questions