Hitachi Vantara Pentaho Community Forums
Results 1 to 11 of 11

Thread: Making a parameter NOT mandatory -> How?

  1. #1
    Join Date
    Aug 2008
    Posts
    563

    Default Making a parameter NOT mandatory -> How?

    Hi,
    When creating parameters in report designer 3.5, you can definie if the paramater is mandatory or not.
    If I have a query like:
    SELECT
    *
    FROM
    table
    WHERE
    channel = ${channel} AND
    media = ${media}

    So, would it be possible to have media not mandatory in this case for example (I guess it wouldn't work unless I change something on the SQL query itself as well), or what is the real purpose of "mandatory"?

    Thanks,
    Diddy

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

    Default

    A parameter that is not set is null, so test for it and return true in that case:

    Code:
    .. WHERE (media = ${media} OR NULL = ${media}) AND channel = ${channel}
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  3. #3
    Join Date
    Aug 2008
    Posts
    563

    Default

    Many thanks for your answer!
    I tried to replicate this, but this is not really working within the report designer. The paramater media is a string. So the question is, will it return null or "null". I tried the where clause with both cases:
    1. WHERE (media=${media} OR "NULL"=${media})
    2. WHERE (media=${media} OR NULL=${media})

    If I write a normal SQL query with a WHERE clause like this:
    Code:
    WHERE (media="Null" OR "NULL"="Null")
    it works, but not with:
    Code:
    WHERE (media="Null" OR NULL=Null)
    or
    Code:
    WHERE (media="Null" OR NULL="Null")
    . I am working with MySQL 5. I ran this SQL queries in MySQL CC.

    The case that works, brings back all the media types and figures, which is what we expect.
    For media I have defined a drop down menu and NOT mandatory. What I realized is that the drop down menu doesn't provide a Null (or even "All") value in this case (You can use the blank field in the first run, but once you choose a value, there is no null value left). So it would be nice to have a Null value available (or even be able to name the Null -> "All media" or whatever).


    Anyway, do you think you could help me on the query?

    Thanks,
    Diddy

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

    Default

    It will return null as in "no value". The String "null" and NULL are two different things.

    http://forums.pentaho.org/showpost.p...26&postcount=4
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  5. #5
    Join Date
    Aug 2008
    Posts
    563

    Default

    Thanks a lot for clarifying this. I wrote a small tutorial for this in case somebody has the same problem. You can find it here: http://diethardsteiner.blogspot.com/...-not-mand.html

    Is there a way to show the NULL value in the drop down menu (or any other input method) consistantly?

    Would it be possible to give it a name, that the end user can easily understand, i.e. "All media types"?

    Thanks,
    Diddy

  6. #6
    Join Date
    Mar 2009
    Posts
    30

    Default

    That shouldn't be too hard to do; adjust your parameter query so that the 'All Media' value is added, like this:

    Select 'All Media' as Media from MediaTable
    UNION
    Select Media from MediaTable

    This will put the value 'All Media' in the drop down list as well. Now you can rewrite the statement (media = ${media} OR 0 = IFNULL(${media},0) as
    (media = ${media} OR 'All Media' = ${media})

    good luck!

  7. #7
    Join Date
    Aug 2008
    Posts
    563

    Default

    Hi Jos,
    Thanks a lot for your help! A perfect solution! I am just reading your book and I just wanted to thank you for providing the first book about Pentaho! It will make working with the Pentaho BI Suite much easier now for a lot of people!

    I have published a full tutorial covering this topic on: http://diethardsteiner.blogspot.com/...parameter.html ... I am sure some more users will have the same problem.

    Best regards,
    Diddy
    Last edited by diddy; 10-01-2009 at 12:01 PM.

  8. #8
    Join Date
    Mar 2009
    Posts
    30

    Default

    Hi Diddy,

    glad you like it; nice tutorial too. A source citation in it would have been nice too ;-)

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

    Default

    Wow, 12 posts only and this guy already knows enough to write a book?

    I was mearly a Junior Member at the time
    Pedro Alves
    Meet us on ##pentaho, a FreeNode irc channel

  10. #10
    Join Date
    Feb 2009
    Posts
    25

    Default

    Quote Originally Posted by Taqua View Post
    It will return null as in "no value". The String "null" and NULL are two different things.

    http://forums.pentaho.org/showpost.p...26&postcount=4
    Not sure if this helps you now, but depending on your SQL dialect, a NULL (meaning nothing) is treated differently to a "NULL" string.
    In Oracle your predicate could be:
    WHERE (${media} IS NULL OR media=${media})
    Oracle applies "lazy" logic, so if your Media parameter is not completed by the user (meaning it "IS NULL"), the condition will not even check the "media=${media}" part.

    Also, regarding your solution by adding an "All media" option to the now mandatory parameter. If you use:
    Select 'All Media' as Media from MediaTable
    UNION
    Select Media from MediaTable
    be aware that you are applying an internal "sort" on the query. Use "UNION ALL" instead for that millionth of a second performance improvement . This obviously has more impact that greater the data set is.

    HTH,
    Guy.

  11. #11
    Join Date
    Aug 2008
    Posts
    563

    Default

    Hi,
    Many thanks for your reply. Yes, "Union All" would improve the query a bit. I'll try to implement this. I am working with MySql, but good to understand that Oracle handles this differently! I have updated my documentation, which you can find here: http://diethardsteiner.blogspot.com/...parameter.html
    Best regards,
    Diddy

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.