Cannot get filter to work with multiple conditions

Question

I am trying to make an MDX request for use with SSRS later. To start with a basic query that works:

Select NON EMPTY { [Measures].[Score %] ,[Measures].[Month Key] } on Columns, NON EMPTY { ([Date].[YMD].[Month Name].&[201301]) * FILTER([Customer].[Customer Full Name].[Customer Full Name].members, ([Measures].[Score %], [Date].[YMD].&[201301]) <> null AND ([Measures].[Score %], [Date].[YMD].&[201301]) <> 0 ) } on Rows from [Cube] 

But this is only for one month. in SSRS, I want to be able to select several months, so I changed my request to this:

 Select NON EMPTY { [Measures].[Score %] ,[Measures].[Month Key] } on Columns, NON EMPTY { ([Date].[YMD].[Month Name].&[201301] : [Date].[YMD].[Month Name].&[201307]) * FILTER([Customer].[Customer Full Name].[Customer Full Name].members, ([Measures].[Score %], ([Date].[YMD].&[201301] : [Date].[YMD].&[201307]))<> null AND ([Measures].[Score %], ([Date].[YMD].&[201301] : [Date].[YMD].&[201307])) <> 0 ) } on Rows from [Cube] 

But here I get an error: Function <> expects a string or numeric expression for argument 1. The tuple set expression was used.

As far as I understand, this is because I am returning for several months in my set, where he expects one month. Therefore, I am writing the following query:

 With Set [QC relation] as FILTER([Customer].[Customer Full Name].[Customer Full Name].members, ([Measures].[Score %], [Date].[YMD].currentmember) <> null AND ([Measures].[Score %], [Date].[YMD].currentmember) <> 0 ) Select NON EMPTY { [Measures].[Score %] ,[Measures].[Month Key] } on Columns, NON EMPTY { ([Date].[YMD].[Month Name].&[201301] : [Date].[YMD].[Month Name].&[201307]) * [QC relation] } on Rows from [Cube] 

But here it seems that my filter is not working properly. It returns all rows with an account in the data for each month, so I have a lot of zero values.

How to get the right results for every month without zero values? I work with SSMS / SSAS on SQLServer2008

thanks

Answer 1

Repeating your request, I end up with this, let me know how this happens:

 SELECT NON EMPTY { [Measures].[Score %] ,[Measures].[Month Key] } ON COLUMNS, NON EMPTY { ([Date].[YMD].[Month Name].&[201301] : [Date].[YMD].[Month Name].&[201307]) * FILTER ( [Customer].[Customer Full Name].[Customer Full Name].members, SUM({[Date].[YMD].CURRENTMEMBER}, [Measures].[Score %])<> null AND SUM({[Date].[YMD].CURRENTMEMBER}, [Measures].[Score %])<> 0 ) } ON ROWS FROM [Cube] 

Or an even simpler version may work:

 SELECT NON EMPTY { [Measures].[Score %] ,[Measures].[Month Key] } ON COLUMNS, NON EMPTY { ([Date].[YMD].[Month Name].&[201301] : [Date].[YMD].[Month Name].&[201307]) * FILTER ( [Customer].[Customer Full Name].[Customer Full Name].members, [Measures].[Score %] <> null AND [Measures].[Score %] <> 0 ) } ON ROWS FROM [Cube] 

Edit: Third attempt, see if this does the trick:

 SELECT NON EMPTY { [Measures].[Score %] ,[Measures].[Month Key] } ON COLUMNS, NON EMPTY { ([Date].[YMD].[Month Name].&[201301] : [Date].[YMD].[Month Name].&[201307]) * [Customer].[Customer Full Name].[Customer Full Name].members } HAVING [Measures].[Score %] <> null AND [Measures].[Score %] <> 0 ON ROWS FROM [Cube]