CODEWITHSUNDEEP
sqlreporting-servicessql-server-2012
David
David

Reputation: 593

SSRS Column to contain list of many to many relationship items

I am looking to do a basic report and in my database, I have items in my main table representing hardware, software applications etc with a many to many relationship to the platform it belongs to.

In the event that a record has multiple platforms, I'd like it listen in a column separated by commas

Example of database layout

Database Diagram

So Essentially I'd like to structure my query roughly as follows.

SELECT cat.CategoryName, sub.SubCategoryName, cur.Model, cur.Version, cur.Vendor, -- >pla.PlatformName,  <--
       cur.AvailableDate, cur.EndOfProduction, cur.EndOfSupport, dep.DepartmentName, 
       emp.FirstName + ' ' +emp.LastName AS 'Tech Owner', emp2.FirstName +' ' + emp2.LastName AS 'Tech Contact',
       cur.NumOfDevices, cur.UpgradeDuration, cur.FiscalConsideration
FROM
dbo.Currency Cur
INNER JOIN dbo.SubCategory sub
ON cur.SubCategoryId = sub.SubCategoryId
INNER JOIN dbo.Category cat
ON sub.CategoryId = cat.CategoryId
INNER JOIN dbo.Employee emp
ON cur.OwnerId = emp.EmployeeId
INNER JOIN dbo.Employee emp2
ON cur.ContactId= emp2.EmployeeId
INNER JOIN dbo.Department dep
ON cur.PortfolioOwnerId = dep.DepartmentId
LEFT JOIN dbo.Currency_Dependency cd
ON cur.CurrencyId = cd.CurrencyId
LEFT JOIN dbo.Currency_Affected ca
ON cur.CurrencyId = ca.CurrencyId
LEFT JOIN dbo.Currency_Platform cpla
ON cur.CurrencyId = cpla.CurrencyId
Left JOIN dbo.Platform pla
ON cpla.PlatformId = pla.PlatformId
GO

So where I have platform name in that query, I would like to structure it as that list separated by commas, but I have no idea how I would go about that. Would one of you be able to guide me on the right path should there be one? Id like to start with being able to do so through a basic SQL query if there is such a way. Ultimately I'd like to build a report through SSRS (using SSDT in Visual Studio 2015) through a parameter or a filter but I can look into that a bit more as I'm just learning it and am doing my reading on it.

Thank you so much in advance

Upvotes: 2

Views: 1029

Answers (1)

StevenWhite
StevenWhite

Reputation: 6034

You can concatenate the platforms into a comma-separated string either in the SQL or in SSRS.

There are many articles already on how to do it in the SQL, just search for "For XML Path".

To do it in SSRS you can use a combination of LookupSet and Join.

=Join(LookupSet(Fields!EmployeeId.Value, Fields!EmployeeId.Value, Fields!PlatformName.Value, "DataSet1"), ", ")

The first two arguments would be the fields that you want to group by. The third one is the Platform value that you will be concatenating. The fourth argument is your dataset name.

If you need to make the list distinct, you'll need to add some custom code in the report to handle that. You would place that function inside the Join before the LookupSet.

Upvotes: 1

Related Questions