Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: MDX query with condition WHERE??

  1. #1
    Join Date
    Feb 2007
    Posts
    29

    Default MDX query with condition WHERE??

    Hi all, I am doing a JPivot table thanks to a simple MDX query and a table.

    My query is :

    select
    { [ITEC_FIC],[ITEC_CRS],[ITEC_GED],[ITEC_GLS],[NON_ITEC] } ON COLUMNS,{ [BL].members } ON ROWS

    from [PB_Pentaho]



    Now I want to add a condition just like in a SQL query "WHERE username = 'John' ",
    username is a column of the table.

    How can I do it in MDX? How should I change my cube to add the column username ?

    Here is my cube :

    <?xml version="1.0"?>
    <Schema name="gsdd03">
    <!-- Shared dimensions -->

    <Dimension name="BL">
    <Hierarchy hasAll="true" allMemberName="ITEC">
    <Table name="PB_MANAGEMENT"/>
    <Level name="Dept" column="NODE2" uniqueMembers="true"/>
    <Level name="Dept2" column="NODE3" uniqueMembers="true"/>
    <Level name="Dept3" column="NODE4" uniqueMembers="true"/>
    </Hierarchy>
    </Dimension>

    <Cube name="PB_Pentaho">
    <Table name="PB_MANAGEMENT"/>
    <DimensionUsage name="BL" source="BL"/>

    <Measure datatype="Numeric" name="ITEC_CAF" column="ITEC_CAF" aggregator="sum" />
    <Measure datatype="Numeric" name="ITEC_FIC" column="ITEC_FIC" aggregator="sum" />
    <Measure datatype="Numeric" name="ITEC_CRS" column="ITEC_CRS" aggregator="sum" />
    <Measure datatype="Numeric" name="ITEC_GED" column="ITEC_GED" aggregator="sum" />
    <Measure datatype="Numeric" name="ITEC_GLS" column="ITEC_GLS" aggregator="sum" />
    <Measure datatype="Numeric" name="NON_ITEC" column="NON_ITEC" aggregator="sum" />
    </Cube>
    </Schema>



    Thanks a lot for your help.

  2. #2
    Join Date
    Sep 2007
    Posts
    2

    Default

    I would create a new Dimension with the Username as a Level or if it makes sense you can add an additional level to the "BL" dimension.

    Your query would look like

    Code:
    select
     {    [ITEC_FIC],[ITEC_CRS],[ITEC_GED],[ITEC_GLS],[NON_ITEC]    } ON COLUMNS,{  [BL].members } ON ROWS
    
    from [PB_Pentaho] where [NewDimension].[John]
    or
    Code:
    select
     {    [ITEC_FIC],[ITEC_CRS],[ITEC_GED],[ITEC_GLS],[NON_ITEC]    } ON COLUMNS,{  [BL].[John] } ON ROWS
    
    from [PB_Pentaho]

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.