I have a rather big function for a calculated member, but it seems, like I have made an error with the data types.
I get the error: #ERR: mondrian.olap.fun.MondrianEvaluationException: Expected value of type STRING; got value ‘12.0’ (NUMERIC)
My function is:
[Measures].[Issue OpenDuration] + [Measures].[Issue InAnalysisDuration]
+
CASE WHEN
[Transition Status].CurrentMember = “Open”
THEN
DateDiffWorkdays(Now(),[Measures].[Issue OpenDate])
ELSE
CASE WHEN
[Transition Status].CurrentMember = “In Analysis”
THEN
CASE WHEN
NonZero([Measures].[Issue InAnalysisDate])
THEN
DateDiffWorkdays(Now(),[Measures].[Issue InAnalysisDate])
ELSE
DateDiffWorkdays(Now(),NOW())
END
END
END
(If you have suggestions on how to optimize the function, do not hesitate telling me )
In the calculation formula, modify CASE WHEN conditions to compare status member name with a string, not the member itself; to retrieve member name property as a string, add function Name to the member:
[Transition Status].CurrentMember.Name = “Open”
Also, it is suggested to set needed formatting for the measure (decimal or integer, or another) manually, as it could be quite complex to detect the correct formatting automatically.
I am trying to do something similar and am getting the same error. I have two dimensions and when the current selection is “empty” for dimension B and the equivalent member for dimension A is not null I want to assume the member in B is the same as in A.
I tried doing this as a calcluated member in B but got the warning " Do not use the other dimension Application when defining a calculated member in Final Application . Create complex calculations in Measures ."
I tried a couple of ways of doing the calculated measure ::
sum(
case when [Final Application].CurrentMember is EMPTY
then [Application].CurrentMember
else [Final Application] .CurrentMember
END,
[Measures].[Issues resolved]
)
sum(
case when [Final Application].CurrentMember.Name="(none)"
then [Application].CurrentMember.Name
else [Final Application] .CurrentMember.Name
END,
[Measures].[Issues resolved]
)
And neither is any good. I am trying to get my head out of SQL mode and into MDX mode and you folks are superb at helping out so please can you point me in the right direction.
In general, we would recommend a slightly different way for the formulas with tuples using CASE WHEN conditions (when creating new user-defined calculated measures in “Measures” dimension)
And the dimension can’t be empty in the report. If you drag it to the report it has either some selected value (-s) or it has no value selected (that means the default member is selected).
CASE WHEN
[Final Application].CurrentMember is [Final Application].DefaultMember
THEN
Nonzero((
[Measures].[Issues resolved],
[Application].Currentmember,
[Final Application].DefaultMember
))
ELSE
Nonzero((
[Measures].[Issues resolved],
[Final Application].DefaultMember,
[Application].CurrentMember
))
END
Note, that you can actually skip using the [Dimension].Currentmember, as eazyBI would take the displayed member as current member automatically.
)