Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: MDX to Filter on a Date Range

  1. #1
    Join Date
    Jun 2008
    Posts
    12

    Default MDX to Filter on a Date Range

    I'm trying to write an MDX query to get some results based on a range of days. I'm testing the query with MDX Query tool (in the Schema Workbench).

    I found a post on the web, that recommended an approach like this:

    WITH MEMBER [Time].[somethingnew]
    AS '[Time].[2008].[March].[24]:[Time].[2008].[March].[30]'
    SELECT
    {[Measures].[Views]} ON COLUMNS,
    Non Empty ( TopCount( {([Industry].Children)}, 10, [Measures].[Views]) ) ON ROWS
    FROM [Traffic]
    WHERE { [Time].[somethingnew] }

    1) Mondrian complains
    Mondrian Error:Syntax error in MDX expression '[Time].[2008].[March].[24]:[Time].[2008].[March].[30]'
    Mondrian Error:Syntax error at line 1, column 28, token ':'

    Why can't I use the range operator in this context?

    2) If I simplify the query for testing purposes and use
    WITH MEMBER [Time].[somethingnew]
    AS '[Time].[2008].[March].[24]'
    SELECT
    {[Measures].[Views]} ON COLUMNS,
    Non Empty ( TopCount( {([Industry].Children)}, 10, [Measures].[Views]) ) ON ROWS
    FROM [Traffic]
    WHERE { [Time].[somethingnew] }

    Then Mondrian says:
    Mondrian Error:MDX object '[Time].[somethingnew]' not found in cube 'Traffic'

    What am I doing wrong?

    Any help is greatly appreciated.

    Marc

  2. #2
    Join Date
    Jan 2007
    Posts
    17

    Default mdx range

    did you try { } instead of ' ' ?

    {[Time].[2008].[March].[24]:[Time].[2008].[March].[30]}

    see http://msdn.microsoft.com/en-us/library/ms146001.aspx

    regards Kees
    Last edited by Kees Romijn; 06-17-2008 at 03:43 PM.

  3. #3
    Join Date
    Jun 2008
    Posts
    12

    Default Closer with the Aggregate function

    Thanks for the response, Kees.

    I just gave that a try and got another error:
    Mondrian Error:Member expression ... must not be a set.

    -----

    This following version seems closer, but I'm still faced with problem #2 in my original post (Mondrian Error:MDX object '[Time].[days]' not found in cube 'Traffic').

    WITH MEMBER
    [Time].[days] AS Aggregate( {[Time].[2008].[March].[24]:[Time].[2008].[March].[25]} )
    SELECT
    [Time].[days] ON COLUMNS,
    Non Empty ( TopCount( {([Industry].Children)}, 10, [Measures].[Views]) ) ON ROWS
    FROM [Traffic]
    WHERE { ([Partner].[xyz],[Measures].[Views]) }

    Marc

  4. #4
    Join Date
    Mar 2007
    Posts
    216

    Smile

    Hi,
    Quote Originally Posted by mlimotte View Post
    Thanks for the response, Kees.

    I just gave that a try and got another error:
    Mondrian Error:Member expression ... must not be a set.

    -----

    This following version seems closer, but I'm still faced with problem #2 in my original post (Mondrian Error:MDX object '[Time].[days]' not found in cube 'Traffic').

    WITH MEMBER[...]

    Marc
    Try WITH SET [myset] AS {[Time].[2008].[March].[24]:[Time].[2008].[March].[25]} instead of WITH MEMBER

    a+, =)
    -=Clement=-

  5. #5
    Join Date
    Jun 2008
    Posts
    12

    Default Tried WITH SET

    Thanks for the suggestion, Clement.

    Gave WITH SET a try, but got similar error:
    Mondrian Error:No function matches signature '(<Member>, <Set>)'

    I'm starting to worry that Mondrian just doesn't support queries that filter across arbitrary ranges. But that seems like a pretty basic feature of a cube, to me. Am I missing something?

  6. #6
    Join Date
    Jun 2007
    Posts
    27

    Default Odd behaviour with Pivot

    Hello:

    I am new to Mondrian. I have just created a pivot successfully using the Mondrian cube designer. When I run this and other pivots for some reason the last pivots result are shown in the next one and the drills are lost i.e no + visible.

    The problem is not there when you open the browser for the first time.

    Any help will be much appretiated.

    Regards
    Sunil

  7. #7
    Join Date
    Jun 2007
    Posts
    27

    Default Slicer text at the bottom of the table and chart

    Hi:

    In continuation to my earlier post. The problems seems to be resolved for the moment. Not exactly sure how. Just redid the entire cube definition.

    I am unable to get any text defining the 'Slicer' values next to the 'Slicer' text below the table and chart. I can see this working in the steelwheels examples by not in the 'Regions Quadrant' example. However am unable to figure out why.

    Can anybody help with this?

    Regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.