Random number on a per row basis
So you've tried to get a random number on a per row basis however RAND isn't evaluated on a per row basis but on a row by row basis.
However you can try the following,
select abs(cast(newid() as binary(6)) %1000) + 1
The distribution is pretty good however there are the occasional peaks.
If you want to change the range of values just change the 1000 to the maximum value you want.
Use this as the source of a report server report and chart the results to see the distribution
SELECT randomNumber, Count(1) countOfRandomNumber
FROM (SELECT abs(cast(newid() as binary(6)) %1000) + 1 randomNumber
FROM sysobjects) sample
GROUP BY randomNumber
ORDER BY randomNumber