IIF (
[HAP5All_Board].CurrentMember.Properties("Dept") = "PH",
(
[BoardIncAcct2].&[61510000] -- General Services
+
[BoardIncAcct2].&[61520000] -- Accounting & Financem Services
+
[BoardIncAcct2].&[61530000] -- Information Technology Services
+
[BoardIncAcct2].&[61540000] -- Purchaseing Services
+
[BoardIncAcct2].&[61550000] -- Executive Services
+
[BoardIncAcct2].&[61560000] -- Human Resources Services
+
[BoardIncAcct2].&[61570000] -- Public Affairs Services
+
[BoardIncAcct2].&[61580000] -- Training Services
)
,
0
) -- IIF Section to filter in only PH
This is in the definition of a calculated member. It works fine for all members except for the "ALL level" on the dimension (Parent-Child dimension). Because the "All Level" is not actually a member/record in the table but defined on the dimension, it does not have Properties for that particular "member", so AS errors out when trying to focus on top ALL Level. What should we do to use such definition on ALL Level?
Maybe you could check for the [All] member first, like:
IIF (
[HAP5All_Board].CurrentMember is [HAP5All_Board].[All],
0,
IIF (
[HAP5All_Board].CurrentMember.Properties("Dept") = "PH",
(
[BoardIncAcct2].&[61510000] -- General Services
+
[BoardIncAcct2].&[61520000] -- Accounting & Financem Services
+
[BoardIncAcct2].&[61530000] -- Information Technology Services
+
[BoardIncAcct2].&[61540000] -- Purchaseing Services
+
[BoardIncAcct2].&[61550000] -- Executive Services
+
[BoardIncAcct2].&[61560000] -- Human Resources Services
+
[BoardIncAcct2].&[61570000] -- Public Affairs Services
+
[BoardIncAcct2].&[61580000] -- Training Services
)
,
0
) -- IIF Section to filter in only PH
)
|||Thanks, Deepak!
That works syntactically. OLAP took it. However, I am wondering that ALL level is taking the aggregate sumation of all children, would that (IF ALL THEN 0) rules out children that actually is PH in Properties. Or in other words, I need to have PH and all others roll up into ALL with PH children having those accounts incluced. Would (IFF ALL THEN 0) prevent PH children to be correctly rolled up? Am I making sense?
Thanks again!
Julius
|||Hi Julius,
Could you illustrate your exact scenario with some examples - I have only a vague idea of what you want, since 2 different dimensions seem to be involved?
No comments:
Post a Comment