Question

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 ?

Answer 1

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]

Answer 2

The Except function only works with sets. But you can use n dimensions on your where:

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}),
      except([Set],[Set to exclude])
   )

This works in Analysis Services 2005, it should work in Mondrian

友情链接