This works:
WITH
MEMBER [Measures].[CurrentDay] AS
AGGREGATE(
[Date].[Calendar].Currentmember,
[Measures].[Reseller Sales Amount]
)
MEMBER [Measures].[CurrentMonth] AS
AGGREGATE(
[Date].[Calendar].Currentmember.parent,
[Measures].[Reseller Sales Amount]
)
SELECT
NON EMPTY
{ [Measures].[CurrentDay],
[Measures].[CurrentMonth] }
ON COLUMNS,
NON EMPTY
{ [Date].[Calendar].[Date] }
HAVING [Measures].[CurrentDay]<>null //<<<<<<<<<<<<<<having line
ON ROWS
From [Adventure Works]
Returning the following from the version of Adventure Works that I have:
If I comment out the line HAVING [Measures].[CurrentDay]<>null
then this happens:
Is there another way of eliminating the rows that are null
for CurrentDay
without using HAVING
?
I've tried using EXISTING
without any success:
WITH
MEMBER [Measures].[CurrentDay] AS
AGGREGATE(
[Date].[Calendar].Currentmember,
[Measures].[Reseller Sales Amount]
)
MEMBER [Measures].[CurrentMonth] AS
AGGREGATE(
[Date].[Calendar].Currentmember.parent,
[Measures].[Reseller Sales Amount]
)
SELECT
NON EMPTY
{ [Measures].[CurrentDay],
[Measures].[CurrentMonth] }
ON COLUMNS,
NON EMPTY
{ EXISTING [Date].[Calendar].[Date] }
ON ROWS
From [Adventure Works]
EDIT
To run nsousa's solution in SSMS I need to nest the IIF
like this:
WITH
MEMBER [Measures].[CurrentDay] AS
AGGREGATE(
[Date].[Calendar].Currentmember,
[Measures].[Reseller Sales Amount]
)
MEMBER [Measures].[CurrentMonth] AS
IIF(
ISEMPTY([Measures].[CurrentDay]),
NULL,
AGGREGATE(
[Date].[Calendar].Currentmember.parent,
[Measures].[Reseller Sales Amount]
)
)
SELECT
NON EMPTY
{ [Measures].[CurrentDay],
[Measures].[CurrentMonth] }
ON COLUMNS,
NON EMPTY
{ [Date].[Calendar].[Date] }
ON ROWS
From [Adventure Works]
You can redefine your measure:
WITH
MEMBER [Measure].[Not Null Reseller Sales Amount] AS
IIF( IsEmpty( [Measures].[Reseller Sales Amount] ), 0, [Measures].[Reseller Sales Amount] )
MEMBER [Measures].[CurrentDay] AS
AGGREGATE(
[Date].[Calendar].Currentmember,
[Measures].[Not Null Reseller Sales Amount]
)
MEMBER [Measures].[CurrentMonth] AS
AGGREGATE(
[Date].[Calendar].Currentmember.parent,
[Measures].[Not Null Reseller Sales Amount]
)
SELECT
NON EMPTY
{ [Measures].[CurrentDay],
[Measures].[CurrentMonth] }
ON COLUMNS,
NON EMPTY
{ [Date].[Calendar].[Date] }
ON ROWS
From [Adventure Works]