Pleaer reply or comment on above



Thanks


--
Posted via http://dbforums.com

please comment or reply on this.



Thanks


--
Posted via http://dbforums.com

This problem calls for the dynamic creation of members, depending on the
desired number of "bins" or ranges. The only MDX function that I know
of, which can do something like that, is CreatePropertySet(); but I
haven't been able to get this exact result. Anyway, here is a prior
post:

http://groups.google.com/groups?q=dp...hl=en&lr=&ie=U
TF-8&oe=UTF-8&selm=bf90cde3.0208281937.3875aa5%40posting.googl e.com&rnum
=2
'CreatePropertySet([Product],
[Product].[Brand Name].Members,
[Product].CurrentMember.Name)'

SELECT {[Unit Sales]} ON COLUMNS,
Order(BrandNames,[Product].CurrentMember.Name) ON ROWS

FROM Sales
Since [MS] has not documented this (??) in SQL BOL, refer to
a SQL Server Magazine article by Russ Whitney of Proclarity:

http://www.sqlmag.com/Articles/Index...rticleID=16302

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

I think that I now have an (inelegant) solution, using the
CreatePropertySet() function. Hopefully, someone can post a prettier
approach. But till then, here is an MDX query for the Foodmart Sales
cube. Measures.BinCount is a place-holder
for the desired number of "bins" or ranges (say, 3 here). The Unit Sales
by City are grouped into 3 dynamic ranges,
so the output in the MDX Sample App looks like:

-----------------------------------------------
2117:15271 | 4 | 18,048.00 |
15271:28425 | 7 | 171,888.00 |
28425:41580 | 2 | 76,837.00 |
-----------------------------------------------

Member Measures.BinCount as '3'
Set StoreCities as 'NonEmptyCrossJoin([Store].[Store City].Members)'
Set MinSales as 'Head(Order(StoreCities,[Measures].[Unit Sales],BASC))'
Set MaxSales as 'Head(Order(StoreCities,[Measures].[Unit Sales],BDESC))'
Member Measures.BinIndex as 'Int(iif([Store].CurrentMember is
MaxSales.Item(0),
Measures.BinCount-1,
(((Measures.[Unit Sales],Store.CurrentMember)-(Measures.[Unit
Sales],MinSales.Item(0)))
*Measures.BinCount)
/((Measures.[Unit Sales],MaxSales.Item(0))-(Measures.[Unit
Sales],MinSales.Item(0)))))'
Member Measures.BinLoVal as 'Int((((Measures.[Unit
Sales],MaxSales.Item(0))*Measures.BinIndex)
+((Measures.[Unit
Sales],MinSales.Item(0))*(Measures.BinCount-Measures.BinIndex)))
/Measures.BinCount)'
Member Measures.BinHiVal as 'Int((((Measures.[Unit
Sales],MaxSales.Item(0))*(Measures.BinIndex+1))
+((Measures.[Unit
Sales],MinSales.Item(0))*(Measures.BinCount-Measures.BinIndex-1)))
/Measures.BinCount)'
Set CitySales AS
'CreatePropertySet([Store],
StoreCities,
CStr(Measures.BinLoVal)+":"+CStr(Measures.BinHiVal ))'
Member [Measures].[City Count] as 'Generate({[Store].CurrentMember} as
S,
Filter(StoreCities,CStr(Measures.BinLoVal)+":"+CSt r(Measures.BinHiVal)
= S.Item(0).Item(0).Name)).Count'
Select {[Measures].[City Count],[Measures].[Unit Sales]} on Columns,
Order(CitySales,Int(Mid(Store.CurrentMember.Name,1 ,InStr(Store.CurrentMe
mber.Name,":")-1))) on Rows
from Sales

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!