Report Portal

MDX using EXISTING, AGGREGATE, CROSSJOIN and WHERE

It is a well-published approach to using the EXISTING function to decode AGGREGATE members and nested sub-query filters.  Mosha wrote a good blog on it here and a more recent one here.  The use of EXISTING in these scenarios is very useful and sometimes the only option when dealing with multi-select filters.  However, there are some limitations I have run across when using the EXISTING function against an AGGREGATE member:

  1. The AGGREGATE member must be assigned to the Dimension.Hierarchy being detected by the EXISTING function in the calculated measure.
  2. The AGGREGATE member cannot contain a crossjoin from any other dimension or hierarchy or EXISTING will not be able to detect the members in the AGGREGATE member.

Take the following query (from Adventure Works DW 2008): 

Read more...

 

Tags: mdx

 

2007-2015 VidasSoft Systems Inc.