Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

applying filter in MDX Expand / Collapse
Author
Message
Posted Tuesday, November 6, 2012 4:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 8, 2012 4:27 AM
Points: 4, 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.
Post #1381494
Posted Tuesday, November 6, 2012 10:44 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 24, 2014 3:10 AM
Points: 1,047, Visits: 1,852
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
Post #1381794
Posted Tuesday, November 6, 2012 11:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 8, 2012 4:27 AM
Points: 4, 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.
Post #1381795
Posted Tuesday, November 6, 2012 11:10 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 24, 2014 3:10 AM
Points: 1,047, Visits: 1,852
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
Post #1381796
Posted Tuesday, November 6, 2012 11:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 8, 2012 4:27 AM
Points: 4, Visits: 21
thanks, let me check out
Post #1381797
Posted Wednesday, November 7, 2012 12:41 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:59 PM
Points: 463, Visits: 1,074
Have you had a look at the FILTER function?
Post #1382136
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse