how to roll values from a field into one row in SSRS

Posted on 2011-10-27
Last Modified: 2012-05-12
Hi , this is a kind of concatenation request. I have two fields Mom and Children in the dataset, Field 1 Mom is top level of Field 2 Children in this SSRS report,  I need to roll all F2 values from many rows into one line, instead to display each row of F2 under each F1.

I DON'T want:

      children 1
      children 2
      children 1
      children 2
      children 3
      children 4
      children 5
      children 1
      children 2
      children 3

I WANT: two columns display the values like this

      children1, children2
      children1, children2, children3, children4, children 5
      children1, children2, children3

It's better related to SSRS report design, any thoughts? urgent, many thanks
Question by:jc50967w
    LVL 21

    Accepted Solution

    I would do it in the dataset instead of in the report.  Something like the following will give you a comma delimited set of data.

       Children = substring(
          (SELECT ( ', ' + t2.Child )
          FROM YourTable t2
          WHERE t1.Parent = t2.Parent
          ORDER BY t2.Parent, t2.Child
          FOR XML PATH( '' )
                      ), 3, 1000 )
    FROM YourTable t1
    GROUP BY t1.Parent


    LVL 100

    Expert Comment

    This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps…
    Hi All, I am here to write a simple article to move SSRS (SQL Server Reporting Services) reports from one server to another. When I have faced the same issue to move reports those were developed by developer on development server and now need to …
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now