SSRS report - multi-dimensional cube as a backend – unable to filter measure with not equals to 0 condition

• vinaypugalia

  Old Hand

  Points: 335

  More actions

  April 11, 2014 at 1:05 am

  #290241

  Hello Everyone,

  I am working on SSRS report in which a multi-dimensional cube is being used as a backend.

  We have a requirement to consider the data where we would like to put a condition on a measure say "TS" does not equal to 0. (Measure.TS != 0).

  I tried to achieve it by adding a calculated measure i.e. "NonZero_TS" in a report query but the NON EMPTY condition does not work when the other members of the SET contains data.

  Hence, indirectly the row is being displayed on a report though the value of TS one of the member of a set equals “0”.

  Following is the query:

  WITH MEMBER [Measures].[PT] AS iif([Measures].[PT] = 0 , null ,[Measures].[GS]/[Measures].[TS])

  MEMBER [Measures].[NonZero_TS] AS iif([Measures].[TS] = 0,null,[Measures].[TS])

  SELECT

  NON EMPTY { [Measures].[NonZero_TS], [Measures].[GS], [Measures].[PT] } ON COLUMNS,

  NON EMPTY { ([BTC].[CBN].[CBN].ALLMEMBERS *

  [IFD].[Date].[Date].ALLMEMBERS ) }

  DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

  FROM ( [TESTCUBE])

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

  Any help would be much appreciated.

  -Vinay Pugalia
  If a post answers your question, please click "Mark As Answer" on that post.
  Web : Inkey Solutions
  Blog : My Blog
  Email : Vinay Pugalia