Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Prepare datepicker parameter for mdx query?

  1. #1
    Join Date
    Mar 2008
    Posts
    364

    Default Prepare datepicker parameter for mdx query?

    Hi!
    Today I encountered a pretty tough problem while trying to create a parameterized Olap-Report in PRD 3.6

    In my mdx query I need to filter on a date range like:

    Code:
    With
    Set [TimeLimit] as '{([time].[Year].[2009] : [time].[Year].[2010])}'
    ...
    Where ([TimeLimit])
    I need to get the daterange from two datepickers. I was able to convert the date to a mondrian member string using a post-processing formula like described here
    http://diethardsteiner.blogspot.com/...ho-report.html

    Now the hard part:
    - When using the range expression in mdx I get an empty result if the higher or lower bound are not in the data.
    - I don't know the minimum and maximum date as the date dimension in my
    cube is calculated from a timestamp "on the fly" (I know this gives bad performance but I can't change the database design)

    Is it possible to fetch the minimum and maximum date in a query first and then use this in the post-processing formula to see if it is higher/lower than
    what the user entered? How could I reference this value in the post-processing formula? Anybody done something like that before?

    best regards,

    mgie

  2. #2
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    Timestamp is good - just make a SELECT MAX(timestampcol) FROM dimension_table and then convert it into a java-date and compute a year from it. (This should not be expensive if you have a index on that column.)

    Then you can use a formula or other expression to select either your date or your parameter input.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  3. #3
    Join Date
    Mar 2008
    Posts
    364

    Default

    Quote Originally Posted by Taqua View Post
    Then you can use a formula or other expression to select either your date or your parameter input.
    This seems to be the point I am missing. I tried to decide that "inside" the parameter using post-processing formula. But it looks like the result of the query that is attached to the parameter is not accessible in the post-processing formula.
    So I could just use a regular formula expression and use that in the query like an input parameter? I thought the formula expressions were evaluated after the query execution. And how would that SELECT MAX... be executed before the olap query? I could only think of putting the olap query into a subreport.
    Last edited by mgie; 07-21-2010 at 08:32 AM.

  4. #4
    Join Date
    Mar 2008
    Posts
    364

    Default

    seems like this post
    http://forums.pentaho.org/showthread.php?t=75654
    means that it is not possible at the moment to use a value from a "pre-query" in other paramaters that go into the "main-query"... or am I wrong (hopefully)?

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.