locked
Sorting data in dynamic columns of matrix in SSRS 2008 report RRS feed

  • Question

  • User1426937062 posted

    Hi,

    I have an report with matrix displays the data for org->doctor->month->proc name and count. as below:

    Data is as below:

    Doctor Date Month Proc No
    Doctor3 2011-04 April Proc3 16
    Doctor3 2011-04 April Proc1 9
    Doctor3 2011-04 April Proc2 5
    Doctor5 2011-04 April Proc3 1
    Doctor5 2011-04 April Proc2 1
    Doctor3 2011-03 March Proc1 2
    Doctor3 2011-03 March Proc2 1
    Doctor3 2011-05 May Proc3 10
    Doctor3 2011-05 May Proc1 5
    Doctor3 2011-05 May Proc2 3
    Doctor4 2011-04 April Proc2 15
    Doctor4 2011-04 April Proc3 5
    Doctor4 2011-04 April Proc4 5
    Doctor4 2011-03 March Proc2 37
    Doctor4 2011-03 March Proc3 17
    Doctor4 2011-03 March Proc4 12
    Doctor4 2011-05 May Proc2 13
    Doctor4 2011-05 May Proc4 8
    Doctor4 2011-05 May Proc3 7
    Doctor1 2011-05 May Proc4 56
    Doctor1 2011-05 May Proc3 15
    Doctor1 2011-05 May Proc5 2
    Doctor2 2011-05 May Proc3 48
    Doctor2 2011-05 May Proc4 19

    Matrix current result:

    Organization Doctor Mar-2011 Apr-2011 May-2011
       
    Org1 Doctor1     Proc4 (56)
        Proc3 (15)
        Proc5 (2)
         
    Doctor2     Proc3 (48)
        Proc4 (19)
         
         
    Org2 Doctor3   Proc3 (16) Proc3 (10)
    Proc1 (1) Proc1 (9) Proc1 (5)
    Proc2 (2) Proc2 (5) Proc2 (3)
         
    Doctor4 Proc2 (37) Proc2 (15) Proc2 (13)
    Proc3 (12) Proc3 (5) Proc3 (7)
    Proc4 (17) Proc4 (5) Proc4 (8)
         
    Doctor5   Proc3 (1)  
      Proc2 (1)  
         

    In bold font the data sorting is wrong. I have Row group on : org -> doctor -> Proc And column group is on : month. Also if I removes the row group Proc it creates blank places for the procs for other months.

    Can anyone have idea to sort the problem. 

    Friday, May 27, 2011 3:24 AM

Answers

  • User1426937062 posted

    I got the solutions to resolve this problem.

    I have inserted a table in matrix cell. The table has only one cell and expression for the cell is set to "Proc Name" so it creates the three separate rows for the specific month. And the data is already sorted so it comes as required with three rows for each doctor.

    Thanks 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 8, 2011 6:39 AM

All replies

  • User174282173 posted

    With your grouping, it looks like the numbers will be displayed in the order that the Proc first appears in the database for a particular doctor. And keeping the Proc in the row grouping ensures that one row will show the results for only that one Proc. For example, Doctor1 is listed as Proc4 first followed by Proc3 and finally Proc5. That's also the order in the matrix. This explains why Doctor 3 is listed as Proc3 then Proc1 and finally Proc2 for all the months.

    To fix this try the following groupings:
    Row: Org -> Doctor
    Column: Month -> No (descending)

    Friday, May 27, 2011 7:35 AM
  • User1426937062 posted

    Thanks for the suggestion!

    I have tried the solution but not working. In this case you removed row group proc and sorting for column group, but after doing this the result is like this.

    Organization
    Doctor Mar-11 Apr-11 May-11
       
    Org1 Doctor1     Proc4 (56)
    Proc3 (15)
    Proc5 (2)
     
    Doctor2 Proc3 (48)
    Proc4 (19)
         
       
    Org2 Doctor3   Proc3 (16)  
    Proc3 (10)
    Proc1 (9)  
    Proc1 (5)
    Proc2 (5)  
    Proc2 (3)
    Proc2 (2)  
    Proc1 (1)  
         
    Doctor4 Proc2 (37)    
    Proc2 (15)  
    Proc4 (17)  
    Proc2 (13)
    Proc3 (12)  
    Proc4 (8)
    Proc3 (7)
    Proc3 (5)  
    Proc4 (5)  
         
    Doctor5 Proc3 (1)  
    Proc2 (1)  
         

    It sorts data but creates blank rows for each procedure. I want this all three procedures in three rows only. Please help.

     

    Wednesday, June 8, 2011 6:34 AM
  • User1426937062 posted

    I got the solutions to resolve this problem.

    I have inserted a table in matrix cell. The table has only one cell and expression for the cell is set to "Proc Name" so it creates the three separate rows for the specific month. And the data is already sorted so it comes as required with three rows for each doctor.

    Thanks 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 8, 2011 6:39 AM