locked
Filtering RRS feed

  • Question

  • I have a set that I want to filter, the only trouble is when I filter it can only do one column at a time and I want to filter the whole set with the same condition.

     

    SELECT NON EMPTY { { { [Date].[Calender Date].[Month].&[Nov-2007].CHILDREN } * { [D Bucket].[Bucket].[All].CHILDREN } } } ON COLUMNS ,

    NON EMPTY { { { [DName].[Hierarchy].[PName].&[CTE].CHILDREN } * { [FTup].[FTup].[All].CHILDREN } * { [Measures].[Risk], [Measures].[SPNL], [Measures].[RLevel], [Measures].[ELevel] } } } ON ROWS

    FROM [PTCube]

    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE

     

     

    I would like to filter the whole set where the Measure SPNL is equal to zero. So far I have been unable to achieve this on all columns

     

    Filter where SPNL = 0

     

     

    Regards

     

    John

    Monday, December 17, 2007 11:10 AM

Answers

  • John,

     

    Do you have access to the ProClarity Selector Add-in for the Professional Client?  It can help you create filtered sets that are saved as Named Sets for using in your queries.  However, it does not support multi-dimensional sets such as your example: { [DName].[Hierarchy].[PName].&[CTE].CHILDREN } * { [FTup].[FTup].[All].CHILDREN } .  You would have to create a Named Set for each hierarchy.  The only way to do the whole thing at once would be to manually write the MDX.  ProClarity allows you to write your own MDX but it can cause unintended consequences if your users are going to do further analysis on the results.

     

    If you want to only return those rows where the SPNL measure is equal to zero, the MDX might look like this:

     

    SELECT NON EMPTY { { { [Date].[Calender Date].[Month].&[Nov-2007].CHILDREN } * { [D Bucket].[Bucket].[All].CHILDREN } } } ON COLUMNS ,

    NON EMPTY { FILTER({ [DName].[Hierarchy].[PName].&[CTE].CHILDREN } * { [FTup].[FTup].[All].CHILDREN }, [Measures].[SPNL]=0) * { [Measures].[Risk], [Measures].[SPNL], [Measures].[RLevel], [Measures].[ELevel] } } ON ROWS

    FROM [PTCube]

     

    Although now that I think of it, this may not take into account every combination of members on the columns.  Hopefully it will get you a bit further along though.

     

    Jason

    Monday, December 17, 2007 5:28 PM

