Monday, March 19, 2012

Multiple time dimensions and inventory measures.

I got 2 time-dimentions:

[Week]
Hierarchised dimension based on Year - Halfyear - Week - Day !

The supply calculated based on the Week-dimension
sum(
generate
(
ascendants([Week].currentmember),
iif(
[Week].currentmember IS
[Week].firstsibling,
{},
[Week].firstsibling:
[Week].prevmember
)
) + [Week].currentmember
,
[Measures].[Mutation]
)

[Posting Period]
Hierarchised dimension based on Year - Quarter - Month - Day !

The supply calculated based on the Posting Period-dimension
sum(
generate
(
ascendants([Posting Period].currentmember),
iif(
[Posting Period].currentmember IS
[Posting Period].firstsibling,
{},
[Posting Period].firstsibling:
[Posting Period].prevmember
)
) + [Posting Period].currentmember
,
[Measures].[Mutation]
)

My question:

iif(Is the Week-time-dimension used in the pivottable or cube-browse ?,
--Yes

sum(
generate
(
ascendants([Week].currentmember),
iif(
[Week].currentmember IS
[Week].firstsibling,
{},
[Week].firstsibling:
[Week].prevmember
)
) + [Week].currentmember
,
[Measures].[Mutation]
)
,
--No, the posting period time-dimension will be used probably
sum(
generate
(
ascendants([Posting Period].currentmember),
iif(
[Posting Period].currentmember IS
[Posting Period].firstsibling,
{},
[Posting Period].firstsibling:
[Posting Period].prevmember
)
) + [Posting Period].currentmember
,
[Measures].[Mutation]
)
)

Is there a way to merge the 2 time-dimensions without having duplicates in my measures ?

Kind regards,
GG

Here is an example from Adventure Works that shows how you can detect whether a user has selected members from the "Calendar" or "Fiscal" hierarchy. You should be able to adapt this to the problem you are trying to solve.

WITH

MEMBER Measures.[Time Hierarchy Used]

AS

CASE

WHEN [Date].Fiscal.CurrentMember.Level.Ordinal > 0 THEN

"Fiscal"

ELSE

"Calendar"

END

SELECT

{[Date].Calendar.DefaultMember.Children} ON ROWS,

--{[Date].Fiscal.DefaultMember.Children} ON ROWS,

{Measures.[Time Hierarchy Used]} ON COLUMNS

FROM

[Adventure Works]

HTH,

Steve

No comments:

Post a Comment