Having a dynamic set which is affected by the filter in SSAS 2005

hi.
what i need is to sum up all the amounts of the contracts which have an image in the month selected (from null till the month selected)
For example,
if one contract has 2 images in months 6 (with amount 300) and 7 ( with amount 100)
and another contract has 1 image in month 6 (with amount 250)
i want the result to be 400 (since i am taking into consideration only the contracts which have an image in the month selected)
so what i am doing is creating a set which returns me all the contracts which have an image in the month selected

with set testset
AS
nonempty([Contract].[Contract].CHILDREN,[Generated Date].[Year - Quarter - Month - Date].currentmember)

and then im joining it with null:currentmember to sum the amount of all previous images of the contract till the month selected
member testmeasure as
SUM
(
CROSSJOIN((NULL:
[Generated Date].[Year - Quarter - Month - Date].CURRENTMEMBER),testset),[Net Premium])

This query runs fine in Management studio and returns a correct result when setting the month 7 whereas in SSAS it is no evaluating it right , it gives the total of 6 and 7 as if the set is not affected by the date filter in the browser.

Hi Dania

I'm imagining you need to redesign this with a Many-to-Many relationship between Contract and Date.

You would build a bridge (table or view) with rows for each relationship (3 rows in your example), add that as a Measure Group (with a hidden count Measure) and then you can replace your Regular Relationship with a Many-to-Many relationship.

You can then remove your complex MDX and any query tool will give the correct answer (without ever double-counting the Amount measure).

Good luck!
Mike

Thank you Mike.

So let me recapitulate what you are trying to do.
You mean I should recreate the fact which i have now (containing the contracts and GeneratedDate) and have many to many relationships to the Contract and Generated Date dimensions though the first fact.
But this will still return me the incorrect result i used to have.

C1 6/2012 300
C1 7/2012 100
C2 6/2012 250
what i want is when i choose 7/2012 from the filter the measure should display 400 (since c2 has no images in 7/2012)and when i choose the whole year the measure should display 650.

Hi Dania,

I presume when you wrote "400" that was a typo and you meant 100?

If that's the case, it looks like a straight sum requirement over levels in the time hierarchy. I dont understand the complexity.

Mike