SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
Search:  
 
 
        
Home       Members    Calendar    Who's On



Equivalent of T-SQL 'NOT IN' for MDX Expand / Collapse
Author
Message
Posted Tuesday, June 30, 2009 9:01 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, November 03, 2009 5:39 AM
Points: 347, Visits: 264
Hello,

I have the following MDX query:

SELECT { [Year].[All Date].[2009] } *
{ [Date].[All].[2009].[Quarter 1].[February] ,
[Date].[All].[2009].[Quarter 1].[March] } *
{ [Dim Service Types].[Service Type].[Service Type Name].[Income Protection Comparison] }
ON 0,
{ ( [Measures].[Batch Count] ) , ([Measures].[Reporting Quote Count] ) } ON 1
FROM [Quotes]
WHERE ( [Dim Product].[Products].[Product Category].&[Protection],
[Dim User Type].[User Type Category].&[IFA & Controlled Distribution] )

It works fine and returns the expected results, but I also want to exclude certain users from the results. Those users (let's call them UserX and UserY) exist in the [Dim Users].[Users] hierarchy.
How can I exclude those users and does the exclusion need to appear in the slicer or possibly as a named set constructed using a filter?

Pointers in the right direction gratefully received!

Thanks
Lempster
Post #744495
Posted Tuesday, June 30, 2009 10:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 21, 2009 12:35 AM
Points: 6, Visits: 22
Hi
I think we can use EXCEPT function. Some thing like this
Except( [Dim Users].[Users].AllMembers, { [Dim Users].[Users].&[UserX], [Dim Users].[Users].&[UserY]})
I would prefer to use this function in columns.
All the best :)
Post #744661
Posted Tuesday, June 30, 2009 5:59 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 10:30 AM
Points: 273, Visits: 1,128
Yeah, you can use Except or - (minus).

Except (MDX)

- (Except) (MDX)

MDX Essentials: Basic Set Functions: The EXCEPT() Function


----------------------------------------------------------------------------------------
Dan English - http://denglishbi.spaces.live.com
Post #745000
Posted Thursday, July 02, 2009 4:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, November 03, 2009 5:39 AM
Points: 347, Visits: 264
Thanks very much to both of you.
Post #746134
« Prev Topic | Next Topic »


Permissions Expand / Collapse