Hitachi Vantara Pentaho Community Forums
Results 1 to 10 of 10

Thread: Order() method in MDX

  1. #1

    Default Order() method in MDX

    Hi,
    I am not sure, if have posted this on the right forum. Anyways,

    I have a simple MDX query,

    SELECT order([Measures].[Purchase],ASC) ON COLUMNS,
    [Date].[Week].Members ON ROWS
    FROM [Cube_Stock]

    for which I am getting the error message, MondrianException: Mondrian Error:No function matches signature 'order(, )'

    Is the order() method supported? If yes, why am getting this message. and if no, How do I sort the values?

    Thanks in Advance.

  2. #2
    Join Date
    Dec 2010
    Posts
    304

    Default

    Hi,
    the correct query is:

    SELECT {[Measures].[Purchase]} ON COLUMNS,
    Order({[Date].[Week].Members}, [Measures].[Purchase], ASC) ON ROWS
    FROM [Cube_Stock]

  3. #3

    Default

    Thank you.Lukolap..Thank you so much..Its working now.
    For any one who has got problem with Order(), please refer the following link for the syntax:
    http://msdn.microsoft.com/en-us/library/ms145587.aspx

  4. #4
    Join Date
    Aug 2014
    Posts
    13

    Default

    Hi, I have a similar problem: I tried to order the result of a MDX query that was written by another person (and I cannot contact him).
    The query is the following one:

    SELECT
    NON EMPTY {Hierarchize({[Elemento].[Elemento].Members})} ON COLUMNS,
    NON EMPTY
    CrossJoin([N. Acc].[N. Acc].Members, CrossJoin([Data prelievo].[Data prelievo].Members,
    CrossJoin([N. colata].[N. colata].Members, [Forno].[Forno].Members))) ON ROWS
    FROM [SALab1]
    WHERE
    Crossjoin(Crossjoin({[Elementi preferito.Codice preferito].[${preferito}]},
    {[Calendario.Anno].[${anno}]}), {[Calendario.Mese].[${mese}]})

    now, I wish to order the result of this query by "Data Prelievo", descending order, so i've tried to do this:

    select
    NON EMPTY {Hierarchize({[Elemento].[Elemento].Members})} ON COLUMNS,
    NON EMPTY(ORDER(Crossjoin([N. Acc].[N. Acc].Members, Crossjoin([Data prelievo].[Data prelievo].Members, Crossjoin([N. colata].[N. colata].Members, [Forno].[Forno].Members))), [Data prelievo].[Data prelievo].Members, DESC)) ON ROWS
    from
    [SALab1]
    where
    Crossjoin(Crossjoin({[Elementi preferito.Codice preferito].[${preferito}]}, {[Calendario.Anno].[${anno}]}), {[Calendario.Mese].[${mese}]})

    but, as AchuthanExelixir, I got the message: "Mondrian Error:No function matches signature 'order(, ,)'.



    What did I do wrong? (It's my first time with MDX query, I looked for online tutorials and docs, but they don't explain it very well).
    Thanks for your help, regards
    Last edited by newbie_; 08-21-2014 at 07:52 AM.

  5. #5
    Join Date
    Jan 2013
    Posts
    796

    Default

    Looks like your ORDER function is defined w/ a fat nested CJ as the first arg and [Date prelievo].Members as your second arg. The second arg should be a numeric expression, not a set expression.

  6. #6
    Join Date
    Aug 2014
    Posts
    13

    Default

    Thanks for your reply mcampbell.
    As I said, I'm new to MDX query, I searched on the web but the only thing I found is the syntax of ORDER statement.
    I tried to write the new query as you can see in my old post, so can you explain me how to change that query to fix it?
    Thank you very much and sorry if my reply comes so late.

  7. #7
    Join Date
    Jan 2013
    Posts
    796

    Default

    The second parameter tells the Order() function what to order by. Typically that will be a Measure reference, and the set will be ordered based on the measure values. So you probably want to swap the [Date prelievo].Members reference in your order function w/ a numeric value that you actually want to order by.

    HTH

  8. #8
    Join Date
    Aug 2014
    Posts
    13

    Default

    Thanks for your answer. I want to order the result by date, in descending order, so the result will show the newest value and gradually the older ones, until you have the older one as the last value of all.
    The property I would like to use to order the result is [Data prelievo], and I have no idea what is the numerical value of it, because it depends by the source that the user choose.
    I hope to was being able to explain the problem.
    Regards,
    newbie_

  9. #9
    Join Date
    Jan 2013
    Posts
    796

    Default

    Typically Date dimensions are defined in such a way that they are automatically sorted in date ascending order. Because of this, a set like [Time].[Month].members will be sorted w/o need to wrap in Order().
    Take a look at http://mondrian.pentaho.com/document...er_and_Display.

    If this still isn't getting at your question, maybe you could provide an example of the output you'd like to get.

  10. #10
    Join Date
    Aug 2014
    Posts
    13

    Default

    Thanks for the answer.
    I'll try to find out a solution based on your suggestions, if I cannot resolve the problem I'll post you an example of the output.
    Thank a lot,
    newbie_

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.