applying filter in MDX

  • 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.

  • 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])

    Raunak J

  • 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.

  • 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

    Raunak J

  • thanks, let me check out 🙂

  • Have you had a look at the FILTER function?

    ----------------------------------------------------

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply