# Sorting data in dynamic columns of matrix in SSRS 2008 report

• ### 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

• 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 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 Thursday, October 7, 2021 12:00 AM
Wednesday, June 8, 2011 6:39 AM