Put Null results of SSRS column group to the last

Question

I am struggling with a challenge in SSRS and your help would be appreciated. I have a dataset of hours and minutes in two separate columns as shown below.

---------------------------------
|A_Service_Hours| Service_Minute|
|   5           |   52          |
|   5           |   54          |
|   5           |   56          |
|   6           |   20          |
|   6           |   22          |
|   6           |   27          |
|   6           |   29          |
|   6           |   46          |
|   6           |   51          |
|   6           |   58          |
|-------------------------------|

I want to group all hours in the SSRS row group and minutes in the column group. and I want the result to be sorted like the below table. how can I achieve this in SSRS?

---------------------------------------------------
|   5           |52 | 54 | 56 |    |    |    |    |
|   6           |20 | 22 | 27 | 29 | 46 | 51 | 58 |
|--------------------------------------------------

Answer 1

If you add a row number to each record you can do this easily.

Make your dataset query something like

SELECT 
    A_Service_Hours, Service_Minute
    , ROW_NUMBER() OVER(PARTITION BY A_Service_Hours ORDER BY Service_Minute) AS RowN
FROM myTable

This will give you the following results

----------------------------------------
|A_Service_Hours| Service_Minute|  RowN|
|   5           |   52          |     1|
|   5           |   54          |     2|
|   5           |   56          |     3|
|   6           |   20          |     1|
|   6           |   22          |     2|
|   6           |   27          |     3|
|   6           |   29          |     4|
|   6           |   46          |     5|
|   6           |   51          |     6|
|   6           |   58          |     7|
|--------------------------------------|

You can now add a matrix control to your report, use A_Service_Hours as the RowGroup and RowN as the ColumnGroup.

This will give you exactly what you want.

This is done from memory so if it does not work, add a comment and I'll edit the answer when I get time.

Answer 2

I could come up with 2 slightly different representation of your req. If this works for you, let me know I will update my answer with how to achieve it. Below Screenshot for help. Left had is original data and other two are represented results