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:
    Code:
    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:
    Code:
    `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:
    Code:
    `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
    Posts
    1,618

    Default

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

    Julian

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.