June 30, 2009 at 9:01 am
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
June 30, 2009 at 10:53 am
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 🙂
June 30, 2009 at 5:59 pm
Yeah, you can use Except or - (minus).
MDX Essentials: Basic Set Functions: The EXCEPT() Function
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
July 2, 2009 at 4:52 am
Thanks very much to both of you. 🙂
September 1, 2011 at 8:11 am
Hi i have same problem with little different requirement, following is my query
select NON EMPTY{
[Measures].[Clicks],
[Measures].[YAP Delivered Impressions],
[Measures].[YAP Scheduled Impressions],
[Measures].[Total Measured Revenue Constant]} on columns,
NON Empty (FILTER(
[Order].[Order Number].[Order Number],
[Measures].[Clicks] > 0 and
[Measures].[YAP Delivered Impressions] >0 and
[Measures].[YAP Scheduled Impressions] > 0 and
[Measures].[Total Measured Revenue Constant] >0),
[Advertiser].[Advertiser Name].children,
[Sales Hierarchy].[Sales Region].[Sales Region],
[Sales Hierarchy].[Sales Rep Name].children,
[Order Item Type].[Order Item Type].[Order Item Type],
{Except([Order].[Campaign].[Campaign].members,
{[Order].[Campaign].&[Unknown],[Order].[Campaign].&[adx booking]})},
{Except([Sales Hierarchy].[Sales Rep Name].members,
{[Sales Hierarchy].[Sales Rep Name].&[Unknown]})}
)
on rows from YAPDM where {(
[Time].[Fiscal Month].&[201101],
[Ad Offering].[Ad Offering].&[Audience Display])}
Above query is returning expected result, i want to add more filter such as i do not want to select "unknown" from [Sales Hierarchy].[Sales Rep Name].&[unknown], if i am using the condition with another except then it is giving error like "The Sales Rep Name hierarchy is used more than once in the Crossjoin function" how can i solve it. please suggest.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply