SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


applying filter in MDX


applying filter in MDX

Author
Message
nish71
nish71
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 21
Hi,

i have one MDX query which i am using for reporting, i have around 30 different fields which have different filter condition, below is the example

with member [Measures].[COLPAS] as
'([Measures].[Appointments Count])'
member [Measures].[COLPA] as
'([Measures].[COL VAL 1],[Garage].[Allow Cross Selling].&[True])'
member [Measures].[COLPASC] as
'([Measures].[COL VAL 0],[Garage].[Allow Cross Selling].&[False])'
..............
select {[Measures].[COLPAS],[Measures].[COLPA],[Measures].[COLPASC],.......} on columns,
{([Garage].[Company Id].[Company Id].ALLMEMBERS * [Garage].[Garage Id].[Garage Id].ALLMEMBERS * [Garage].[Company Name].[Company Name].ALLMEMBERS ) } ON rows
FROM [CUBE]

so question is if i want to apply filter on full query i can put filter condition at the end of query with WHERE which will consider full query for filter but i want to put filter on member level and sample filter condition are follow in SQLformat.

AND (AL.CreatedOn >= @StartDate AND AL.CreatedOn <= @EndDate)
---------------------------------------------------------------

AND ((@Partner = 0) OR (@Partner = 1 AND A.LeasingCompanyID > 0) OR (@Partner = 2 AND (A.LeasingCompanyID IS NULL OR A.LeasingCompanyID = '-2')))

so how can i achieve this.
Raunak Jhawar
Raunak Jhawar
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3703 Visits: 1944
Nish, you should create a new meaure For example:

WITH MEMBER [Measures].[Test Measure] as ([Measures].[Reseller Sales Amount],[Geography].[Country].&[United States],[Reseller].[Business Type].&[Warehouse])

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
nish71
nish71
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 21
you are right Ranuk, thanks for your inputs, but adding "[Reseller].[Business Type].&[Warehouse]" type of condition on member level is easily accomplish but adding <>, <=,>=, AND, OR, CASE stement on that level is not achiving, you can put all this condition at the end of query which will apply to full data set but requirment is to apply conditions on specific member level.
Raunak Jhawar
Raunak Jhawar
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3703 Visits: 1944
Nish, if thats the case, why not use a Hybrid Query(Mix of MDX and SQL)

See this: http://www.sqlservergeeks.com/blogs/suhas/personal/545/sql-mdx-in-one-apartment-hybrid-query

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
nish71
nish71
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 21
thanks, let me check out :-)
MMartin1
MMartin1
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7205 Visits: 2033
Have you had a look at the FILTER function?

----------------------------------------------------
How to post forum questions to get the best help
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search