Hi

I currently have a requirement to show all sales for customers in 2006
by Customer by Product. Let me paint the picture:
Rows : Customer then Product
Columns: YTD Revenue, Last Year to date Revenue, Growth

The problem I have is that I am missing the rows where no sale exists
this year for a particular customer, but there was a sale last year.
So I dont see this customer in the rows. I need to be able to see the
customer in the rows showing 0 for YTD and whatever value for Last Yr
YTD.

Below is the mdx I have currently:
with
set [Hospitals] as 'descendants([Facility].[State].[All Facility],
[Facility], SELF_AND_BEFORE)'
set [Products] as 'descendants([Product].[Local].[All Product],
[Product].[Local].[Brand], SELF_AND_BEFORE)'
set [Surgeons] as 'descendants([Practitioner].currentmember,
[Practitioner].[Practitioner], SELF_AND_BEFORE)'
set [Components] as 'descendants([Product].[Component].[All Product],
[Product].[Component].[Component], SELF_AND_BEFORE)'

MEMBER [Date].[Calendar Year].[Periodagg] AS
'Aggregate({([Date].[Calendar
Year].[Day].&[20060228].parent.parent).firstchild :[Date].[Calendar
Year].[Day].&[20060228].parent})'

member [Measures].[YTD] as '([Measures].[Revenue], [Exchange].[Local])'

member [Measures].[YTD Last Yr] as '(ParallelPeriod([Date].[Calendar
Year].[Year], 1), [Measures].[Revenue], [Exchange].[Local])'
member [Measures].[% Grth YTD] as 'iif([Measures].[YTD Last Yr] = 0,
null, ([Measures].[YTD] - [Measures].[YTD Last Yr] ) /
[Measures].[YTD Last Yr])'

member [Measures].[YTDUnits] as '([Measures].[YTD Units])'
member [Measures].[YTD Last Yr Units] as
'(ParallelPeriod([Date].[Calendar Year].[Month], 12), [Measures].[YTD
Units])'
member [Measures].[% Grth YTD Units] as 'iif([Measures].[YTD Last Yr
Units] = 0, null, ([Measures].[YTD Units] - [Measures].[YTD Last Yr
units] ) / [Measures].[YTD Last Yr Units])'
SET [Report Measures] as
'tail(AddCalculatedMembers([Measures].Members),6)'
select
{[Report Measures] } on Columns,
{nonemptycrossjoin( order([Surgeons], [Measures].[YTD],
BDESC),[Hospitals],[Products],[Components])} on rows
//{Month.[All Months].&[1]: Month.[All Months].&[2]} ON AXIS(2)
from [Sales Cube]
where
([Customer Type].&[Ext], [Data Type].&[Billing], [Date].[Calendar
Year].[Periodagg], [SalesRep].&[16])

The problem is the nonemptycrossjoin(). Nonemptycrossjoin only looks at
fact table data to figure out what is empty and what is not so you can
get issues like this with it not recognising non empty calculations.

The easiest way to fix your issue would be to use:

NON EMPTY {crossjoin( order([Surgeons], [Measures].[YTD],
BDESC),[Hospitals],[Products],[Components])} on rows

....as your row definition, but this will be noticeably slower.


--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1142901371.260801.148800 (AT) u72g2000cwu (DOT) googlegroups.com>,
drmoey (AT) hotmail (DOT) com says...