CODEWITHSUNDEEP
sql-servervisual-studioreporting-servicesreporting
Rayk
Rayk

Reputation: 63

SQL Server Report Builder - How to aggregate rows of a group

I am creating a report and have an issue to concatenate strings within a group. E.g. I have the following structure of a table

NameOfPerson | Country | State

I grouped the table by NameofPerson and the sub group Country.

-Name
--Country

Now, if I expand Country I get a list of all States but I would like to have on the sub group lvl of Country a concatination of States.

E.g. 

-Name:Bill
--Country: America | State: Virgina, Texas, California
--Country: Canada| State: Alberta, Ontario

How can I do this?

Thanks and regards,

Upvotes: 1

Views: 1095

Answers (1)

Hannover Fist
Hannover Fist

Reputation: 10880

You can use LookUpSet to look up the states and JOIN to combine them into a string.

=JOIN(LOOKUPSET(Fields!Country.Value, Fields!Country.Value, Fields!State.Value, "DataSet1"),", ")

Depending on your data this may have duplicates. You may want to create a separate dataset of the countries and states.

Upvotes: 1

Related Questions