Results 1 to 6 of 6

Thread: time on both col and rows

  1. #1
    Join Date
    Apr 2005
    Location
    CA
    Posts
    25

    time on both col and rows

    I'd like a report with days across the top and 24 hours down the rows so management can compare call data performance hour to hour across days.

    select
    { [TimeByMinute].[All TimeByMinute].[2005].[May].[1] : [TimeByMinute].[All TimeByMinute].[2005].[May].[10] } on columns ,
    {lastperiods(24, [TimeByMinute].[All TimeByMinute].[2005].[May].[1].[23])} on rows
    from ACD_Calls2
    where [Measures].[Calls]

    I get the "duplicate dimensions across (independant) axes" message.

    Any ideas?
    Richard

  2. #2
    Join Date
    Apr 2005
    Location
    CA
    Posts
    25
    Opps,
    this probably makes more sense, but still sames error message:

    select
    { [TimeByMinute].[All TimeByMinute].[2005].[May].[1] : [TimeByMinute].[All TimeByMinute].[2005].[May].[10] } on columns ,
    { [TimeByMinute].[Hour].members } on rows
    from ACD_Calls2
    where [Measures].[Calls]

  3. #3
    Join Date
    Sep 2002
    Posts
    169
    The easiest way to get the result you are after is to create a dimension that contains the hours of the day (separate from the TimeByMinute dimension

  4. #4
    Join Date
    Apr 2005
    Location
    CA
    Posts
    25

    Got it

    Thanks Stephen.
    I got a similar response on sql central too. They also mentioned Yukon resolving some of this (I wouldn't know). I guess the hard part of mdx- seeing all the xtreme complex mdx on the web- is thinking that I just don't know enough, when in fact there is no answer but a work-a-round.
    Have a good day and thanks again for taking the time to answer.
    R

  5. #5
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607
    Duplicating a dimension across independent axes typically results in this failure - usually not a logical arrangement anyway, but there are circumstances where it makes sense.

    The good news is that I have published an article that specifically addresses one approach to this, in graphic detail. Follow this link:

    http://www.databasejournal.com/featu...le.php/3490561

    Let us know how it goes. And have a look through my series' at DBJ for other stuff that you are certain to encounter at some point.

    Have a great weekend, and good luck.

    Bill

  6. #6
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607
    BTW - I don't imagine this will be "resolved" in Yukon (what isn't, apparently? Anything anybody ever wanted is described somewhere as magically appearing in Yukon). This is designed behavior, not a "bug."

    And multiple time dimensions is not a good idea from many standpoints, either (including MS Best Practices, and others). The approach in my article avoids this (see link in my earlier answer).

    If you want to do multiple time hierarchies (fiscal vs. calendar is a common need), then you have a reason to do it via dimensions (actually in the same one), but otherwise, as in your described case, think about it (and the alternative I show in the article) first....

    Let me know if you have any questions.

    Good Luck.

    Bill

Posting Permissions

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