Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Problem trying to perform date range MDX where clause

  1. #1
    DEinspanjer Guest

    Default Problem trying to perform date range MDX where clause

    Ideally, I'd like to prompt the user for a start and end date and then restrict the resulting pivot to just that range.

    I'm working with the attached schema and so far I've been trying the following query with hard-coded dates:
    with member [Local Date.YD].[DateRange] as 'Aggregate(([Local Date.YD].[All].[2007].[2007-11-28] : [Local Date.YD].[All].[2007].[2007-12-01]))'
    select NON EMPTY Hierarchize({[Profile].[All Profiles]}) ON COLUMNS,
      NON EMPTY Crossjoin({[Measures].[Count Requests], [Measures].[Distinct Visitors]}, {[Context.Method-Appcontext Hierarchy].[All].[xxx].[yyy]}) ON ROWS
    from [Method Calls]
    where [Local Date.YD].[DateRange]
    After precaching, there are two queries executed to get my results. The one for the count measure seems okay. it has the following in it's raw SQL where clause:
    `f_method_calls`.`local_date_pk` = `d_date`.`date_pk` and `d_date`.`date` in ('2007-11-28', '2007-11-29', '2007-11-30', '2007-12-01', '2007-12-02') and ...
    but the query for the distinct count measure is weird:
    `f_method_calls`.`local_date_pk` = `d_date`.`date_pk` and (`d_date`.`date` in ('2007-11-28', '2007-11-29') and `d_date`.`date` = '2007-11-30' and `d_date`.`date` = '2007-12-01' and `d_date`.`date` = '2007-12-02')]
    This conflicting criteria is causing my distinct count measure to always show 0.
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 1999


    That sounds like a bug. As you surmised, most of those ANDs in the last query should be ORs. Can you log it at please? If you can provide a testcase against the FoodMart schema, that would be even better.


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.