August 8, 2012 at 6:41 pm
I have a calculated member in a cube its performing extremely slowly. I am pretty sure its something to with the comparison in the IIF statement (see highlighted). Is there a better way I can compare. Earlier I was using something like the below and the performance was good just that when users didn't use the hierarchy the IIF didn't work
Previous working one
[GL Hierarchy].[GL Hierarchy].CurrentMember.Parent.Name = 'Retained Earnings'
---------------------------------------------------------------
Problem statement
IIF(
(
[GL Hierarchy].[GL Acct Code].CurrentMember = [GL Hierarchy].[GL Acct Code].&[320110]
OR
[GL Hierarchy].[GL Acct Code].CurrentMember = [GL Hierarchy].[GL Acct Code].&[320100]
OR
[GL Hierarchy].[GL Acct Code].CurrentMember = [GL Hierarchy].[GL Acct Code].&[320200]
)
,(
Descendants
(
Ancestor
(
[Calendar].[Calendar].CurrentMember
,[Calendar].[Calendar].[Year]
)
,[Calendar].[Calendar].[Month]
,SELF
).Item(0)
,[Measures].[Actual Amount]
)
,SUM(PERIODSTODATE([Calendar].[Calendar].[(All)],[Calendar].[Calendar].CurrentMember), [Measures].[Actual Amount])
)
Appreciate if someone can help me out here. Thanks....:-)
August 8, 2012 at 7:16 pm
I changed the IIF comparison to use the name it improved the performance a bit still very slow
New IIF
IIF(
(
[GL Hierarchy].[GL Acct Code].CurrentMember.Name = '320110'
OR
[GL Hierarchy].[GL Acct Code].CurrentMember.Name = '320100'
OR
[GL Hierarchy].[GL Acct Code].CurrentMember.Name = '320200'
)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply