Using Excel functions

#1

I've used the Excel percentile functions.

First, you must have the Excel library loaded on the
server.

Here is an example for the 25th quartile.

QUARTILE(SETTOARRAY(FILTER(DESCENDANTS
([DimensionName].currentmember,[DimensionName].
[DimensionLevel]),NOT ISEMPTY([Measure].&[1])),[Measure].&
[1]),1)

Quote:

-----Original Message-----
Hi,

We're hopeing to do some analyses using the Excel
Percentile function within
Analyses Services.

On a dataset of drug compounds. example below:

NMEId Type Route TimeTakenToBringToMarket

-------- ---- ------ ---------------------
--------

C1 NCE BM 106 days

C2 NCE BM 210 days

C3 NME IV 300 days

C4 NCE IV 100 days

C5 NME BM 50 days

In this example my cube dimensions would be Type & Route.
NMEId is a unique
index code.

I want to be able to use the Excel Percentiles function
server side to give
my cube the facility to produce the 5th, 25th, 50th,
75th, 95th Percentiles
by dimension.

The documentation says you can use Excel functions within
a calculated
member, but not how to set it up. With a help from this
newsgroup, I've got
as far as producing this in the Caculated Member Builder:

Percentile(SetToArray(descendants([NMEId].currentmember,
[NMEId].[NMEid]),[Me
asures].[TimeTakenToBringToMarket]), 0.95) using the
Excel function.

The problem is that, unless I use NMEId as my dimension,
it returns an #ERR
in each cell of the data view of the Cube Editor .

I've got the inbuilt Median function working fine with:

MEDIAN({Descendants([NMEId].Currentmember,[NMEId].NMEid)},
[TimeTakenToBringT
oMarket])

Any thoughts would be greatly appreciated.

Cheers

Andy

.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Using Excel functions

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

Using Excel functions - 07-15-2003 , 11:18 AM