Hi,

As we define a where clause in MDX, how could you define a not( Where
expresion )

Is it possible :

select
.... on columns,
.... on rows
from
...
where not ( Tuple specification )

?

If not, Any idea to do it differently ?

thks,

Reno

Hi Reno,

How generic does this need to be - is there a fixed set of dimensions in
the tuple?

For example, this query for Foodmart Sales cube assumes that the tuple
contains [Customers] and [Product]:

'{([Customers].[All Customers].[USA].[CA],
[Product].[All Products].[Drink])}'
Cell Calculation [ExceptTuple]
for '(Measures.Members)'
as 'Aggregate(Except([Customers].[State Province].Members
* [Product].[Product Family].Members, [CustProdTuple]),
CalculationPassValue(Measures.CurrentMember, -1, RELATIVE))'
select {[Measures].[Unit Sales]} on 0,
[Time].[Year].Members on 1
from Sales

Since AS 2005 now allows sets in the where clause, I initially thought
this could be expressed more elegantly; but I'm baffled by the error in
this Adventure Works query - maybe someone from [MS] could explain it?

{([Customer].[Customer Geography].[Country].&[Canada],
[Product].[Product Categories].&[1])}
select {[Measures].[Order Quantity]} on 0,
[Date].[Calendar Year].[Calendar Year].Members on 1
from [Adventure Works]
where Except([Customer].[Customer Geography].[Country].Members
* [Product].[Product Categories].[Category].Members,
[CustProdTuple])

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak,

Thank's a lot. It answers to my question.
So what you suggest is to recompute MesureMembers according to an exception
for a given subcube.
Could you give detail on :
'CalculationPassValue(Measures.CurrentMember, -1, RELATIVE))'

Renaud

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> a écrit dans le message de news:
OVKBgagKGHA.3896 (AT) TK2MSFTNGP15 (DOT) phx.gbl...

Renaud,

The 'CalculationPassValue(Measures.CurrentMember, -1, RELATIVE))'
construct is typically used to avoid a recursive definition - the
current pass value is derived from values in the previous pass of the
MDX calculation engine. In AS 2005, this is often unnecessary, as it
tries to detect recursive definitions.

http://msdn.microsoft.com/library/de.../en-us/olapdma
d/agmdxfunctions_06ud.asp

Returns the value of a Multidimensional Expressions (MDX) expression
evaluated over the specified calculation pass of a cube.
...
http://sqljunkies.com/WebLog/mosha/a...functions_as20
05.aspx
...
Bad/Deprecated functions
Some functions are supported by AS2005 for backward compatibility
purposes, but either deprecated (which means that they could be removed
in the future release) or simply bad to use. The worst offenders are
listed below

CalculationCurrentPass

With MDX Scripts and the procedural view of execution, the cube designer
doesn't need to worry anymore about passes - it is all taken care of
automatically by the system. CalculationCurrentPass therefore while
nominally still produces correct results, these results will be pretty
much useless.

CalculationPassValue

Same comment as above. In general, CalculationPassValue was most
commonly used in two following scenarios:

1) Go to the previous pass, i.e.
CalculationPassValue(Measures.CurrentMember, -1, RELATIVE)
There is no need in this because of automatic recursion resolution
performed by AS engine. For the practical example of such recursion
resolution, see this insigtful whitepaper by Richard Tkachuk.

2) Go to some hardcoded pass, usually to pass 0 where no calculations
exist, i.e. CalculationPassValue(Measures.CurrentMember, 0, ABSOLUTE)
This still can be done, but FREEZE statement makes it unnessesary. Also
FREEZE statement is more poweful then this.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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