SSRS List formatting

Question

Need to make a list in SSRS that has a numbered range from 0-30 in the rows and allows for null values to be entered in as a dash. i.e if I have 8 players who scored 10 points the list would show an 8 in the row value next to the row value 10 but for the other 29 numbers it would show a dash(-)?

Answer 1

You may have to make a couple of adjustments, but this should get you most of the way there. There wasn't a lot of information to go by to determine exactly what you want.

Adjust your dataset query in SSRS to the following, replacing the subquery Z with your current query that provides the points and player count. I inserted dummy data in there for now so I would have data for this example (8 = 1, 13 = 1, 17 = 2).

With X as 
(
    select top (30) n = ROW_NUMBER() OVER (ORDER BY m1.number)
    from master.dbo.spt_values as m1
    CROSS JOIN master.dbo.spt_values as m2
)
,Y as 
(
    Select Points, PlayerCount, ROW_NUMBER() OVER (Order by PlayerCount) RowNum
    from (
    --replace this with your query to return the data 
    --with your 2 columns for points and player count
        Select 8 as Points, 1 as PlayerCount
        UNION 
        Select 17 as Points, 2 as PlayerCount
        UNION 
        Select 13 as Points, 1 as PlayerCount) Z
)
Select x.n as Points,  /*isnull(Y.Points,0),*/ Isnull(Y.PlayerCount,0) PlayerCount
from X 
left join Y on X.n = Y.Points;

The CTE labeled X is what creates the 30 spots. If you want it to be 31 spots (0 - 30 inclusive), change the query in X to be

select top (31) n = ROW_NUMBER() OVER (ORDER BY m1.number)-1
from master.dbo.spt_values as m1
CROSS JOIN master.dbo.spt_values as m2

You end up with a data set with 2 columns: Points and PlayerCount. Now create your list in SSRS.

1. Insert a list. In that list, insert 2 columns to the right. Then delete the left most (original) column.
2. Set the expression for the left textbox in the list to the points field.
3. Set the expression for the right textbox in the list to the PlayerCount field.
4. Add a row outside the group above. Type in column headers for each column. I used Points and Player Count.
5. In the Group Properties for the Details row group, go to Sorting and set the Sort By column to points. The order should be A to Z.
6. Adjust the height of the rows to whatever looks best to you. I used .25 for the header and detail rows.
7. In the Tablix properties, check the box next to Keep together on one page if possible.
8. On the text box containing the Player Count field (bottom right), go to text box properties, Number category. Set it to 0 decimal places. Check the box next to Show zero as:. Make sure - is selected.

That gives you a list that looks like this:

If for some reason you want to see only the numbers that have > 0 players with that amount of points at the top and then the rest, you can do this with a calculated column.

1. Right click on your data set and add a calculated field. Set the Name to PointsCountSort. The the expression to =iif(Fields!PlayerCount.Value = 0, 2, 1) . Click OK.
2. In the Sort Order of the Details group. Change the sort order to go by PointsCountSort A to Z then Points A to Z.

That makes the list sorted like this: