locked
How to filter an MDX query with > or <> RRS feed

  • Question

  • I have the following MDX query:

     

    SELECT NON EMPTY {[Measures].[Amount], [Measures].[Gross Margin], [Measures].[Coil Weight] } ON COLUMNS, NON EMPTY { ([Customer].[Name].[Name].ALLMEMBERS * [Customer].[Account Code].[Account Code].ALLMEMBERS * [Date Shipped].[Month].[Month].ALLMEMBERS * [Date Shipped].[Year].[Year].ALLMEMBERS)}DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM

    (

    SELECT ( STRTOMEMBER(@FromDateShippedMonth, CONSTRAINED) : STRTOMEMBER(@ToDateShippedMonth, CONSTRAINED) ) ON COLUMNS FROM

    (

    SELECT (STRTOSET(@Division, CONSTRAINED) ) ON COLUMNS FROM

    (

    SELECT ( STRTOSET(@SalesTypeDescription, CONSTRAINED) ) ON COLUMNS FROM

    (

    SELECT ( STRTOSET(@ShipFromLocation, CONSTRAINED) ) ON COLUMNS FROM

    [Heidtman DW]))))

    WHERE

    (

    IIF

     

    ( STRTOSET(@SalesTypeDescription, CONSTRAINED).Count = 1, STRTOSET(@SalesTypeDescription, CONSTRAINED), [Sales Type].[Description].currentmember),

    IIF

     

    ( STRTOSET(@Division, CONSTRAINED).Count = 1, STRTOSET(@Division, CONSTRAINED), [Organization].[Division].currentmember ),

    IIF

     

    ( STRTOSET(@ShipFromLocation, CONSTRAINED).Count = 1, STRTOSET(@ShipFromLocation, CONSTRAINED), [Location].[Company].currentmember ) ,

    [Coil Status].[SMSTS Code].&[Hist-Ship])

     

    CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS


    The cube has some irrlevent data that returns with a Month of 0.  I can't remove that data from the cube right now.

    How would I filter the query to return Month>0 or Month <>0?

    I've used the EXCEPT before, as in:

    Except

     

    ([Date SHipped].[Month].[All].Children,[Date Shipped].[Month].[0])

    Is that the best way to go in this case?
    Thanks.
    Tuesday, June 23, 2009 1:33 PM

All replies

  • Except should work, if you wish to exclude [Date Shipped].[Month].[0] from the set of months (assuming the parameters are on the same Month heirarchy):

    ...
    SELECT ( Except({STRTOMEMBER(@FromDateShippedMonth, CONSTRAINED)
    :
    STRTOMEMBER(@ToDateShippedMonth, CONSTRAINED)},
    {[Date Shipped].[Month].[0]} )
    ON COLUMNS
    ...

    - Deepak
    Tuesday, June 23, 2009 8:14 PM
  • Not so much.  I still get the 0 month data using:

    (

    SELECT ( Except({STRTOMEMBER(@FromDateShippedMonth, CONSTRAINED)

    :

    STRTOMEMBER(@ToDateShippedMonth, CONSTRAINED)},

    {[Date Shipped].[Month].[0]} ))

    ON

     

    COLUMNS FROM

    Tuesday, June 23, 2009 8:38 PM
  • Then you can start at the basics - what month rows do you get for this simple query, and what are the parameter values?

    select
    {[Measures].[Amount], [Measures].[Gross Margin], [Measures].[Coil Weight] } ON COLUMNS
    Except({STRTOMEMBER(@FromDateShippedMonth, CONSTRAINED)
    : STRTOMEMBER(@ToDateShippedMonth, CONSTRAINED)},
    {[Date Shipped].[Month].[0]} ) on columns
    FROM [Heidtman DW]

    - Deepak
    Wednesday, June 24, 2009 3:38 AM
  • My cube contains spurious data which is noted by sales data not having a sales date.  At this point the users do not wish to purge the cube of bad data.  Most of my query's ruturn data by date.  I am getting data with a month of (0) and it is triggering error messages in my reports.  I was just looking for an easy way to filter data that had a month of 0.

    Most of my querys include filtering as in:

     

    SELECT NON EMPTY { [Measures].[Gross Margin], [Measures].[Gross Profit], [Measures].[Coil Weight], [Measures].[Steel Margin], [Measures].[Amount] } ON COLUMNS, NON EMPTY { ([Inside Rep].[Rep].[Rep].ALLMEMBERS * [Sales Release].[SOORR Num].[SOORR Num].ALLMEMBERS * [Sales Release].[SOORH Number].[SOORH Number].ALLMEMBERS * [Date Shipped].[Year].[Year].ALLMEMBERS * [Date Shipped].[Month].[Month].ALLMEMBERS, lastperiods(@RollBackNum,STRTOMEMBER(@DateShippedYearMonth,CONSTRAINED)) ) }



    The user probably can't go back far enough to get to day 0, which, in my date dim, is 01/01/1900.

    Other of my queries use the FromMonth:ToMonth filter and when I use "all" I get the month 0 data.  It's not an unsurmountable problem.  I was looking for a quick fix.

    Perhaps rather than spending a lot of time changing my queries I should just remove the spurious data.

    Wednesday, June 24, 2009 1:20 PM
  • Hi John,

     

    How about using Scope function, I mean hide the values belong to month0:

    Scope([Measures].Members);

    Freeze;

    Descendants([Date Shipped].[Month].[0])=NULL;

    End Scope;

     

    And then drop the null value from the query.

     

    Hope this helps.

    Raymond

    Thursday, June 25, 2009 3:05 AM
  • Raymond,

    Where do I insert the Scope in my query?  I've never used it before.

    Thanks.

    I added it to one of my queries:

    Scope

     

    ([Measures].Members);
    Freeze;
    Decendents([Date Shipped].[Month].[0])=
    NULL;
    End Scope;

    SELECT

     

    NON EMPTY { [Measures].[Steel Margin], [Measures].[Coil Weight], [Measures].[Amount], [Measures].[Gross Margin], [Measures].[Gross Profit] } ON COLUMNS, NON EMPTY { ([Date Shipped].[Month].[Month].ALLMEMBERS * [Date Shipped].[Year].[Year].ALLMEMBERS * [Customer].[Account Code].[Account Code].ALLMEMBERS * [Customer].[Name].[Name].ALLMEMBERS, lastperiods(@RollBackNum,STRTOMEMBER(@DateShippedYearMonth,CONSTRAINED)) ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
    FROM
    (
    SELECT (STRTOSET(@Division, CONSTRAINED) ) ON COLUMNS FROM
    ( SELECT ( STRTOSET(@SalesTypeDescription, CONSTRAINED) ) ON COLUMNS FROM [Heidtman DW]))
    WHERE
    (
    IIF( STRTOSET(@SalesTypeDescription, CONSTRAINED).Count = 1, STRTOSET(@SalesTypeDescription, CONSTRAINED), [Sales Type].[Description].currentmember ),
    IIF( STRTOSET(@Division, CONSTRAINED).Count = 1, STRTOSET(@Division, CONSTRAINED), [Organization].[Division].currentmember ) ,
    [Coil Status].[SMSTS Code].&[Hist-Ship]
    )
    CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS


    And I get an error: Failed to parse the query to detect if it is MDS or DMX.

    Thursday, June 25, 2009 12:02 PM
  • You could add a MDX script assignment with scope function for the cube. Click the Calculations tab, and then click the Script View button on the toolbar. More information, see:

    http://msdn.microsoft.com/en-us/library/ms145515.aspx

     

    Hope this helps.

    Raymond

     

    Friday, June 26, 2009 6:47 AM
  • I don't want to drop them for the entire cube, just for individual queries. 
    Thanks.
    Monday, June 29, 2009 5:48 PM