0

I have been working on a custom dll (that is called via a custom xll / Excel Addin) to construct MDX and return 2D data.

It's working nicely and I just went to work out how I add the ability to send in an exclusion list using EXCEPT.

I built up a query with filtering and this query works except it ignores the EXCEPT. Anyone with more MDX than me (I'm about 2 months in haha :)) know why?

Thanks

Leigh

WITH  
Member  [Measures].[Book_Label] AS [Book].[Book].CURRENTMEMBER.MEMBER_CAPTION 
Member  [Measures].[Isin_Label] AS [Isin].[Isin].CURRENTMEMBER.MEMBER_CAPTION 
SELECT 
NON EMPTY 
{[Measures].[Book_Label],[Measures].[Isin_Label],[Measures].[Notional.SUM]} 
ON COLUMNS, 
NON EMPTY ORDER
(
EXCEPT(
FILTER(
([Book].CHILDREN,[Isin].CHILDREN), 
([Book].[Book].CURRENTMEMBER.MEMBER_CAPTION = "ALGO1")
), 
[Isin].[Isin].[DE0001104776]),
[Notional.SUM]
,
BASC) 
ON ROWS 
FROM[TraderCube] 
WHERE ([Date].[Date].[2019-11-18])
MoazRub
  • 2,881
  • 2
  • 10
  • 20
TilleyTech
  • 441
  • 5
  • 13

2 Answers2

1

That is nice progress in two months. A humble piece of advise, you should always specify your problem in simple words along with the code developed so far. That helps the person answering.

Form your code, my understanding is you want "ALGO1" books with all members of [ISin] except the member "DE0001104776". Based on this understanding use the code below

NON EMPTY
ORDER
(
([Book].[Book].[ALGO1],{[Isin].[Isin].children-[Isin].[Isin].[DE0001104776]}),
[Notional.SUM],
BASC
)
MoazRub
  • 2,881
  • 2
  • 10
  • 20
  • Hey. Thanks I think I picked it up quickly as I've been doing software dev since mid 90s, SQL since then, and I work on the Java8 code on the server side of the cube too so the MDX came to me quickly / with lots of trial and error! Thanks for your feedback. I will bear that in mind! :). I did use what you have outlined in tests / hardcoded MDX. Where the tool must be able to cope with multiple filters I ended up using FILTER(set I want, (condtion AND condition) OR (conditon)...chained together and it works nicely for many filters). I think that you cannot use FILTER and EXCEPT together. – TilleyTech Nov 20 '19 at 09:53
  • So I think what I will have to do is detect the presence of EXCEPT list of argument(s) and if this is the case build up the query differently (or rewrite everything but im on the trading floor and have a time limit! haha). – TilleyTech Nov 20 '19 at 09:54
  • 1
    You are welcome.If the answer solves your issue you should consider marking it as an answer and/or upvoting it. – MoazRub Nov 20 '19 at 10:25
  • I tried writing a hardcoded query to test (before I start to build generic C# code to construct the queries. "WITH Member [Measures].[Book_Label] AS [Book].[Book].CURRENTMEMBER.MEMBER_CAPTION Member [Measures].[Isin_Label] AS [Isin].[Isin].CURRENTMEMBER.MEMBER_CAPTION SELECT NON EMPTY {[Measures].[Book_Label],[Measures].[Isin_Label],[Measures].[Notional.SUM]} ON COLUMNS, NON EMPTY ORDER(EXCEPT({[Book].[Book].[ALGO1], [Book].[Book].[EY_LDN]},{[Isin].[Isin].[Isin]-[Isin].[Isin].[DE0001104776]}),[Notional.SUM],BASC) ON ROWS FROM[TraderCube] WHERE([Date].[Date].[2019-11-19]) works however: – TilleyTech Nov 20 '19 at 11:14
  • returns Book Isin Notional.SUM EY_LDN AllMember ALGO1 – TilleyTech Nov 20 '19 at 11:15
  • Basically [Isin].[Isin].[Isin] (or [Isin].CHILDREN) - [Isin].[Isin].[some Isin] returns ALLMEMBER – TilleyTech Nov 20 '19 at 11:16
  • Actually I just cross-referenced it against a normal pivot (against the same cube source) and the - [Isin].[Isin].[DE0001104776] has no effect as the Notional.SUM is the same total whereas I'd expect that total to be less the amount for that Isin. Mmmmm :) – TilleyTech Nov 20 '19 at 11:28
  • are you still facing the issue? – MoazRub Nov 20 '19 at 14:52
  • Hey. Yeah it didn't work although I have other items of work to add to it. A request for totals came in so I changed my [dim].CHILDREN to [Dim].Dim].Members to get a total row (on the rightmost dimension) except it is wrong/inconsistent with a raw excel pivot agains the cube. I think it's to do with the way certain measures are calculated as they are often custom 'postprocessors' in Java doing custom stuff. So I will keep my own running totals in C# :) – TilleyTech Nov 21 '19 at 15:05
  • I'll update this ticket/thread when i get the EXCEPT working. – TilleyTech Nov 21 '19 at 15:06
0

I returned to trying out combining my currently working 1..n FILTER builder in conjunction with an EXCEPT (requested by business). Unfortunately, despite the query passing syntax check and executing, as reported in original post the cube/server ignores it.

I just tried adding a <> to my FILTER and it worked! :)

Here's an example. 

WITH  
  Member  [Measures].[Book_Label] AS [Book].[Book].CURRENTMEMBER.MEMBER_CAPTION 
  Member  [Measures].[Isin_Label] AS [Isin].[Isin].CURRENTMEMBER.MEMBER_CAPTION 
SELECT 
NON EMPTY {[Measures].[Book_Label],[Measures].[Isin_Label],[Measures].[Notional.SUM]} 
ON COLUMNS, 
NON EMPTY 
ORDER( 
FILTER(
([Book].CHILDREN,[Isin].CHILDREN), 
(([Book].[Book].CURRENTMEMBER.MEMBER_CAPTION = \"ALGO1\") AND 
([Isin].[Isin].CURRENTMEMBER.MEMBER_CAPTION <> \"DE0001102309\"))
),[Notional.SUM],
BASC) 
ON ROWS 
FROM[TraderCube] 
WHERE([Date].[Date].[2019-11-21])
TilleyTech
  • 441
  • 5
  • 13