Search code examples
olapmdxpentaho

MDX Except function in where clause

I'm having problem restricting a query in mdx, using except function at where clause. i need to retrieved a set of data but which not in an specific set. Then i created the next query:

select {[Measures].[Amount], [Measures].[Transaction Cost], [Measures].[Transaction Number]} ON COLUMNS,{[ManualProcessing].[All ManualProcessings].[MAGNETICSTRIPE], ManualProcessing].[All ManualProcessings].[MANUAL]} ON ROWS 
FROM [Transactions]
where except([Product].[All Products].Children,{[Product].[All Products].[Debit})

apparently this works fine, but when I try to add another restriction to slicer, I got this error: No function matches signature (Set,Member).

I'm currently working on mondrian 3.1

Is it possible to add multiple restriction to slicer when im sing the except function ? are there any other way to get this ?

Solution

  • Apparently this can't be done on Mondrian, because there is a bug that doesn't allow compound slicers. I found a nice solutions using Mondrian OLAP Server. It's creating a member which excludes the set that I didn't want. It looks like this.

    WITH member [Product].[Except] as ([Product].[All  Products]) - 
([Product].[All Products].[Debit]) 
SELECT {[Measures].[Amount],[Measures].[Transaction Cost], [Measures].[Transaction Number]} ON COLUMNS,
{[ManualProcessing].[All ManualProcessings].[MAGNETICSTRIPE],[ManualProcessing].[All ManualProcessings].[MANUAL]} ON ROWS 
FROM [Transactions] 
WHERE[Product].[Except]