March 2, 2009 at 1:17 am
Hi,
If anyone can help me with the following problem, it would be greatly appreciated.
I have the following calculated members (Unfortunately this has to be a leaf level calculation for the last calculation as it is a non-commutative calculation):
CREATE MEMBER CURRENTCUBE.[MEASURES].[MF Opening Inventory]
AS ROUND(
iif([Measures].[IsFuture] > 0,
iif([Measures].[IsFuture] = 3,
-- MTD Return Actuals
IIF(ISEMPTY([Measures].[Fact NonEdit Inventory SOH]),0.00,[Measures].[Fact NonEdit Inventory SOH]),
-- Future
IIF([Measures].[IsFuture] = 2, IIF(ISEMPTY([Measures].[Fact NonEdit Inventory SOH]),0.00,[Measures].[Fact NonEdit Inventory SOH]),IIF(ISEMPTY([Measures].[Fact NonEdit Inventory SOH]),([Time].CURRENTMEMBER.LAG(1),[Measures].[MF Opening Inventory]),([Time].CURRENTMEMBER.LAG(1),[Measures].[Fact NonEdit Inventory SOH])) + ([Time].CURRENTMEMBER.LAG(1),[Measures].[MF Constrained P]) - ([Time].CURRENTMEMBER.LAG(1),[Measures].[MF Unconstrained S With Adjustments]))
),
-- History
IIF(ISEMPTY([Measures].[Fact NonEdit Inventory SOH]),0.00,[Measures].[Fact NonEdit Inventory SOH]))
,2),
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[MEASURES].[MF Opening Inventory At Leaf]
AS ROUND(
iif([Measures].[IsFuture] > 0,
iif([Measures].[IsFuture] = 3,
-- MTD Return Actuals
IIF(ISEMPTY([Measures].[Fact NonEdit Inventory SOH]),0.00,[Measures].[Fact NonEdit Inventory SOH]),
-- Future
SUM(
DESCENDANTS([Time].CURRENTMEMBER,[Time].[Month])
*
DESCENDANTS([MF Plan].[MF Plan].CURRENTMEMBER,,LEAVES)
*
DESCENDANTS([Inventory].[Product].CURRENTMEMBER,,LEAVES)
*
DESCENDANTS([Site].[Site].CURRENTMEMBER,,LEAVES)
,
[Measures].[MF Opening Inventory]
)
),
-- History
IIF(ISEMPTY([Measures].[Fact NonEdit Inventory SOH]),0.00,[Measures].[Fact NonEdit Inventory SOH]))
,2),
VISIBLE = 1 ;
Now the problem is that when Require this calculation to perform over all time, i.e. [Time].[All Time].
So for example if a fire the following MDX select statement:
SELECT
{[Time].[All Time]} ON COULMNS,
{[Measures].[MF Opening Inventory At Leaf]} ON ROWS
FROM [CUBE]
Does not give the same answer as:
SELECT
{[Time].[Month].Members} ON COULMNS,
{[Measures].[MF Opening Inventory At Leaf]} ON ROWS
FROM [CUBE]
Summing indivual month periods from the second select statement.
On further investigation I found that it had to do with the [Time].CurrentMember Statement. The problem is that when one uses the [Time].[All Time] Context it uses the aggregations stored for that member and not using the leaf / descendants at month level to complete the calculation. Since there is explicit use of this with the lag funcation that recursively calls this meber again to do the calculation I need to have some way of calling the lag of a time period using the descendants and cross join function used in the calculated meber.
Thanks in advance again.
March 9, 2009 at 12:01 pm
Not sure if this will add significantly to your approach, but why not check the status of the Time dim in the beginning of the calc member and follow a different path if it proves to be at the 'All' level?
Steve.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply