Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: MDX with parameters. Urgent help needed

  1. #1

    Default MDX with parameters. Urgent help needed

    Dear all,

    i m trying to use my olap cubes for building reports.
    In my report I need to display only three fields and define two input parameters.
    The report head looks like:
    Publisher, DateTime, Quantity

    The input parameters for my report should be:
    Start Date, End Date, Publisher

    When I use only one param "Name" it works good.
    Code:
    SELECT
    
    NON EMPTY { [Measures].[Q Impressions] } ON COLUMNS,
    NON EMPTY (
    CrossJoin ( [Publisher].[All Publisher].[${paramPublisher}], { [TimePeriods Degenerated].[All Periods].Children })
    )
     ON ROWS FROM
    [Ad Banners]
    All that I need is just to add date parameters together with ${paramPublisher}. I wrote the next MDX:
    Code:
    WITH member [TimePeriods Degenerated].[AggregateDates]
    AS 'Aggregate([TimePeriods Degenerated].[All Periods].[${date1}] : [TimePeriods Degenerated].[All Periods].[${date2}])'
    SELECT
    NON EMPTY { [Measures].[Q Impressions] } ON COLUMNS,
    NON EMPTY { [Publisher].[All Publisher].[${paramPublisher}] }
     ON ROWS FROM
    [Ad Banners]
    where [TimePeriods Degenerated].[AggregateDates]
    But in this case I can't display DateTime field ([TimePeriods Degenerated].[All Periods].Childrens) as row in report. Because, when I add TimePeriods into query:
    Code:
    WITH member [TimePeriods Degenerated].[AggregateDates]
    AS 'Aggregate([TimePeriods Degenerated].[All Periods].[${date1}] : [TimePeriods Degenerated].[All Periods].[${date2}])'
    SELECT
    NON EMPTY { [Measures].[Q Impressions] } ON COLUMNS,
    NON EMPTY (
    CrossJoin ( [Publisher].[All Publisher].[${paramPublisher}], { [TimePeriods Degenerated].[All Periods].Children })
    ) ON ROWS
    FROM
    [Ad Banners]
    where [TimePeriods Degenerated].[AggregateDates]
    i receive error:

    Code:
    Hierarchy '[TimePeriods Degenerated]' appears in more than one independent axis.
    That is why I need your help dear gurus, how should I correct write my MDX query to achiev my goal.

    Thank you in advance,
    Alex.

  2. #2
    Join Date
    Jul 2007
    Posts
    2,498

    Default

    Tip: the work "urgent" in the topic almost always will guarantee that no one will answer you. If it's that urgent, buy pentaho support.
    Pedro Alves
    Meet us on ##pentaho, a FreeNode irc channel

  3. #3
    Join Date
    Jun 2007
    Posts
    112

    Default

    The filter() expression might be what you are looking for. You can't put the dates in both the rows and the slicer, but filter() should let you restrict the part of the date dimension that you include on the rows.

    Good luck!

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.