Hello,

I have a problem
whith a calculated
member.
The formula of this
member should be :

----
sum({crossjoin({[Activity].[01],[Activity].[04]},{Except({[Type
Materiel].[Type
Materiel].members},{[b]})})},
measures.[Impayes])
----

The problem is that
the value
activity.[04] does
not exist
and analysis
services does not
allow me to record
this request.
I tried using
iif(isempty) or
iif(iserror() but it
does not work.
I tried this too :
----
sum({crossjoin(except({Activite.[Activite].members},{[activite].[03],
[activity].[03],[activity].[06],[activity].[07],[activity].[11],
[activity].[14],[activity].[13],[activity].[28],[activity].[21],
[activity].[24],[activity].[23],[activity].[25],[activity].[31],
[activity].[34],[activity].[33]}),{Except({[Type
Materiel].[Type
Materiel].members},{[b]})})},
measures.[Impayes])
----

But no success, AS
tell it cannot
because
activity.[04] does
not exist.

Thanks for help.
Thierry

Hi Thierry,

Here's a simple example for the Foodmart Sales cube -a "Professional
Degree" member doesn't exist, but the calculated member still works. The
conditional set is first computed as a string because iif() doesn't
return a set:

'Sum(StrToSet("{[Education Level].[Graduate Degree]"
+ iif(IsError(StrToValue("([Education Level].[Professional Degree])")),
"",
", [Education Level].[Professional Degree]")
+ "}"))'

Select Measures.Members on columns,
AddCalculatedMembers([Education Level].[All Education Level].Children)
on rows
from Sales

There is a simpler solution in AS 2005 (Yukon):

http://www.mosha.com/msolap/ppt/Yukon_MDX_I.zip
Handling Missing Members

Problem:
Changing dimensions: members come/move/go
Saved queries and reports fail because of missing members

Solution:
Default Yukon behaviour returns NULL for missing members
Queries and reports continue to work
Consistent with SQL Server
Dimension Property = MDXMissingMemberMode
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hello,

Thanks for your help but it does not work with this syntax :

iif(IsError(StrToValue("([Activite].[04]")),
sum({crossjoin({[activite].[01]},{Except({[Type Materiel].[Type
Materiel].members},{[b]})})}, measures.[Impayes]),
sum({crossjoin({[activite].[01],[activite].[04]},{Except({[Type Materiel].[Type
Materiel].members},{[b]})})}, measures.[Impayes])
)

thierry

Sorry, )

here is the right syntax :

sum(
StrToSet("
{crossjoin(
{[Activite].[01]"+iif(IsError(
StrToValue("([Activite].[04])")
)
,""
,",[Activite].[04]"
)
+"},
{Except({[Type Materiel].[Type Materiel].members},{[b]})}
)
}"
),measures.[Impayes]
)


thanks a lot
thierry