dbTalk Databases Forums  

MDX

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


Discuss MDX in the microsoft.public.sqlserver.olap forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
NYDBA
 
Posts: n/a

Default MDX - 04-05-2010 , 09:18 PM






On the following MDX, the Bill date 2009-10-28 does not exist so
[Measures].[91-120] returns null for all membersin the column. But
2009-10-29 does exist. But let's say I did not know that. How can I
write this so that the query finds and uses 2009-10-29? I tried
playing with HEAD and DESCENDANTS and some other functions but I can't
seem to figure out a way to do this. Is it possible?

With member [Measures].[91-120] as
sum([Dim Bill].[Bill Date].&[2009-10-28T00:00:00] : [Dim Bill].[Bill
Date].&[2009-11-26T00:00:00],
[Measures].[Balance])

select {[Balance],[Measures].[91-120]} on columns,
Filter(([Client Billing Employee].[Full Name by Last Name].members),
[Measures].[Balance]<>0)
on rows
from [AR]
where (Null:[Dim Time].[Date].&[2010-02-28T00:00:00])

Thanks in advance for any assistance.

Reply With Quote
  #2  
Old   
SQL Lion
 
Posts: n/a

Default Re: MDX - 08-05-2011 , 06:38 AM






MDX decendants function is nicely with more examples and diagrams on the below location.
http://www.sqllion.com/2011/08/mdx-decendants/


Quote:
On Monday, April 05, 2010 10:18 PM NYDBA wrote:

On the following MDX, the Bill date 2009-10-28 does not exist so
[Measures].[91-120] returns null for all membersin the column. But
2009-10-29 does exist. But let us say I did not know that. How can I
write this so that the query finds and uses 2009-10-29? I tried
playing with HEAD and DESCENDANTS and some other functions but I cannot
seem to figure out a way to do this. Is it possible?

With member [Measures].[91-120] as
sum([Dim Bill].[Bill Date].&[2009-10-28T00:00:00] : [Dim Bill].[Bill
Date].&[2009-11-26T00:00:00],
[Measures].[Balance])

select {[Balance],[Measures].[91-120]} on columns,
Filter(([Client Billing Employee].[Full Name by Last Name].members),
[Measures].[Balance]<>0)
on rows
from [AR]
where (Null:[Dim Time].[Date].&[2010-02-28T00:00:00])

Thanks in advance for any assistance.

Reply With Quote
Reply




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.