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

View previous topic View next topic Go down

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

Post  Dania K on Tue Nov 13, 2012 12:06 pm

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.

Dania K

Posts : 2
Join date : 2012-11-13

View user profile

Back to top Go down

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

Post  Mike Honey on Thu Nov 15, 2012 9:50 pm

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

avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

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

Post  Dania K on Fri Nov 16, 2012 10:50 am

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.

Dania K

Posts : 2
Join date : 2012-11-13

View user profile

Back to top Go down

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

Post  Mike Honey on Tue Dec 11, 2012 11:07 pm

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
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum