none
MDX Filter Expression

    问题

  • Hi All,

    MDX :I am using MDX filter expression with Postcodes .but getting wrong result.
    please help

    Filter Expression : [Account].[Postcode].&[21000] : [Account].[Postcode].&[21999]

    Result i am getting :

    21000

    21001

    21002

    21003

    2105

    2106

    2108

    21009

    21010.

    2105,2106,2108 is wrong result.

    result shoube be between 21000 & 21999

    Please help

    Sruthi

    2012年3月25日 20:10

答案

  • As Davor said, your best solution is to change it in the cube. The range operator (":") can only work with the predefined attribute hierarchies (as far as I know).

    One other way is to filter using CInt. e.g.

    Filter( [Account].[Postcode].Members,  VBA!CInt([Account].[Postcode].CurrentMember.Key) >= 21000 AND  VBA!CInt([Account].[Postcode].CurrentMember.Key) <= 21999)

    HTH


    Craig Bryden - Please mark correct and/or helpful answers

    2012年3月26日 22:58

全部回复

  • It looks to me like your account key is stored as a text value. Is that so?

    If so, that is why you are seeing this. You need to make sure that the key is numeric.


    Craig Bryden - Please mark correct and/or helpful answers

    2012年3月25日 22:06
  • yes, I think it is stored in text value, but how to convert it?

    SELECT
    NON EMPTY {
     [Measures].[Volume KPI], [Measures].[Revenue KPI] } ON COLUMNS,
     NON EMPTY
     { ([Account].[Postcode].[Postcode].ALLMEMBERS ) } ON ROWS FROM
     ( SELECT ( { [Account].[Reg Country].[Reporting Region Level 5].&[293] } ) ON COLUMNS FROM
    ( SELECT ( [Last 12 Months] ) ON COLUMNS FROM
     ( SELECT

    ({[Account].[Postcode].&[21000] : [Account].[Postcode].&[21999]
    }) ON COLUMNS FROM [Revenue])
    ))
    WHERE ( [Account].[Reg Country].[Reporting Region Level 5].&[293])

    Many Thanks

    Sruthi

    2012年3月26日 8:54
  • I believe VBA!Cint should work. (look for examples for VBA functions in MDX)

    However, I would change the datatype in the dimesion design because I don't thing that

    you really need string datatype for postcode at all.

    cheers


    DBA

    2012年3月26日 9:26
  • As Davor said, your best solution is to change it in the cube. The range operator (":") can only work with the predefined attribute hierarchies (as far as I know).

    One other way is to filter using CInt. e.g.

    Filter( [Account].[Postcode].Members,  VBA!CInt([Account].[Postcode].CurrentMember.Key) >= 21000 AND  VBA!CInt([Account].[Postcode].CurrentMember.Key) <= 21999)

    HTH


    Craig Bryden - Please mark correct and/or helpful answers

    2012年3月26日 22:58