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 |
|--------------------------------------------------
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.