All replies

  • John,

     

    Do you have access to the ProClarity Selector Add-in for the Professional Client?  It can help you create filtered sets that are saved as Named Sets for using in your queries.  However, it does not support multi-dimensional sets such as your example: { [DName].[Hierarchy].[PName].&[CTE].CHILDREN } * { [FTup].[FTup].[All].CHILDREN } .  You would have to create a Named Set for each hierarchy.  The only way to do the whole thing at once would be to manually write the MDX.  ProClarity allows you to write your own MDX but it can cause unintended consequences if your users are going to do further analysis on the results.

     

    If you want to only return those rows where the SPNL measure is equal to zero, the MDX might look like this:

     

    SELECT NON EMPTY { { { [Date].[Calender Date].[Month].&[Nov-2007].CHILDREN } * { [D Bucket].[Bucket].[All].CHILDREN } } } ON COLUMNS ,

    NON EMPTY { FILTER({ [DName].[Hierarchy].[PName].&[CTE].CHILDREN } * { [FTup].[FTup].[All].CHILDREN }, [Measures].[SPNL]=0) * { [Measures].[Risk], [Measures].[SPNL], [Measures].[RLevel], [Measures].[ELevel] } } ON ROWS

    FROM [PTCube]

     

    Although now that I think of it, this may not take into account every combination of members on the columns.  Hopefully it will get you a bit further along though.

     

    Jason

    Monday, December 17, 2007 5:28 PM
  • I tried this and it never worked :-(

     

    Does anyone have any links to web articles refering to MDX filtering queries?

     

    Thursday, December 27, 2007 10:27 AM
  • Hi John,

     

    Other than SQL Books online and MSFT's articles, I don't know of any solid links that would supply the information you're hoping for.  I suggest submitting an inquiry to the SQL Server Analysis Services Forum:

     

    http://forums.microsoft.com/TechNet/ShowForum.aspx?ForumID=83&SiteID=17

     

    I bet they'll be able to point you in the right direction.

     

    HTH.

     

    Amanda

     

    Friday, December 28, 2007 6:51 PM
  • Code Block

    SELECT NON EMPTY { { { [Date].[Calender Date].[Month].&[Nov-2007].CHILDREN } * { [D Bucket].[Bucket].[All].CHILDREN } } } ON COLUMNS ,

    NON EMPTY { { { [DName].[Hierarchy].[PName].&[CTE].CHILDREN } * { [FTup].[FTup].[All].CHILDREN } * { [Measures].[Risk], [Measures].[SPNL], [Measures].[RLevel], [Measures].[ELevel] } } } ON ROWS

    FROM [PTCube]

    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE

     

    "I would like to filter the whole set where the Measure SPNL is equal to zero." - but it's unclear which set you're referring to. Is it the set on columns, on rows, or .. ? Since SPNL could vary by  dimensions both on columns and on rows, it would be helpful if you could provide specific data examples.

     

    Monday, January 7, 2008 6:00 PM
  • i suppose on both i guess. where ever there is a cell where SPNL = 0.0 I want the values for that row and column to remain. So in the example below I would only want the green background showing in my report, the white background will remain blank. Hope this helps

     

    1M 2M 3M 4M 5M 6M 7M 8M  
    31/12/2007 00:00 31/12/2007 00:00 31/12/2007 00:00 31/12/2007 00:00 31/12/2007 00:00 31/12/2007 00:00 31/12/2007 00:00 31/12/2007 00:00  
    RENFORD BD SPNL 0 0 -0.978084295 -1.077412822 0 278.1119377    
    RENFORD   Risk -0.301969276 0.12750302 0.439578569 1.104370506 -244.5769356 -193.5795986  
    RENFORD   RLevel 26.9 28.6 28.6 34 40.2 44.6  
    RENFORD   ElLevel 0 0 30.82505 34.97559 41.76914 46.03668    
    RENFORD GP SPNL 0 0 -0.291449546          
    RENFORD   Risk -3.53797139 -1048.136126 -0.020619297  
    RENFORD   RLevel 47.1 49.86 54.48  
    RENFORD   ElLevel 0 0 39.94783          
    RENFORD MP SPNL 0 0 -19760.01552 0 -613.2699455 1152.444419 0 -3056.772537
    RENFORD   Risk -4.52146471 4.597092935 -1456.574114 -951.2851027 1687.496411 -580.2593638 -8933.837921 4446.635008
    RENFORD   RLevel 44.86 47.49 51.89 56.29 60.69 65.96 73 74.32
    RENFORD   ElLevel 0 0 38.32391 55.88384 61.05342 67.946085 74.83875 75.007435
    Tuesday, January 8, 2008 5:04 PM
  • can no-one help me with this I have searched High and Low on the web and can't find a solution

     

    Tuesday, January 15, 2008 12:34 PM
  • You might want to try posting in the Analysis Services forum.  I know a lot of experienced OLAP developers watch that forum and have significant MDX knowledge.  They may be able to give you a better answer since your question is more about MDX than ProClarity specific.  Here is the link:

     

    http://forums.microsoft.com/TechNet/ShowForum.aspx?ForumID=83&SiteID=17

     

    TJ Nelson

     

    Tuesday, January 15, 2008 7:01 PM
  • Did you ever find an answer?  I've filtered my MDX queries in the past with a simple where claus.  It's not quite the same as T-SQL but it does work. 

     

    Let me know and I'll try to dig up some of my old code and see how I actually did it.

     

     

     

    Wednesday, January 23, 2008 8:14 PM
  • No never did find a solution John still stuck, your help would be gratefully receieved

     

    Thursday, January 31, 2008 10:36 AM
  • Something like this has worked for me:

     

     

    SELECT NON EMPTY { { { [Date].[Calender Date].[Month].&[Nov-2007].CHILDREN } * { [D Bucket].[Bucket].[All].CHILDREN } } } ON COLUMNS ,

    NON EMPTY {  FILTER( [DName].[Hierarchy].[PName].&[CTE].CHILDREN  *  [FTup].[FTup].[All].CHILDREN * { [Measures].[Risk], [Measures].[RLevel], [Measures].[ELevel], measures.[SPNL]=0) } } } ON ROWS

    FROM [PTCube]

    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE

     

     

    You'll have to play around with it as I'm not sure about all the braces and parens.  I don't have exactly the same query but the FILTER has worked for me:

     

    SELECT NON EMPTY { [Measures].[Gross Profit], [Measures].[Coil Weight], [Measures].[Steel Margin], [Measures].[Gross Margin], [Measures].[Amount], [Measures].[Sales Rank], [Measures].[Ton Rank], measures.[GProfit Rank],

    [Measures].[SMargin Rank],[Measures].[GMargin Rank]} ON COLUMNS, NON EMPTY {

    (FILTER(([Customer].[Account Code].[Account Code].ALLMEMBERS * [Customer].[Name].[Name].ALLMEMBERS),measures.[GProfit Rank] > 0), lastperiods(@RollBackNum,STRTOMEMBER(@DateShippedYearMonth,CONSTRAINED)) ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM

    ( SELECT ( STRTOSET(@SalesTypeDescription, CONSTRAINED) ) ON COLUMNS FROM [Heidtman DW])

    WHERE

    ( IIF( STRTOSET(@SalesTypeDescription, CONSTRAINED).Count = 1, STRTOSET(@SalesTypeDescription, CONSTRAINED), [Sales Type].[Description].currentmember )) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

     

     

    I'm not a huge MDX expert.  I just let MS build the basics and play around with it until I get it to work.

     

     

    Monday, February 11, 2008 4:31 PM