none
Row grouping in SSRS Reports RRS feed

  • Question

  • Hi All,

    I am facing a issue while using the row groupings in the SSRS report. Here is the example of it;

    But what is happening is that even the Year column gets grouped as shown in below;


    My client has asked, grouping is not required on the Year field and only Accomplishment field needs to be grouped.

    Please suggest.

    Friday, August 9, 2013 5:47 AM

Answers

  • Hi ,

    Try like this ,

    DECLARE  @sample_data table
    (
    [Year] varchar(5),
    Emp_id varchar(30),
    Name varchar(30),
    Accomplishments varchar(30)
    )
    insert @sample_data values
    ('2007','Emp - 01','Sam','ALC Notes 1'),
    ('2007','Emp - 01','Sam','ALC Notes 2'),
    ('2007','Emp - 01','Sam','ALC Notes 3'),
    ('2007','Emp - 02','John',''),
    ('2007','Emp - 03','Mary',''),
    ('2007','Emp - 04','Akash','')
    --SELECT * FROM @sample_data
    SELECT Year,Emp_id,Name, 
           STUFF((SELECT ' | ' + Accomplishments
                  FROM   @sample_data X WHERE X.Emp_id = Y.Emp_id GROUP  BY Year,Emp_id,Name,Accomplishments
                  FOR XML PATH('')), 1, 2, '') Accomplishments                
    FROM   @sample_data Y
    GROUP  BY Year,Emp_id,Name

    In the report , I wrote an expression for Accomplishments column :

    =Replace(Fields!Accomplishments.Value," |",VBCRLF)


    sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    Saturday, August 10, 2013 6:02 PM
    Moderator
  • Hi Prashant,

    In your scenario, we can use a matrix control to achieve your requirement.  I have tested it on my local environment, the steps below are for your reference.

    1. Drag a Matrix control to your design surface.
    2. Drag [Accomplishments] field to Rows group.
    3. Add a Row group for [Accomplishments] using [Name] as parent group.
    4. Add a Row group for [Name] using [EmpId] as parent group.
    5. Right-click the handle of [EmpId] column>Insert Column>Inside Group-Left
    6. Drag [Year] field to new inserted column.
    7. Right-click the handle of Columns group>Column Visibility and set it to Hidden.

    The report looks like below.

    If you have any questions, please feel free to ask.

    Regards,
    Charlie Liao

    If you have any feedback on our support, please click here.

    Charlie Liao
    TechNet Community Support

    Monday, August 12, 2013 2:37 AM
    Moderator
  • Will this work for you?  Create this dataset:

    IF OBJECT_ID('tempdb..#sample_data') IS NOT NULL  DROP TABLE #sample_data
    
    create table #sample_data
    (
    [Year] varchar(5),
    Emp_id varchar(30),
    Name varchar(30),
    Accomplishments varchar(30)
    )
    
    insert #sample_data values('2007','Emp - 01','Sam','ALC Notes 1'),('2007','Emp - 01','Sam','ALC Notes 2'),('2007','Emp - 01','Sam','ALC Notes 3'),
    ('2007','Emp - 02','John',''),('2007','Emp - 03','Mary',''),('2007','Emp - 04','Akash','')
    
    Select distinct
        p.[Year], p.Name, p.Emp_id
    ,   replace(
            replace(
                replace(
                (
                    select
                        o.Accomplishments as d
                    from #sample_data o
                    where o.Name = p.Name
                    for xml auto
                ), '"/><o d="', '; ')
            , '<o d="', '') 
        , '"/>', '') as SeperatedList
    from #sample_data p
    order by p.Emp_id
    


    Ryan D

    Saturday, August 10, 2013 12:53 AM

All replies

  • Will this work for you?  Create this dataset:

    IF OBJECT_ID('tempdb..#sample_data') IS NOT NULL  DROP TABLE #sample_data
    
    create table #sample_data
    (
    [Year] varchar(5),
    Emp_id varchar(30),
    Name varchar(30),
    Accomplishments varchar(30)
    )
    
    insert #sample_data values('2007','Emp - 01','Sam','ALC Notes 1'),('2007','Emp - 01','Sam','ALC Notes 2'),('2007','Emp - 01','Sam','ALC Notes 3'),
    ('2007','Emp - 02','John',''),('2007','Emp - 03','Mary',''),('2007','Emp - 04','Akash','')
    
    Select distinct
        p.[Year], p.Name, p.Emp_id
    ,   replace(
            replace(
                replace(
                (
                    select
                        o.Accomplishments as d
                    from #sample_data o
                    where o.Name = p.Name
                    for xml auto
                ), '"/><o d="', '; ')
            , '<o d="', '') 
        , '"/>', '') as SeperatedList
    from #sample_data p
    order by p.Emp_id
    


    Ryan D

    Saturday, August 10, 2013 12:53 AM
  • Hi ,

    Try like this ,

    DECLARE  @sample_data table
    (
    [Year] varchar(5),
    Emp_id varchar(30),
    Name varchar(30),
    Accomplishments varchar(30)
    )
    insert @sample_data values
    ('2007','Emp - 01','Sam','ALC Notes 1'),
    ('2007','Emp - 01','Sam','ALC Notes 2'),
    ('2007','Emp - 01','Sam','ALC Notes 3'),
    ('2007','Emp - 02','John',''),
    ('2007','Emp - 03','Mary',''),
    ('2007','Emp - 04','Akash','')
    --SELECT * FROM @sample_data
    SELECT Year,Emp_id,Name, 
           STUFF((SELECT ' | ' + Accomplishments
                  FROM   @sample_data X WHERE X.Emp_id = Y.Emp_id GROUP  BY Year,Emp_id,Name,Accomplishments
                  FOR XML PATH('')), 1, 2, '') Accomplishments                
    FROM   @sample_data Y
    GROUP  BY Year,Emp_id,Name

    In the report , I wrote an expression for Accomplishments column :

    =Replace(Fields!Accomplishments.Value," |",VBCRLF)


    sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    Saturday, August 10, 2013 6:02 PM
    Moderator
  • Hi Prashant,

    In your scenario, we can use a matrix control to achieve your requirement.  I have tested it on my local environment, the steps below are for your reference.

    1. Drag a Matrix control to your design surface.
    2. Drag [Accomplishments] field to Rows group.
    3. Add a Row group for [Accomplishments] using [Name] as parent group.
    4. Add a Row group for [Name] using [EmpId] as parent group.
    5. Right-click the handle of [EmpId] column>Insert Column>Inside Group-Left
    6. Drag [Year] field to new inserted column.
    7. Right-click the handle of Columns group>Column Visibility and set it to Hidden.

    The report looks like below.

    If you have any questions, please feel free to ask.

    Regards,
    Charlie Liao

    If you have any feedback on our support, please click here.

    Charlie Liao
    TechNet Community Support

    Monday, August 12, 2013 2:37 AM
    Moderator
  • Thanks Satya and Charlie,

    Solution provided by you was really helpful.

    @Charlie, I have one more query with the solution you provided:

    Is it possible to place a column with single value after Accomplishment. Because excel creates a multirow cell in the Accomplishment field.


    • Edited by S Prashant Monday, August 12, 2013 9:00 AM
    Monday, August 12, 2013 7:38 AM
  • Hi Prashant,

    Sorry for the delay.

    In your scenario, you want to add a column with single value after Accomplishment column, right? In other words, you want to add a group on the right side of the Accomplishment column, right? In Reporting Services, if we set tablix LayoutDirection to LTR, and add the group on the tablix, then the group column dispaly on the left of the detail column. If we set tablix LayoutDirection to RTL, the group column dispaly on the right of the detail column.

    In your scenario, you want to dispaly the group column on the both left and right side of the detail column, this cannot be done in Reporting Services currently.

    Thank you for your understanding.

    Regards,


    Charlie Liao
    TechNet Community Support

    Monday, August 19, 2013 1:40 AM
    Moderator
  • Thanks Charlie. This was helpful.
    Wednesday, September 18, 2013 8:26 AM