Getting the first period's data - OpeningPeriod function

Hello,

I have a Time hierarchy (FY - Period Num) as follows:

- Fiscal Year

- - Period Number

I am trying to get the number of just PeriodNumber 1 for each year for Actives. I have tried the following:

([Measures].[Active] , OpeningPeriod( [Time].[FY - Period Num].[Time].[FY - Period Num].currentmember ) )

That returns nothing.

([Measures].[Active] , OpeningPeriod( [Time].[FY - Period Num].[Period Number] , [Time].[FY - Period Num].currentmember ) )

That just returns the current period number.

I had also tried to do stuff like:

([Measures].[Active] , [Time].[Period Number] .currentmember)

But that again only gave me the current period data

([Measures].[Active] , [Time].[Period Number] .[&1])

Gives me a #VALUE! error

I also tried:

([Measures].[Active] , [Time].[Period Number] .[&1], [Time].[Fiscal Year] .currentmember)

I can do this:

([Measures].[Active] , [Time].[FY - Period Num].[Period Number].[&1]&[2006])

Here I get data, but it is only for 2006.

And on, and on. Obviously, I don't know what I am doing, but I think I am close.

How can I get the first period data from the current year?

Thank you.

-Gumbatman

Hello Again GumbatMan.

The answere to this question depends on if you have a time dimension that streches further away than the current year.

If you only have the current year as the last member on the year level you could write MDX like:

YourTimeDimension.(FY - Period Num).LastChild.FirstChild.

This will give you the first child member of the last year in your time dimension/hierarchy.

In the enterprise edition of SSAS2005 you have aggregation functions that will take care of this problem. Have a look at the properties for the measure in the relevant measure group.

Here is a link to another approach provided that you are looking for the current time member in reality:

http://blogs.conchango.com/christianwade/archive/2006/06/23/MDX-Script_3A00_-Current_2F00_Relative-Period.aspx

Think about if your problem is about aggregations(key wordemi additive measure) or the time member on the column or row axis in a client.

HTH

Thomas Ivarsson

|||

Thomas,

Once again you've been very, very helpful.

Thank you!