I thought you might find this interesting. Two rows contribute to the DRILLTHROUGH command that you see illustrated here.

I am surprised that it decided to summarize the two cash flows. It was important that these be separate because my assembly needs to recognize the negative amount (if there is one). If a negative cash flow and positive cash flow occurred on the same date for the same FUND (highly unlikely) than it would be obfuscated and raise an error. I find this behavior puzzling and wonder if it is by intent or overlooked when 2005 DRILLTHROUGHs were implemented to retrieve from MOLAP cube. According to this blog:

http://www.databasejournal.com/features/mssql/article.php/10894_3552186_2/MDX-Essentials-Drilling-Through-with-MDX-The-DRILLTHROUGH-Statement.htm

AS 2000 behaved the way one would expect. Any thoughts (makes no difference how I arrange the axes or remove DISTINCT function)?
Very curious,

Lance

SELECT CASHFLOW.[FundID]

      ,[CurrencyCode]

      ,[CashflowDate]

      ,[CashflowAmount]

FROM [Fund Performance OLAP].[CASHFLOW] CASHFLOW

INNER JOIN [Fund Performance OLAP].[FUND] FUND ON (FUND.FUNDID=CASHFLOW.FUNDID)

WHERE (CASHFLOW.FUNDID=248766 and  cashflowdate='2004-04-22 00:00:00.000' AND CURRENCYCODE='USD' )

FundID      CurrencyCode      CashflowDate                  CashflowAmount

248766      USD               2004-04-22 00:00:00.000             43330

248766      USD               2004-04-22 00:00:00.000             34730

DRILLTHROUGH  

SELECT

{ distinct( ([Cashflow Dates].[Cashflow Dates].&[2004-04-22T00:00:00], [Measures].[Cashflow Amount]))} on 0 ,

{ ([FUND].[FUNDID].&[248766])} on 1 --,

FROM [Private Equity Fund Performance]

[CASHFLOW].[$Currency.Currency]   [CASHFLOW].[$Cashflow Dates.Cashflow Dates]     [CASHFLOW].[$Fund.Fund]       [CASHFLOW].[Cashflow Amount]

USD                               04/22/2004                                      248766                                           78060