Hello everyone,

I have the following MDX:

SELECT [Delivery Date].[10/2006] ON COLUMNS,
{[Measures].[Quantity]} ON ROWS
FROM [Inventory]

[Measures].[Quantity] measure has it's AggregateFunction set to
LastNonEmpty, so I get the last non empty quantity in the result set.
Now here is the fun part I can not figure out.

I have another dimension called [Item]. What this query is returning is
the last non-empty member over all Items, but what I really want is the
aggregated sum of the last non- empty member for each Item. Example:

Lets say I have two Item members with the following quantites:

40 quantities on 10/21/2006 for item A
100 quantities on 10/22/2006 for item A
240 quantities on 10/27/2006 for item B
300 quantities on 10/28/2006 for item B

The above query gives me 300 which is the last non-empty quantity. But
I really want to get back 400 (300 + 100). That is the sum of last
non-empty quantites for each item. There are over 30K items so I can
not hard code the Item members in the query.

How would you rewrite this query to do that?

Thanks in advance,
Kevin

*** Sent via Developersdex http://www.developersdex.com ***

Maybe something like:

Sum([Item].[Item].[Item].Members,
[Measures].[Quantity])


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thank you for responding.

Please explain what did you mean by by this syntax:
([Item].[Item].[Item].Members

The quantities I am trying to pull out are daily inventory levels. For
daily buckets query returns the right data. However, if the user selects
monthly time buckets then obviously the MDX will multiple-count as it
sums up inventory levels of all days in the month.

The problem I am facing is that the last inventory data is not
necessarily in the last day of the month (otherwirse I could have very
easily used the ClosingPeriod function and get back the inventory level
of the last day of the month). Additionally, the last available daily
inventory level is different for each item.

Therefore, the MDX needs to sum up for each month the last available
quantity of each item.

I tried this calculated measure formula:
member [Measures].[Quantity] as'
sum([Item].CurrentMember.children,[Measures].[Quantity On Hand])'

where [Item]..CurrentMember denotes the "All Items" member.

But the calculated quantities include all quantity members from all days
in the month and not just the LastNonEmpty per item, and therefore it
multiple counts.

Any pointers and help is appreciated.

..


*** Sent via Developersdex http://www.developersdex.com ***

Since I'm not clear about your scenario, here's an example from
Adventure Works, using the [End of Day Rate] measure with LastNonEmpty
aggregation:

{[Destination Currency].[Destination Currency].&[Argentine Peso],
[Destination Currency].[Destination Currency].&[Deutsche Mark],
[Destination Currency].[Destination Currency].&[EURO],
[Destination Currency].[Destination Currency].&[French Franc]} on 1
from [Adventure Works]
where [Source Currency].[Source Currency].&[US Dollar]
------------------------------------------------------
End of Day Rate
Argentine Peso .27
Deutsche Mark .47
EURO .97
French Franc .14

These are the last exchange rates for each of 4 currencies - but, if you
browse the cube, there is no data for the DM and FF after 2001. If you
aggregate these 4, values of all 4 on the last date with any data will
be summed (as you also observed for your scenario). So there is no
contribution from the DM (.47) and FF (.14):

Aggregate({[Destination Currency].[Destination Currency].&[Argentine
Peso],
[Destination Currency].[Destination Currency].&[Deutsche Mark],
[Destination Currency].[Destination Currency].&[EURO],
[Destination Currency].[Destination Currency].&[French Franc]})
select {[Measures].[End of Day Rate]} on 0,
[Destination Currency].[Destination Currency].[Agg4] on 1
from [Adventure Works]
where [Source Currency].[Source Currency].&[US Dollar]
----------------------------------------------------------
End of Day Rate
Agg4 1.24

But if instead you sum these 4 currencies, then the last exchange rate
for each is summed, regardless of the date on which that occurs. So the
contribution from the DM (.47) and FF (.14) is now included:

Sum({[Destination Currency].[Destination Currency].&[Argentine Peso],
[Destination Currency].[Destination Currency].&[Deutsche Mark],
[Destination Currency].[Destination Currency].&[EURO],
[Destination Currency].[Destination Currency].&[French Franc]})
select {[Measures].[End of Day Rate]} on 0,
[Destination Currency].[Destination Currency].[Sum4] on 1
from [Adventure Works]
where [Source Currency].[Source Currency].&[US Dollar]
----------------------------------------------------------
End of Day Rate
Sum4 1.85

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***