Creating bell curve in SSRS

Question

Creating bell curve in SSRS

mharbuz

Say Hey Kid

Points: 678
More actions
August 5, 2019 at 3:25 pm

#3668854

Hi,

I have a report that shows how long we have spent on certain matters (I work for a solicitors). I round the amount of time spent on a matter to the nearest hour so we could have 5 matters where we spent 1 hour, 3 matters where we spent 2 hours, 1 matter where we spent 3 hours, etc.

I was wondering how I could display this data in a bell curve in SSRS. I know in Excell you have to work out the standard deviation and normal distribution, is the process in SSRS similar?

Any help on this would really be appreciated!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

The process is similar in SSRS. Take a look at the following article that walks through the process of a bell curve in SSRS. Most of what you are looking for you will see in the query used for the second dataset:

Plotting a Bell Curve in SQL Server Reporting Services

Sue

mharbuz Say Hey Kid Points: 678 More actions · Answer 2

Hi Sue,

Thanks for the reply but I'm struggleing to get a bell curve out of this, all I'm getting is a straight line.

Can you check the SQL I have used to see if I'm doing anything stupid please!

This is the first query:

select MAX(casetype) as casetype, MAX(mattersarchive.description) as description, TimeRecordedBalance / 3200 as Time, Count(*) as Amount
from mattersarchive inner join casetypes on mattersarchive.CaseType = CaseTypes.Description
where casetype not like 'domestic %' and mattersarchive.created > '2013-07-01' and mattersarchive.description LIKE '%' + @Description + '%'
group by TimeRecordedBalance
order by TimeRecordedBalance

I have included extra fields like casetype and description as I want to apply filtering to the graph. Time is recorded in seconds in the table which is why I have divided it by 3200 to get it in hours.

Here is the second query:

select MAX(mattersarchive.description) as description, MAX(casetype) as CaseType,
count(mattersarchive.description) as [Count],
min(TimeRecordedBalance / 3200)   as [Min],
max(TimeRecordedBalance / 3200)   as [Max],
round(avg(TimeRecordedBalance / 3200),2)   as [Mean],
round(stdev(TimeRecordedBalance / 3200),2) as [Standard Deviation],
(
 (select max(TimeRecordedBalance / 3200) from
   (select top 50 percent TimeRecordedBalance from mattersarchive order by TimeRecordedBalance) AS BottomHalf)
 +
 (select min(TimeRecordedBalance / 3200) from
   (select top 50 percent TimeRecordedBalance from mattersarchive order by TimeRecordedBalance desc) AS TopHalf)
) / 2 AS Median
from mattersarchive inner join casetypes on mattersarchive.CaseType = CaseTypes.Description
where casetype not like 'domestic %' and mattersarchive.created > '2013-07-01' and casetype LIKE @CaseType and mattersarchive.description LIKE '%' + @Description + '%'

I'm pretty much a complete novice when it comes to SQL to like I say, any pointers would be great 🙂