Iam new to MDX queries & exploring possibilities of solving a problem
that iam currently facing.

Now assume that there is a time dimension with 3 levels
Year,Quarter,month respectively.

Now i have a set something like this
[2005].[Quarter 4].[October],[2005].[Quarter
4].[November],[2005].[Quarter 4].[December]

Now i want to get just [2005].[Quarter 4] using a MDX query.

This is to find out if all months are coming in a quarter or not. Then
i jst need that
[2005].[Quarter 4] string from the query.

This is just an example what i have given

Basically it should rollup till a level when all child members are
available at that level.

There is probably more than one way of doing this. The query below runs
against the Adventure Works sample database from AS2005, but the same
technique will work in AS 2000.

{[Measures].[Sales Amount]} ON COLUMNS,
DISTINCT(GENERATE({[Date].[Calendar].[Month].&[2003]&[12],[Date].
[Calendar].[Month].&[2003]&[11],[Date].[Calendar].[Month].&[2004]&[1]},
{Ancestor([Date].[Calendar].CurrentMember,[Date].[Calendar].[Calendar
Quarter])})) ON ROWS
FROM [Adventure Works]
What I am doing is using the GENERATE function to get the ancestor of
each month at the quarter level and then I extract the distinct set of
those ancestor member.

Hope this helps.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1144402069.388826.260070 (AT) t31g2000cwb (DOT) googlegroups.com>,
ypavankumar (AT) gmail (DOT) com says...

Hi Darren,


First of all Thanks, fr your reply.

Actually iam working on AS2000 server & iam not able to run your sample
query as i have foodmart2000 sample DB.
But iam able to figure out what exactly u r trying to do is to assume a
level up to which the member set should rollup to.

In my case i do not know to which level it should rollup.
Time dimension was just an example i gave to so my problem can be
understood easily.

The mdx query should rollup to a level with that level hierarchy
([parent1].[child1]) if all members below this level are available
For example assume at this level there are 1 sublevel with 2 more
members. Then the set would be
{[parent1].[child1].[child11],[parent1].[child1].[child12]}

With just the above data from a mdx query i should be able to get
somehow
'[parent1].[child1]' value without specifying any level names

Please let me know if iam clear about my problem


Pavan

I am not sure all the function available in MDX, but you could try the
following logic,

iif([dim].currentmember.children.count >
count(filter([dim].current.children,not empty([measure].[]), NULL,
[dim].currentmember)

"pavan" wrote:

The only way I could figure out to do this was if we could assume that
all the members of the set were on the one level. If that assumption is
broken I don't think the following code will work.

I created this query against the AS 2000, Foodmart 2000 sample database
so it should work on your system. I am basically saying that for each
member in the set, get all children of the immediate parent. Then I am
using the except function to check that all those children are in the
set, if they are I return the parent member otherwise I return the
current member.

You will see in the query below if you change the references from
testSet to testSet2 you will see that the expression will not roll up
the Wine members to their parent as one of the children is missing.

Some of these lines are quite long so you will have to watch out that
the line breaks when you run this code.

SET testSet as '{
[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].
[Wine].[Good]
,[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].
[Wine].[Pearl]
,[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].
[Wine].[Portsmouth]
,[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].
[Wine].[Top Measure]
,[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].
[Wine].[Walrus]
,[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].
[Beer].[Portsmouth]
}'

SET testSet2 as '{
[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].
[Wine].[Good]
,[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].
[Wine].[Pearl]
,[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].
[Wine].[Top Measure]
,[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].
[Wine].[Walrus]}'

SELECT
{[Measures].[Store Sales]} ON COLUMNS,

GENERATE(TestSet
,{StrToMember(
IIF(
EXCEPT(
{Product.CurrentMember.Parent.Children},TestSet).C ount=0
,Product.CurrentMember.Parent.UniqueName
,Product.CurrentMember.UniqueName
)
)}
) ON ROWS

FROM Sales
--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1144642111.472936.155250 (AT) i40g2000cwc (DOT) googlegroups.com>,
ypavankumar (AT) gmail (DOT) com says...

Hi Pavan and Darren,

This problem sounds similar to the "Tuning YTD-style calculations"
discussion in Chris Webb's blog:

http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!
107.entry

http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!
111.entry


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

I love that second link, I think the first one looks similar to my
earlier approaches, but the second one rocks.

Thanks for that Deepak.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <#$dJ73jXGHA.4652 (AT) TK2MSFTNGP04 (DOT) phx.gbl>,
deepak_puri (AT) progressive (DOT) com says...