Using Stuff and For XML Path to format the string in one column

Recently in one project I needed to create some SSRS reports to present business data. One normal requirement is to use comma to join all the text in one column to one big string. I had experience to use Stuff and For XML Path to join columns, but this time I had a chance to know what is really going on behind For XML Path.  You can check this great blog for details to explain the syntax.

In my project, the first requirement was to join 2 columns with a space, and use a comma to separate each record, but leave a space after the comma.  So my SQL was like this:

stuff((
                    select ', ' + [Name] + ' ' + [Call] From DetailsView
                        for XML path('')
                ), 1, 2, '')

Later, the requirement changed.  User wanted to show each record in a separate line.  So I needed to replace the comma with a line feed.  Char(13) and Char(10) together can make a new line.  In my case, Char(10) is good enough to go.  So I changed my SQL to this:

stuff((
                    select Char(10) + [Name] + ' ' + [Call] From DetailsView
                        for XML path('')
                ), 1, 1, '')
Later, user still wanted to add the comma before the line break, so I just slightly changed to this:

stuff((
                    select ',' + Char(10) + [Name] + ' ' + [Call] From DetailsView
                        for XML path('')
                ), 1, 2, '') 

So now in my report, I can have a formatted string in multiple line.

A difference between Table and List in SSRS

In our project, we needed to create SQL Server Reporting Service reports for users to view tabular data and graphs. We decided using Report Builder 3.0 to build the report templates.

On 2008 when I worked on another project, we had a requirement to support reports. At that time, I wrote an article to compare Crystal Reports, Report Designer in Visual Studio, and Report Designer in SQL Server. I used Report Designer in Visual Studio in that project.

The Report Builder tool is effective and fun, but sometimes it's annoying, so occasionally I would rather directly open the rdl file to modify the XML syntax. 

Problem

One of our reports has the following requirement.  First, it should be grouped under Product.  Every product will have a table to show its related information. The table has two different formats depending on the product type. What I did was to drag a table, add a group, and inside the group, add 2 rows. Then I added one tables to each row, and the row visibility would be decided by the product type.

It worked fine and showed the result users want. However, I noticed that the product name always showed in a separate page if the details table for that product crossed over one page. Report Builder has some properties such as KeepTogether which will push the content to a new page if they cannot be showed in one page. This time we did want the details table to be in the same page as the product name even it could not fit into one page. We didn't want a silly empty page only having the product name showed.

Solution

First and obviously, we thought it should be a easy task and the properties of the report should do the trick. We tried different properties such as KeepTogether, PageBreak, Group Properties, and changed them for both the parent table and children table, but neither of them worked.

I searched around and couldn't find a good solution or anything which claims this is a known issue. I did get this and this stating sub report has this issue but it seems already fixed at some point.

My final solution is totally out of my expectation.  I just changed the parent tablix from a table to a list and the problem was resolved. Lists give you more freedom to control the layout, and probably have less restriction. In the list, I can easily put 2 tables anywhere and control the visibility of them.

Second Thoughts

I was curious about what is different between a table and a list, and why they have different behaviors. So I created 2 new reports and put a list and a table to them respectively, and I used Beyond Compare to compare them. There was really not much difference. As we know, both table and list are called Tablix in the .rdl XML file. The only significant difference is inside the TablixCell, list puts a Rectangle, whereas table puts a Textbox. So I guess that the behavioral difference of List and Table is caused by the design feature of Rectangle and Textbox. KeepTogether probably is not working on a textbox. In the other hand, Rectangle is a loose layout control, and provides more freedom to end users.