SSRS Row Consolidation

Question

I'm running SSRS for Server 2008 R2 and creating reports in Report Builder 3.0

I have this report called missing weeks that shows me weeks from a year where we're missing financial statements from our companies. Right now the report shows all the missing weeks in separate rows of the report, like so:

Group  Cmp_code  town      Owner    Year  Week
001    004       townname  mr ozzy  2012  50
                                          52
                                    2013  25
                                          26
       005       townname  mr bark  2013  32
                                          33
002    235       townname  mr woof  2013  17
ETC..

Is it possible to have all the weeks show up in the same row for that group and cmp_code?

Group  Cmp_code  town      Owner    Year  Week
001    004       townname  mr ozzy  2012  50, 52
                                    2013  25, 26, etc...
       005       townname  mr bark  2013  32, 33, etc...
002    235       townname  mr woof  2013  17

Ideally I would like to accomplish this in SSRS and not alter the stuff i've done on sql server.

Ian Preston Ian Preston · Accepted Answer · 2013-09-27T15:00:27

You can use the LookupSet function to help with this.

Here's my test Dataset:

enter image description here

I added a Calculated Field called CmpYear to the Dataset, with the expression:

=Fields!Cmp_code.Value & CStr(Fields!Year.Value)

We'll use this later on.

Now, make your report table:

enter image description here

You can see there are row groups based on Group, Cmp_code and Year. I've assumed town and Owner are based on Cmp_code.

The most important part is the expression for Week:

=Join(LookupSet(Fields!CmpYear.Value, Fields!CmpYear.Value, Fields!Week.Value, "DataSet1")
  , ",")

Since we're interested in all weeks for a particular company/year combination, we use the Calculated Field based on these as the lookup key - with this LookupSet returns an array of all the weeks in the particular company/year group, then we use Join to turn the array into a delimited list:

enter image description here

Looks pretty good to me. Of course you may have to change slightly for your underlying data, but hopefully this gives you some useful ideas.

SSRS Row Consolidation

2 Answers