Hi,

I've had this problem in OLAP for 3 weeks now, and I haven't found a
solution for it yet.

I have the following 5 Dimensions:
Line, Product, Store, Reporting Centre, and Legal Entity.

Every Product belongs to a Reporting Centre, where there could be many
Products that belong to the same Reporting Centre, but only one
Reporting Centre for each Product.

Every Store belongs to a Reporting Centre, where there could also be
many Stores that belong to the same Reporting Centre, but only one
Reporting Centre for each Store.

Now, each Reporting Centre has a specific tax rate, which is stored in
Line [9199]. But Products, Stores, and Legal Entity do not have a tax
rate, but have an Income amount, which is Line [2001].

If I'm in the cube looking at a specific Product (ie P1111), I have to
calculate the tax amount on the Income that P1111 has, but since
Products do NOT have a tax rate (Line 9199 is NULL for them), I would
like to use the tax rate that belongs to the Reporting Centre that
Product P1111 belongs to.

I have no clue how to code this in MDX, and any help or solution to
this problem will be greatly appreciated.

Thanks

Hichem

You didn't say what measures are in the cube, but assuming generic
measures exist, like [Amount] and [LineCount]:

'Avg(NonEmptyCrossJoin(
[Reporting Centre].[ReportingCentreID].Members,
{[Product].CurrentMember}, 1),
([Measures.[Amount], [Line].[9199])
/([Measures.[LineCount], [Line].[9199]))'
Member [Measures].[ProductTaxAmount] as
'[Measures].[ProductRate] *
([Measures].[Amount], [Line].[2011])'

Select {[Measures].[ProductTaxRate],
[Measures].[ProductTaxAmount]} on columns,
{[Product].[P1111]} on rows
from [YourCube]

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

If it were me, I'd simplify the question by denormalising the source data and
include the tax rate as a Product dimension attribute.

hth

"Hichem" wrote: