Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: How-to get date range to work

  1. #1
    Join Date
    May 2006
    Posts
    104

    Question How-to get date range to work

    I'd like to enhance the cube from the "Cube Designer Quick Start Guide" with a parametrized from/to date range, but can't seem to get the right syntax together. Could someone please help me with this? Thx

    The mondrian.xml likes like this and works as MDX Query 'default' just fine:
    Code:
    <?xml version="1.0" encoding="UTF-8"?>
    <Schema name="MyCube">
        <Cube name="MyCube">
            <Table name="ORDERFACT"/>
            <Dimension name="CUSTOMER" foreignKey="CUSTOMERNUMBER">
                <Hierarchy name="CUSTOMER" hasAll="true" allMemberName="All customer" primaryKey="CUSTOMERNUMBER">
                    <Table name="CUSTOMER_W_TER"/>
                    <Level name="CustomerName" table="CUSTOMER_W_TER" column="CUSTOMERNAME" uniqueMembers="false">
                        <Property name="First Name" column="CONTACTFIRSTNAME"/>
                        <Property name="Last Name" column="CONTACTLASTNAME"/>
                    </Level>
                </Hierarchy>
            </Dimension>
            <Dimension name="TIME" foreignKey="TIME_ID">
                <Hierarchy name="TIME" hasAll="true" allMemberName="All time" primaryKey="TIME_ID">
                    <Table name="TIME"/>
                    <Level name="Year" table="TIME" column="YEAR_ID" uniqueMembers="false"/>
                    <Level name="Quarter" table="TIME" column="QTR_ID" nameColumn="QTR_NAME" uniqueMembers="false"/>
                    <Level name="Month" table="TIME" column="MONTH_ID" nameColumn="MONTH_NAME" uniqueMembers="false"/>
                </Hierarchy>
            </Dimension>
            <Measure name="Total Price" column="TOTALPRICE" aggregator="sum" datatype="Numeric" formatString="#,##0.##"/>
        </Cube>
    </Schema>
    Resulting in an MDX query of:
    Code:
    select NON EMPTY {[Measures].[Total Price]} ON COLUMNS,
      NON EMPTY Hierarchize({([CUSTOMER.CUSTOMER].[All customer], [TIME.TIME].[All time])}) ON ROWS
    from [MyCube]
    Now, what do I have to do to keep TIME as a dimension, but limit it to, say, 2004 and 2005? I tried using:
    Code:
    select NON EMPTY {[Measures].[Total Price]} ON COLUMNS,
      NON EMPTY Hierarchize({([CUSTOMER.CUSTOMER].[All customer], [TIME.TIME].[All time])}) ON ROWS
    from [MyCube]
    where ([TIME].[Year].[2004]:[TIME].[Year].[2005])
    but got "Mondrian Error : Dimension '[TIME]' appears in more than one independent axis.". So I tried:
    Code:
    select NON EMPTY {[Measures].[Total Price]} ON COLUMNS,
      NON EMPTY Hierarchize({([CUSTOMER.CUSTOMER].[All customer], ([TIME].[Year].[2004]:[TIME].[Year].[2005]))}) ON ROWS
    from [MyCube]
    but got "Mondrian Error : No function matches signature '(<Member>, <Set>)'". I did read articles in DB Journal, etc., but still am missing something. BTW, I using Pentaho. V1.2.0 build 534 GA on a local WinXP with Java 1.6.0_02.
    Last edited by rlance; 09-20-2007 at 12:47 PM. Reason: Small correction
    Ralph

  2. #2
    Join Date
    Jul 2007
    Posts
    2

    Default

    Hi

    try this syntax with Crossjoin

    Code:
    select NON EMPTY {[Measures].[Total Price]} ON COLUMNS,
       NON EMPTY Crossjoin({[CUSTOMER].[All customer]}, {([TIME].[Year].[2004] : [TIME].[Year].[2005])}) ON ROWS
    from [MyCube]
    Regards

    Selecta

  3. #3
    Join Date
    May 2006
    Posts
    104

    Default

    Thanks! That worked and got me going in the right direction.
    Ralph

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.