Friday, February 24, 2012

Financial Periods YTD & Date Ranges

I am developing a set of reports for a site based on AS2000.

The reports will have columns for each finance period in the current year from period 01 to the current reporting period from the Primary Time dimension.

i.e. If period 4 is the default period, The report should show columns for periods 1,2,3,4 with a total that sums the four columns.

When the default period 5 the columns should be 1,2,3,4,5.

I cannot use the current year and explode that to show all periods in the year, as there are postings in future periods beyond the current accounting period that should not be displayed on the report.

I am setting the default member of the time dimension to the current reporting period. (2005/08 for example)
I am trying to use a named set based on an MDX expression to set the column range for the report.

The first test I tried was to set the MDX for the period range to some fixed values
{[Primary Time].[ACCSCAL].&[200501]:[Primary Time].[ACCSCAL].&[200508]}
This gave the expected result showing columns 01/2005 to 08/2005

I then tried changing the last period to use the default member of the dimension
{[Primary Time].[ACCSCAL].&[200501]:[Primary Time].[ACCSCAL].DefaultMember}
This again gave the expected result range and I could change the default member on the dimension and see the columns on the report update correctly.

I then tried testing that the first period can be found correctly
{[Primary Time].[ACCSCAL].&[200508].FirstSibling:[Primary Time].[ACCSCAL].&[200508]}
This again gave the expected result range from 01/2005 to 08/2005

I then tried combining the default member and first sibling calculations
{[Primary Time].[ACCSCAL].DefaultMember.FirstSibling:[Primary Time].[ACCSCAL].DefaultMember}
This gave a single column "All Primary Time", this was not what I was expecting.

So I tried changing the mdx to test the value of the first sibling of the default member, expecting 01/2005
{[Primary Time].[ACCSCAL].DefaultMember.FirstSibling}
This again gave a single column "All Primary Time".

So I then tried changing the mdx to test that a single period would work in the named set
{[Primary Time].[ACCSCAL].DefaultMember}
This again gave a single column "All Primary Time".

So I tried changing the mdx to test the value of the default member, expecting 08/2005
{[Primary Time].[ACCSCAL].&[200508]}
This again gave a single column for 08/2005 as expected

By now this has me totally confused!

Do you have any idea why the default member works when used as the second part of the range but does not work when used on its own or as the first element of the range?

Is this the correct way of achieving the report I am aiming for or is there a better method to use?

Any suggestions you have would be welcome.

Apologies is this is a FAQ.

What is the actual MDX expression for the Default Member of [Primary Time].[ACCSCAL]?|||The Default Member is [Primary Time].[ACCSCAL].&[200508]

No comments:

Post a Comment