Hello
I have an MDX statement that looks like this:
SELECT
{ Measures.members } on Columns ,
{ CrossJoin( [Time].[Calendar].[2004].[May]:[Time].
[Calendar].[2004].[July],[Business].[User].[Business
Entity Id].[212].Children ) } on Rows
FROM UsageStats
And it returns this via a matrix report:
2004
|--|--|--|--|
| May | June | July |Total |
--|--|--|--|--|
Alan Smith | 2 | 3 | 2 | 7 |
Amy Marcus | | 3 | 3 | 6 |
Bob Fields | | | | |
Carry Grant | 1 | | | 1 |
--|--|--|--|--|
TOTAL | 3 | 6 | 5 | 14 |
--|--|--|--|--|
The report above represents hits to a website on a per-
Monthly basis for each user.
I would like to supress the empty rows so that Bob Fields,
above, won't show up at all. I tried using
NonEmptyCrossJoin like this:
SELECT
{ Measures.members } on Columns ,
{ NonEmptyCrossJoin( [Time].[Calendar].[2004].[May]:
[Time].[Calendar].[2004].[July],[Business].[User].
[Business Entity Id].[212].Children ) } on Rows
FROM UsageStats
and just NON EMPTY like this:
SELECT
{ Measures.members } on Columns ,
{ NonEmptyCrossJoin( [Time].[Calendar].[2004].[May]:
[Time].[Calendar].[2004].[July],[Business].[User].
[Business Entity Id].[212].Children ) } on Rows
FROM UsageStats
Both methods above supress the empty rows, but they also
supress anyone who hasn't had any hits to the site in the
first month, even if they do have hits in the 2nd or 3rd
months (so both Bob Fields, and Amy Marcus are both left
out of the report above).
Does anyone know how to possibly get around this? Any help
at all will be greatly appreciated!!! :D
Best regards
JanoYou might be able to use FILTER in conjunction with the CoalesceEmpty to
meet your need:
Example of CoalesceEmpty against Foodmart 2000 Sales cube to set
Measures.[Store Sales] to 0 for any Quarter that is empty:
=====================================================================WITH
MEMBER [Measures].[NonEmpty Sales] AS
'CoalesceEmpty(Measures.[Store Sales], 0)'
SELECT
CROSSJOIN( {[Time].[1997].children}, { [Measures].[NonEmpty
Sales] } )
ON COLUMNS,
CROSSJOIN( { [Store].[Store City].members }, {[Product].[Product
Department].members} )
ON ROWS
FROM Sales
=====================================================================
Now we add FILTER statement to remove any stores that did not have sales in
all Quarters:
=====================================================================WITH
MEMBER [Measures].[NonEmpty Sales] AS
'CoalesceEmpty(Measures.[Store Sales], 0)'
SELECT
CROSSJOIN( {[Time].[1997].children}, { [Measures].[NonEmpty
Sales] } )
ON COLUMNS,
FILTER(
CROSSJOIN( { [Store].[Store City].members }, {[Product].[Product
Department].members} ),
([Store].CurrentMember,
[Product].CurrentMember, [Measures].[NonEmpty Sales]) > 0 )
ON ROWS
FROM Sales
=====================================================================--
-- John H. Miller [MSFT]
-- SQL Server BI Product Group
----
--
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
----
--
jhmiller@.online.microsoft.com
"Jano" <JanoBermudes@.microsoft.com> wrote in message
news:31d401c47eca$c1c7b200$a301280a@.phx.gbl...
> Hello
> I have an MDX statement that looks like this:
> SELECT
> { Measures.members } on Columns ,
> { CrossJoin( [Time].[Calendar].[2004].[May]:[Time].
> [Calendar].[2004].[July],[Business].[User].[Business
> Entity Id].[212].Children ) } on Rows
> FROM UsageStats
>
> And it returns this via a matrix report:
> 2004
> |--|--|--|--|
> | May | June | July |Total |
> --|--|--|--|--|
> Alan Smith | 2 | 3 | 2 | 7 |
> Amy Marcus | | 3 | 3 | 6 |
> Bob Fields | | | | |
> Carry Grant | 1 | | | 1 |
> --|--|--|--|--|
> TOTAL | 3 | 6 | 5 | 14 |
> --|--|--|--|--|
> The report above represents hits to a website on a per-
> Monthly basis for each user.
>
> I would like to supress the empty rows so that Bob Fields,
> above, won't show up at all. I tried using
> NonEmptyCrossJoin like this:
> SELECT
> { Measures.members } on Columns ,
> { NonEmptyCrossJoin( [Time].[Calendar].[2004].[May]:
> [Time].[Calendar].[2004].[July],[Business].[User].
> [Business Entity Id].[212].Children ) } on Rows
> FROM UsageStats
> and just NON EMPTY like this:
> SELECT
> { Measures.members } on Columns ,
> { NonEmptyCrossJoin( [Time].[Calendar].[2004].[May]:
> [Time].[Calendar].[2004].[July],[Business].[User].
> [Business Entity Id].[212].Children ) } on Rows
> FROM UsageStats
> Both methods above supress the empty rows, but they also
> supress anyone who hasn't had any hits to the site in the
> first month, even if they do have hits in the 2nd or 3rd
> months (so both Bob Fields, and Amy Marcus are both left
> out of the report above).
> Does anyone know how to possibly get around this? Any help
> at all will be greatly appreciated!!! :D
> Best regards
> Jano
>
