SQL Server accessing rows

Question

I have a database where one table is as follows:

Owner Type | Owner Name
---------------------------------
Testing    | abc, def, xyz
Testing2   | ppp
Testing3   | jkl, mno, pqr

In the SQL Report (SSRS), I would like to display something like this:

Testing owners = abc, def, xyz
Testing2 owners = ppp
Testing3 owners = jkl,m mno, pqr

How do I build a report in this way, using SQL Server Report Builder? I understand that a simply query would work using the regular SQL Query mode. However, the difficulty I am facing is that there is only a single table cell where I need to enter the expression to be evaluated (there are more things in the table and the report, this is only a subset). I'm not sure how I can get that done.

Answer 1

There are at least 2 ways to do this

I would recommend doing the catenation in the report itself. Assuming you have already used the wizard to create the dataset, and the wizard has both raw columns in your report already, right click on the OwnerType detail row text box and select Expression (fx). You can then project the required display, e.g.:

=Fields!Owner_Type.Value + " owners = " + Fields!Owner_Name.Value

And then change the column name and delete the second column entirely.

The other way to do this is by projecting this directly in a Sql Query, e.g. by providing the following query by using the configure option on the DataSource:

select Owner_Type + ' owners = ' + Owner_Name as OwnerRow
from [dbo].[Own];

(you could also create a proc or view in SqlServer to do this projection and then bind the report to the proc / view).

Answer 2

If you

SELECT [Owner Type], [Owner Name] FROM Table

You can group by [Owner type] in a table/matrix in your SSRS report and get something that looks like

Testing
Testing2
Testing3

Then you can add what you need after that on a new column f.ex. But grouping is the way to display all [Owner type].

Edit: Suggestion to filter: