Reporting, Visual Studio, layout level, median function

I'd like to calculate the median of a set of values, ie: 1,2,2,2,5 would have
a median of 2.
I've been able to get the average using
sum(fields!1.value)/count(fields!1.value) or Avg(fields!1.value).
How can the median be found using either a function or a math formula?
Thanks,
JimI have no clue if this would work, but it might be worth a try... add a new
text column ( and maybe hide it)...
It's value is
=IIF(rownumber(nothring)=Count(Fields!1,Value),Fields!Fieldname.Value,0)
So the value of this field will be 0 for all rows except the middle row,
which will have the value of the OTHER column with values ... Of course this
only would work if the OTHER column is sorted, and you'd have to account for
an even/odd number of rows...
But then you sum this column in your aggregate row, which would be the
median value...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jim" <Jim@.discussions.microsoft.com> wrote in message
news:440D1DAE-8C20-41D5-819D-C193254626F4@.microsoft.com...
> I'd like to calculate the median of a set of values, ie: 1,2,2,2,5 would
> have
> a median of 2.
> I've been able to get the average using
> sum(fields!1.value)/count(fields!1.value) or Avg(fields!1.value).
> How can the median be found using either a function or a math formula?
> Thanks,
> Jim|||Is your syntax correct? I cant get it to work..
=IIF(rownumber(nothring)=Count(Fields!1,Value),Fields!Fieldname.Value,0)
what does Fields!1 represent?
"Wayne Snyder" wrote:
> I have no clue if this would work, but it might be worth a try... add a new
> text column ( and maybe hide it)...
> It's value is
> =IIF(rownumber(nothring)=Count(Fields!1,Value),Fields!Fieldname.Value,0)
> So the value of this field will be 0 for all rows except the middle row,
> which will have the value of the OTHER column with values ... Of course this
> only would work if the OTHER column is sorted, and you'd have to account for
> an even/odd number of rows...
> But then you sum this column in your aggregate row, which would be the
> median value...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Jim" <Jim@.discussions.microsoft.com> wrote in message
> news:440D1DAE-8C20-41D5-819D-C193254626F4@.microsoft.com...
> > I'd like to calculate the median of a set of values, ie: 1,2,2,2,5 would
> > have
> > a median of 2.
> >
> > I've been able to get the average using
> > sum(fields!1.value)/count(fields!1.value) or Avg(fields!1.value).
> >
> > How can the median be found using either a function or a math formula?
> >
> > Thanks,
> > Jim
>
>