Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
How do I create Group with Multi Row Header and Multi Column in SSRS. Can any one help me please?

Unanswered How do I create Group with Multi Row Header and Multi Column in SSRS. Can any one help me please?

  • Saturday, February 09, 2013 3:27 PM
     
     
    Sign In to Vote
    0

    I have created one data set which returns the following results.

    Column1 Column2 Column3 Column4 Column5 Column6 Column7 Column8 Column9 Column10
    Group 1 90 85 80 75 70 65 A100 AAAAAA 750.00
    Group 1 90 85 80 75 70 60 B100 BBBBBBB 650.00
    Group 1 90 85 80 75 70 60 C100 CCCCCCC 550.00
    Group 1 90 85 80 75 70 60 D100 DDDDDD 450.00
    Group 1 90 85 80 75 70 60 E100 EEEEEEEE 475.00
    Group 1 90 85 80 75 70 60 F100 FFFFFFFF 425.00
    Group 2 88 77 66 55 44 33 A100 AAAAAA 750.00
    Group 2 88 77 66 55 44 33 B100 BBBBBBB 650.00
    Group 2 88 77 66 55 44 33 C100 CCCCCCC 550.00
    Group 2 88 77 66 55 44 33 D100 DDDDDD 450.00
    Group 2 88 77 66 55 44 33 E100 EEEEEEEE 475.00
    Group 2 88 77 66 55 44 33 F100 FFFFFFFF 425.00


    I want the report to be like this.

    Group 1
    90 85    
    80 75
    70 65
    A100 AAAAAA 750.00 C100 CCCCCCC 550.00 E100 EEEEEEEE 475.00
    B100 BBBBBBB 650.00 D100 DDDDDD 450.00 F100 FFFFFFFF 425.00
                     
    Sum 3300.00      
     
    Group 2
    88 77
    66 55
    44 33
    A100 AAAAAA 750.00 C100 CCCCCCC 550.00 E100 EEEEEEEE 475.00
    B100 BBBBBBB 650.00 D100 DDDDDD 450.00 F100 FFFFFFFF 425.00
    Sum 3300    


    Thanks.




    • Edited by MKSU Monday, February 11, 2013 12:48 AM
    •  
     

All Replies

  • Wednesday, February 13, 2013 8:51 AM
    Moderator
     
     
    Sign In to Vote
    1

    Hi Mksu,

    I have tested it on my environment, the steps below are for your reference.

    1. Drag a matrix control to the design surface.
    2. Add some row to the matrix. (Right-click the handle of the row=>Insert Row=>Insert group-Below)
    3. Create another report as subreport and create a parameter group for this report.
    4. Drag three tables control to the subreport and drag col8, col9 and col10 to those table.
    5. Use the expressions to set the visibility of the row.
      =iif(fields!col1.Value=parameters!group.value and rownumber(nothing) mod 3=1,false,true)
      =iif(fields!col1.Value=parameters!group.value and rownumber(nothing) mod 3=2,false,true)
      =iif(fields!col1.Value=parameters!group.value and rownumber(nothing) mod 3=0,false,true)
    6. In the main report, drag a subreport control to the matrix, and use the parameter to run the subreport.
      Name   Value
      group   [column1]

    The report looks like below.

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

    Regards,

    Charlie Liao
    TechNet Community Support

     
  • Sunday, February 24, 2013 5:41 PM
     
     
    Sign In to Vote
    0

    Hi Charlie,

    Thanks for the solution. Could you please elaborate the no 3 on how to create the sub report with the parameters and what will be the data set for the sub report?

    Regards

    MK.

     
  • Monday, February 25, 2013 1:44 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hi Mksu,

    The query for subreport would like:
    SELECT column1,column8,column9,column10 from tablename

    Then drag three tables control to the design surface and drag column8,column9 and column10 to thoes tables.

    Use the expression below to set the visibility of first table's row.
    =iif(fields!col1.Value=parameters!group.value and rownumber(nothing) mod 3=1,false,true)

    Use the expression below to set the visibility of second table's row.
    =iif(fields!col1.Value=parameters!group.value and rownumber(nothing) mod 3=2,false,true)

    Use the expression below to set the visibility of third table's row.
    =iif(fields!col1.Value=parameters!group.value and rownumber(nothing) mod 3=0,false,true)

    Create a parameter "group" and then set the visibility of it to hidden.

    The structure of the subreport looks like below.

    Regards,

    Charlie Liao
    TechNet Community Support