MDX Filter Expression

全部回复

• 2012年3月25日 22:06

   

   
  

  0

  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月26日 8:54

   

   
  

  0

  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日 9:26

   

   
  

  0

  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日 22:58

   

   
  

  0

  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

  • 已标记为答案 Lola WangMicrosoft Contingent Staff, Moderator 2012年3月27日 6:17
  •  
  • 回复
  • 引用