MDX Query Help...Please

Hi,

I have a fact table containing the following columns:

• CustomerID
• RegionID
• CategoryID
• ItemID
• Price

Relationships:

• A customer belongs to only one particular region and category
• Each customer can buy the same item at different prices
• The measures are LowPrice and AveragePrice

Query

• For a particular customer, what was the customer price, average price and low price for some other combination of region and category ( this not the region and category,the customer belongs to)

For ex:

CutomerId RegionID CategoryID ItemID Price

1 1 2 1 2.00

2 2 3 1 6.00

3 2 3 1 4.00

Report should display for Customer 1 , Region 2 and Category 3 as

ItemID Price LowPrice AveragePrice

1 2.00 4.00 5.00

Iam stuck with this query for a long time now...Any help on the query will be appreciated.

Also, how would find the above result for all combinations of (regions and categories)

Thanks,

Prash

Thanks Deepak for looking into the query.

Here we are looking at report for (customer 1, region 2, category 3, item 1).

As shown in the fact table there are two records for (item 1 ,region 2, category 3) combination and therefore Iam taking an average and minimum of the prices from those two records for Item 1.

CusomerID RegionID CategoryID ItemID Price

1                         1                2                1      2.00

2                         2               3                1        6.00

3                         2                3                1       4.00

So when customer 1 logs in and runs the report for (region 2,category3, item1), he should be able to see the following data

ItemID       Price    LowPrice       AveragePrice

1                 2.00         4.00                 5.00

The 'price'  is Customer's Price from the facttable, 'LowPrice' is  the lowestprice for Item1 in (region2, category3) and 'AvgPrice' is the averageprice for the Item1 in (region2, category3) combination.

As explained before:

Customers belong to one region and category. But the reports should be able to display the low price and average price for an item  for any combination of region and category.

Hope I have explained it properly. Please let me know, if you need any other details

Thanks,

Prash