July 2003 MDX Puzzle

Use the FoodMart 2000 Sales cube to create an MDX query that finds the most recent nonzero sales amount for each customer in Los Angeles for each quarter of 1997. Format the quarters of 1997 on the columns and the individual customers in Los Angeles on the rows. In each cell, display the most recent monthly Store Sales value for that customer in that quarter. Here's a hint: This problem is very much like an inventory problem in which you want to aggregate the last known value for each time period. You need to create a measure that determines each time-dimension member's value by taking the value from the member's last non-empty child. Good luck! (You can read the solution to June's MDX puzzle on SQL Server Magazine's Web site at http://www.sqlmag.com, InstantDoc ID 39067.)