Hi all

Here is something that I think may be a bit of a challenge...

The topic of multiple hierarchies esp of a time dimension often crops
up in this group.

What I want to do is to be able to present my data against the
accounting calendars of my customers. For example, my year starts in
June whereas a particular customer's starts in April

Thus, for internal reporting, I want Aug YTD to be Jun+Jul+Aug but when
conducting the business review with this customer, Aug YTD becomes
Apr+May+Jun+Jul+Aug

.... and across all customers there are probably 12 different
calendars!!

I use a time analysis dimension to generate YTD against my calendar
YTD = aggregate(periodstodate(time.year,time.currentmemb er),[time
analysis].[current])

To follow this method, I would have to provide 12 alternate hierarchies
and 12 alternate YTD calculations - very messy!

An extra dimension (with 12 members) to generate these YTDs might be
the way to go. But it still looks like the MDX would be tricky

Any other ideas would be welcome

Regards
Ian Bamforth

How about using Member Property?

IIF
(
VAL(Customer.CurrentMember.Properties("Start")) >
Time.CurrentMember.Properties("Key"),
Sum({Time.CurrentMember : StrToMember(Ancestor(Time.CurrentMember,
Year).PrevMember.UniqueName + ".[" +
Customer.CurrentMember.Properties("Start") +"]")}),
Sum({StrToMember(Ancestor(Time.CurrentMember, Year).UniqueName + ".[" +
Customer.CurrentMember.Properties("Start") +"]") : Time.CurrentMember}
)

I have no test cube. My sample is not complete nor tested. You may have to
customize it depending on the dimensional data and time hierarchy. But I
hope it could help you think out better idea.

Ohjoo Kwon


"bammers99" <ian.bamforth (AT) gmail (DOT) com> wrote

In Analysis Services 2005, create an attribute that defines the reporting
period. Scope on each one and define your calculations accordingly.

Then define a role for each group and set the role default member to the
right value for each member of the role.

Cheers,
Richard

--
_______________
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.

"Ohjoo Kwon" <ojkwon (AT) olap (DOT) co.kr> wrote