I'm trying to make a MDX query for use with SSRS later on. To start with the 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 multiple months, so i altered my query 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: The <> function expects a string or numeric expression for the 1 argument. A tuple set expression was used.
As far as i understand it is because i'm returning multiple months in my set where it expects one month. So i write the next 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 my filter doesn't work properly. It is returning all the rows with a score in the data for each month so i have a lot of null values.
How do i get the proper results for each month without the null values? I'm working with SSMS/SSAS on SQLServer2008
Thanks
Reworking your query I end up with this, let me know how it goes:
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 might 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, let's 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]