Summing over time

[disgustipated]

Hi all,

Wondering if someone can throw me a quick tip re: Analysis Server.

I have a Dimension called {Periods].

Its heirarcy is:-

[Year]
--> [Month]
--> [Week]


What I want to do is sum a calculated measure for the entire year, regardless of what level the user is at, but I can't for the life of me seem to do this.

So no matter what level the user is at, I want the cube to show the total for the Year Dimension that is currently being drilled down on?

is it possible?

I tried doing a sum and using the descendants formula, but it still seemed to change regardless...

Any tips appreciated,

Regards,

Scott

 

[Shab]

Scott,

Have you tried: [Year].Currentmember: this will always return the current year.

Shab

 

[justins57]

Scott,

Look at the Ancestor function.

Ancestor([Periods].CurrentMember,[Periods].[Year])

Justin