Using Filter on measures in MDX query on SSRS

Question

i've creted a report using a cube as datasource and MDX query on my dataset. i have a few measures in my dataset but i want to show wonly the rows with at least one of the measures > 0, using something like an OR filter (measure1 >0 or measure 2 >0 ..etc) how can i do this?

thanks

Fulvi

Answer 1

Use the Filter() clause to constrain the memberset for the rows:

SELECT
  [Measures].[Internet Sales Amount] ON COLUMNS,
  Filter(
    [Customer].[Country].Members, 
    ([Measures].[Internet Sales Amount] > 2000000) 
        AND ([Measures].[Internet Sales Amount] < 5000000)
  ) ON ROWS
FROM [Adventure Works];

Sanborne

thanks, i'll try that.. and is there a way to configure a measure filter from the Query Designer > Design Mode? – Fulvi

I don't use the Query Designer so I'm not sure, but a quick search turned up this suggestion: basarat.com/2009/08/ssas-with-ssrs-filtering-and-sorting.html – Sanborne