November 11, 2008 at 7:28 am
Hi Guys,
If anyone knows how to create bell curves in SSRS 2005, please let me know. I'd IMMENSELY appreciate it.
November 12, 2008 at 12:32 pm
Hmmm.... I've been playing with this issue a little bit throughout the day. I haven't come up with a definitive answer, yet, however.
What I can share is that a bell curve is essentially a histogram (frequency distribution). So, at the very least your dataset needs to have categories (ranges along the x-axis) and the count of elements within each range (chart on the y-axis). Charting the data using a smooth line graph would get you close to a bell curve presentation.
If you're intention is to show a distribution as a reflection of standard deviations, however, then it would be fairly easy to compute a Zscore for each element using SQL
For example, if you wanted to know the distribution of test scores, you could calculate the Zscore of each testscore as follows:
zscore = (testscore - average test score)/standard deviation of all test scores
In sql, average test score is AVG(SCORE) and the standard deviation is STDEV(SCORE)
After each testscore has been computed a zscore, you could then chart the count of zscores.
My answer may not be all that helpful, but perhaps it brings up some ideas or steps to take?
--Pete
November 12, 2008 at 11:12 pm
Thanks Peter...I actually did this solution but I'm having troubling with y-val. The y-val should contain the normal distribution formula which I took from the NORMDIST function in Excel. And since SQL does not have this function, I'm trying to duplicate it. If you know how to resolve, I'd appreciate it. Thanks.
November 13, 2008 at 9:21 am
I just had an idea. If your goal is to plot the look of the data, couldn't the X-axis contain the individual elements (or ranges of elements) and the Y-axis reflect the count of those elements (or ranges)?
For example, below is a sql script that simulates rolling two dice 300 times, while also calculating the mean, standard dev, and the zscore for each roll of the dice:
DECLARE @TEMP TABLE(SCORE INT)
DECLARE @X INT
SET @X = 1
WHILE @X <=300
BEGIN
INSERT @TEMP(SCORE)
SELECT((ASCII(LEFT(NEWID(),1)) % 6) +1 ) + ((ASCII(LEFT(NEWID(),1)) % 6) +1 )
SELECT @X = @X + 1
END
SELECT
SCORE
,MEAN = B.MEAN
,STDV = B.STDV
,ZSCORE = CONVERT(DECIMAL(10,2),(SCORE - MEAN)/STDV)
FROM@TEMP A,
(
SELECT MEAN = AVG(CONVERT(DECIMAL(10,2),SCORE)), STDV = STDEV(SCORE) FROM @TEMP
) B
Given the result set, define a smooth line chart in SSRS, put the SCORE field on the x-axis (category field), and set the Y-axis to a count of the scores (data field).
By the way, I looked at the NORMDIST function in excel and it appears to be calculating a zscore which can be computed in excel as (i -avg(i))/stdev(i).
(As I am writing my response, my gut is telling me that you're probably looking to graph a bellcurve like the kind you'd find in a typical math book where the mean is labeled with a vertical line, and the various standard deviations are shaded and labeled as well....)
Just in case you're interested, attached is a text file (actually it's an rdl file with the extension changed from .rdl to .txt) that you can take a look at in SSRS to see how I'm implementing the dice rolling example.
--Peter
November 13, 2008 at 3:43 pm
Thanks Peter. I'll take a look at it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply