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