November 20, 2006 at 2:32 pm
I'm not sure what you would call this or how you would accomplish this, I've been working on this for 2 days and decided to ask the experts.
for example I have 200,000 AccountNumbers and each AccountNumber has a $ value (AccountValue)
I want to add an additonal column in my MDX query that groups these by:
Above $5 million
$1 - 5 million
$500,000 - $999,999
$250,000 - $499,999
$100,000 - $249.000
Under $100,000
My MDX looks like this:
SELECT
{[Measures].[AccountValue]}
ON COLUMNS
,(nonempty([Account].[AccountNumber].[AccountNumber]))
on rows
from [MyCube]
where
([Date].[Month].&[2006-09]
)
I tried a custom member and I couldn't get it to work correctly. Any thoughts on how to rank or group this?
November 21, 2006 at 3:33 pm
Do you want to group them permanently (ie in the dimension)? If so, I don't suppose you're using AS2K5? If yes, then you can use the Descretization feature (new in AS2K5) to bucketize the groups, add this as a level to your user Hierarchy and then add the lower level (account) to the User Hierarchy.
Using AS2K, assuming you want the groups permanently, I'd prob go with a view in the DB to add a new grouping level to the dimension.
Steve.
November 22, 2006 at 7:35 am
I am using AS2K5 and will look into Descretization, it might be better then what I have now.
I did get something to work yesterday by using a custom member:
WITH
Member [Account].[Statement Parent Account].[Above $5 million] AS Aggregate(nonempty(filter([Account].[Statement Parent Account].[Statement Parent Account], [Measures].[AUM] > 5000000)))
I did this for each bucket...
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply