Want to retrieve total amount in whether slicing by Year, Qtr or M

#1

Thank you advance. This group has being a life saver.
I am trying to retrieve total for the whole year , whether I am slicing the
data by Year, Quarter or Month. but the following code does not work. I am
also trying to do the sime for the previous year. So if I get the total for
this year, I want the total for the previous year.

([Measures].[Revenue],ClosingPeriod ( [Time].[Year],
[Time].CurrentMember.Parent))
The problem is that I want to check in the calculated measure, that If I am
at the year level, only to return the current, but if I am at any other level
then use year level.

#2

This will get the full year amount for the current year:

Quote:

([Measures].[Revenue],Ancestor([Time].CurrentMember, [Time].[Year]))

And this would get the full year amount for the previous year:

Quote:

([Measures].[Revenue],Ancestor([Time].CurrentMember, [Time].

[Year]).Prevmember)

Quote:

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <3F65693A-7CE8-4173-9334-D50BD31C6A18 (AT) microsoft (DOT) com>,
Tomas (AT) discussions (DOT) microsoft.com says...

Quote:

Thank you advance. This group has being a life saver.
I am trying to retrieve total for the whole year , whether I am slicing the
data by Year, Quarter or Month. but the following code does not work. I am
also trying to do the sime for the previous year. So if I get the total for
this year, I want the total for the previous year.

([Measures].[Revenue],ClosingPeriod ( [Time].[Year],
[Time].CurrentMember.Parent))
The problem is that I want to check in the calculated measure, that If I am
at the year level, only to return the current, but if I am at any other level
then use year level.

#3

Thank you very very much. That did the trick
tomas

"Darren Gosbell" wrote:

Quote:

This will get the full year amount for the current year:

([Measures].[Revenue],Ancestor([Time].CurrentMember, [Time].[Year]))

And this would get the full year amount for the previous year:

([Measures].[Revenue],Ancestor([Time].CurrentMember, [Time].
[Year]).Prevmember)

--
Regards
Darren Gosbell [MCSD]
dgosbell_at_yahoo_dot_com
Blog: http://www.geekswithblogs.net/darrengosbell

In article <3F65693A-7CE8-4173-9334-D50BD31C6A18 (AT) microsoft (DOT) com>,
Tomas (AT) discussions (DOT) microsoft.com says...
Thank you advance. This group has being a life saver.
I am trying to retrieve total for the whole year , whether I am slicing the
data by Year, Quarter or Month. but the following code does not work. I am
also trying to do the sime for the previous year. So if I get the total for
this year, I want the total for the previous year.

([Measures].[Revenue],ClosingPeriod ( [Time].[Year],
[Time].CurrentMember.Parent))
The problem is that I want to check in the calculated measure, that If I am
at the year level, only to return the current, but if I am at any other level
then use year level.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Want to retrieve total amount in whether slicing by Year, Qtr or M

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

Want to retrieve total amount in whether slicing by Year, Qtr or M - 11-02-2005 , 02:40 PM

Re: Want to retrieve total amount in whether slicing by Year, Qtr or M - 11-02-2005 , 09:37 PM

Re: Want to retrieve total amount in whether slicing by Year, Qtr - 11-03-2005 , 10:16 AM