Pentaho Analysis - Mondrian
  1. Pentaho Analysis - Mondrian
  2. MONDRIAN-980

MDX subquery (also known as "sub-select" and "subcube")

    Details

    • Type: New Feature New Feature
    • Status: Open
    • Priority: Medium Medium
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: Not Planned
    • Component/s: None
    • Labels:
      None
    • Notice:
      When an issue is open, the "Fix Version/s" field conveys a target, not necessarily a commitment. When an issue is closed, the "Fix Version/s" field conveys the version that the issue was fixed in.

      Description

      davidmet writes:
      > Hi everybody.... I have problems with a nested query after of a From MDX sentence . For instance:
      > select ....... from [db] .....
      > works fine, but:
      > select ....... from (select ...... from ..... where .......) ...............
      > don't works. JPivot says that there is a syntaxis error in "(".

      Mondrian does not support MDX subqueries. It should. See e.g. http://blogs.technet.com/b/paolt/archive/2005/02/14/372374.aspx

        Issue Links

          Activity

          Hide
          Julian Hyde added a comment -

          The email thread http://lists.pentaho.org/pipermail/mondrian/2012-October/004153.html makes a case that sub-selects are a more elegant way of providing "visual totals" than the VisualTotals function. I agree.

          However, sub-selects are quite a lot of work. They are definitely more than syntactic sugar. We're not likely to implement this feature by mondrian-4.

          Show
          Julian Hyde added a comment - The email thread http://lists.pentaho.org/pipermail/mondrian/2012-October/004153.html makes a case that sub-selects are a more elegant way of providing "visual totals" than the VisualTotals function. I agree. However, sub-selects are quite a lot of work. They are definitely more than syntactic sugar. We're not likely to implement this feature by mondrian-4.
          Hide
          Julian Hyde added a comment -

          Microsoft's documentation:

          Using Cube and Subcube Expressions
          http://msdn.microsoft.com/en-us/library/ms145564.aspx

          CREATE SUBCUBE Statement (MDX)
          http://msdn.microsoft.com/en-us/library/ms144916.aspx

          Show
          Julian Hyde added a comment - Microsoft's documentation: Using Cube and Subcube Expressions http://msdn.microsoft.com/en-us/library/ms145564.aspx CREATE SUBCUBE Statement (MDX) http://msdn.microsoft.com/en-us/library/ms144916.aspx
          Hide
          Maxime Caron added a comment -

          For a use case for this feature :

          If want to create a barchart with a bar for each month but also need to filter on a date range which might not completly overlap some of the month.

          When that happen I would expect the aggregate count for those month to only aggregaet over the days that fall in the date range instead of the aggregate for the whole month.

          I dont know if this is possible using VisualTotals but if it is this seem like that would not be fast.

          please see: http://stackoverflow.com/questions/12681096/how-to-groupby-and-filter-on-the-same-dimension-in-mdx

          Show
          Maxime Caron added a comment - For a use case for this feature : If want to create a barchart with a bar for each month but also need to filter on a date range which might not completly overlap some of the month. When that happen I would expect the aggregate count for those month to only aggregaet over the days that fall in the date range instead of the aggregate for the whole month. I dont know if this is possible using VisualTotals but if it is this seem like that would not be fast. please see: http://stackoverflow.com/questions/12681096/how-to-groupby-and-filter-on-the-same-dimension-in-mdx
          Hide
          Luc Boudreau added a comment -

          Maxime,

          Can you use something like this:

          with member [Measures].[Member] as 'Aggregate( Filter( [Dim].Members,

          {whatever contition}

          ) )
          select ....

          Show
          Luc Boudreau added a comment - Maxime, Can you use something like this: with member [Measures] . [Member] as 'Aggregate( Filter( [Dim] .Members, {whatever contition} ) ) select ....

            People

            • Assignee:
              Unassigned User
              Reporter:
              Julian Hyde
            • Votes:
              8 Vote for this issue
              Watchers:
              11 Start watching this issue

              Dates

              • Created:
                Updated: