Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Month or week in parameter

  1. #1

    Default Month or week in parameter

    Good afternoon!
    I use PRD 3.5. For operation with dates in report parameters there is only a type of parameter DatePicker? Whether It is possible to make somehow for the report the dropping out list with a multichoice of months (or weeks)? For example:
    January
    February
    March

    Or:
    Week 1 (01.01 - 07.01)
    Week 2 (08.01 - 14.01)

    Prompt how to make, please, it easier? Thanks.

  2. #2
    Join Date
    Nov 2009
    Posts
    25

    Default

    Make a query or a table with the months and then in the parameter conf , in the query selection put the table or the query that have the months, for multiselection use, 'multiselection- box'

  3. #3

    Default

    Thanks.
    I assumed that there are intrinsic functions in pentaho for this purpose. But it is necessary to query(((
    Prompt, please, as the query for a choice of weeks will look. I will not think up that in any way.

  4. #4

    Default

    All good afternoon!
    I will result inquiries for the list of weeks and months:

    select to_char(num)||' week: '||to_char(week_begin)||' - '||to_char(week_end) as week_of_year from
    (
    select
    level as num,
    (select trunc(trunc(sysdate,'YY'), 'DAY') from dual)+(level-1)*7 WEEK_BEGIN,
    (select trunc(trunc(sysdate,'YY'), 'DAY') from dual)+(level)*7-1 WEEK_END
    from dual
    connect by
    (select trunc(trunc(sysdate,'YY'), 'DAY') from dual)+(level-1)*7<=(select trunc(sysdate,'YY')+365 from dual)
    ORDER BY 1
    )

    select to_char(month_begin,'Month') as month_of_year from
    (
    select
    level as num,
    add_months((select trunc(sysdate,'YY') from dual),(level-1)) MONTH_BEGIN,
    add_months((select trunc(sysdate,'YY') from dual),(level))-1 MONTH_END
    from dual
    connect by
    add_months((select trunc(sysdate,'YY') from dual),(level))<=(select trunc(sysdate,'YY')+365 from dual)
    ORDER BY 1
    )
    Now the big question how to make better definition of a time interval for further filtering of the data?
    For example, has selected January and February. Then it is necessary to define an interval: 01.01 - 28.02.
    Whether it is possible to make definition of intervals in other query, and then to use this data for query (main query) on which the report is under construction? Or it is necessary to do only in the main query (to define variables and in "where" to use these variables)? Whether Not too it will decelerate creation of reports?
    It would be desirable to find the most simple and fast solution.
    In advance thanks for answers.

  5. #5

    Default

    I have troubleshot easier.
    In constructions where has transformed date to string and has revealed string presence in parameter through in.
    Now there was such question: how to access to parameter with a multiselection? It is necessary to mirror, for what months the report.
    For example:
    Months of the report: January, February.
    Did with the help message_field $ (param_month). How to be with a multiselection?

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

    Default

    Multi-selection will be expanded automatically to a separate prepared-statement parameter for each element in the array of values for this parameter.

    ${parameter}

    with three elements as values becomes

    ?, ?, ?

    in the sql-statement (and of course, the values are filled in as well).

    Therefore, you can use it in where-clauses like this:

    SELECT * FROM table WHERE column IN (${parameter})


    In 3.6, callable statements (calling a stored procedure) do not receive such a parameter-expansion and the array is passed in as a single parameter.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  7. #7

    Default

    If I in message_field access to parameter multiselection ($(param_month)) the report I receive the following:
    [Ljava.lang.String;@159abf4
    How in the report to see the selected months through a comma?

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

    Default

    The multiple-value replacement only works in the datasources.

    For message-fields, you have to properly format the value first. You can use (for instance) the CSVTEXT formula function to convert a array into a CSV string. A MessageFormat would not know how to handle your array-data and defaults back to a plain "toString()" (which is the ugly gibberish you get right now).

    Use a plain string-field with the following formula and you shall see your values. There are also sample reports shipped with the report-designer that do the same.

    =CSVTEXT([your-array], FALSE())

    to get Human-readable text without any quoting applied to the elements in your array.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

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.