Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2005
    Posts
    41

    Unanswered: getting year/quarter/month/day in the right order (was "Dates")

    I have a program that calls queries (OLAP system) the system includes a dimension of date: Year, Quater, Month, Week

    When the result appears in the table, it is not in order? Only the year is in oredr and after that each heirachy is wrong and not in order....not sure how to do this!!!

    any help would be grateful!!! not sure what I should be looking at.....

  2. #2
    Join Date
    Feb 2005
    Posts
    41
    By the way I'm using MDX query and the Quater is sorted as well
    ie only month and week are not in correct order.

    PLEASE HELP

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    The order of data in a database is meaningless...

    You need to supply what order you wish to see it....

    Even with a clustered index...data can be all over the place until it's reorganized
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Feb 2005
    Posts
    41
    so i need to use the order function??? ie Order in asc order???

    but then i need to order the Month and week and do not need to do anything to year and quarter???

    ive done something lik this before and all the data was ordered without me needed to do anything!!!????

  5. #5
    Join Date
    Feb 2005
    Posts
    41
    please help ive been workin at this 4 2 days and doing my head in

    Basically the result of my MDX query, does not have the correct order when displaying the months (eg is in order feb, ja, march, may, july, august) and when showing weeks (doesn't go in order wwek 1, week2...week 10 etc). The year is in order, and so is the quarter..

    how do i get the result dislpayed in the correct month order/week order????

    THANKS

  6. #6
    Join Date
    Feb 2005
    Posts
    41
    anybody got any ideas????

  7. #7
    Join Date
    Feb 2005
    Posts
    78
    You need to add an order by clause to the select statement as suggested before. Did you try this??? Order by all four fields in order starting from year.

    If you did this query on a different database and got the data in order you just got lucky.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Since the MDX SELECT does not support the ORDER BY clause, the only way that I know to order the results is to use SQL Server to do a standard SELECT statement using the OPENQUERY function, then use ORDER BY on the SQL SELECT to get what you want. It isn't what I consider "pretty", but it works!

    -PatP

  9. #9
    Join Date
    Feb 2005
    Posts
    41
    tahnks for replyin....

    could i use the order by property on the dimension in any way to arrange the dimension, so that wen i call the query, the dimension will already b sorted???

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by asbir
    tahnks for replyin....

    could i use the order by property on the dimension in any way to arrange the dimension, so that wen i call the query, the dimension will already b sorted???
    While you could do it, and it might work, I don't think it will be guaranteed if you do multi-dimensional queries. I suspect that it might work fine for queries using only one dimenstion, but I'm not comfortable that it would be certain to behave as you'd like.

    I know that it works using the SQL SELECT with the OPENQUERY function, every time, without fail.

    -PatP

  11. #11
    Join Date
    Feb 2005
    Posts
    41
    cool, im looking into how to use openquery function, just another quick qusetion:

    as a test i ran a query on FoodMart dbase, making time as the row, I didnt order in any such way and was the simlar query:

    SELECT
    { [Measures].[Warehouse Sales] } ON COLUMNS,
    { [Time].Members } ON ROWS
    FROM
    [Warehouse]

    the result of the query had the time shown as:
    1997
    Q1
    1
    2
    3
    Q2
    4
    5
    6
    Q3
    7
    8
    9
    Q4
    10
    11
    12
    1998
    .....

    which is sorted in the way i want my time to be sorted, when i ran the same query on my dbase the time is in no such order; it is all mixed up, was something missed out when creating the time dimension or inputting data by any chance???

  12. #12
    Join Date
    Feb 2005
    Posts
    41
    ignore my last reply i was wrong, it is nearly hierarchically sorted... it has a couple of months (therefore weeks in the wrong places), which is the same order as when I call the months in the original query.....

  13. #13
    Join Date
    Oct 2003
    Posts
    706
    Year, month, quarter and so-on are all derived from one value: "date." This should be your sort-field. You can sort by a column even if that column does not appear in the final output.

    If you cannot get the results to be fed to you in the desired order, you must first retrieve them then order them separately.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  14. #14
    Join Date
    Feb 2005
    Posts
    41
    il tried:


    SELECT {[Measures].Members } ON COLUMNS,
    { Order ({[Accounting Date]. Members }, ( [Accounting Date].[Week No]) , BASC ) } ON ROWS
    FROM
    [Accounts Sales]


    but i get error saying unknown dimension or mmember [Accountin Date].[Week No] any ideas?????

  15. #15
    Join Date
    Feb 2005
    Posts
    41

    still confused with ordering!

    without doing sny sort at all, if I call the data on a standard query, the data should seemed to be some what sorted, only some months are out of date (the same order as when I use the original query) Does that mean that the data is shown depending on how it was entered in the table!!!

    I think I have made this problem more complicated than it should be, all I want to do is to shon the data in date order. I have tried using the order fuinction, but always been unsuccessful!!

    Does it matter that the type of the date table is string?? Is it right because it is a month name and not an actual date...

    PLEASE HELP...............

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •