I have been trying to use the MEDIAN and QUARTILE excel functions in Analysis services, but they are not working as they only sum the numbers. Is there a patch for these? I am using SP3 for SQL2000 and Excel 2000.

The problem may not be the Excel functions themselves. I'm not sure what you
mean when you say that it only sums the numbers.

My guess is that the real issue is that you are expecting the MEDIAN and
QUARTILE functions to operate at the granularity of the fact table. In
other words, just as if you have a SUM, COUNT, MIN, MAX aggregates as
available aggregate types, you're expecting MEDIAN and QUARTILE Excel
functions to give you a Median aggregate. This is not possible since those
functions operate at the granularity of the cube, which is often not the
same as the granularity of the fact table.

For exmaple, suppose you have a fact table that tracks each individual sale
event at a cash register. You then decide to build a cube on top of this
data, but more than likely you won't include a transaction dimension. At
this point, the granularity of the cube is not the same as the granularity
of the fact table. If you then need to determine the median transaction
value, you will not be able to do so. The only way to be able to use a
function like Median is to insure that the the granularity of the cube
matches the granularity of the data that you need to compute that value.




--
Sean

--
Sean Boon
SQL Server BI Product Unit

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.



"Roger" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Is there a patch for these? I am using SP3 for SQL2000 and Excel 2000.