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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy