Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Setting up an "All" Parameter in pentaho Report PRD (prpt)

  1. #1

    Thumbs up Setting up an "All" Parameter in pentaho Report PRD (prpt)

    1) create filter query like this
    example

    SELECT
    "All" AS region_name
    UNION
    select region_name
    from region;

    now on report query
    select
    ..
    ..
    from
    ..

    where Facility_LOC = ${location} OR "All " = ${location}



    contact me in case of any query
    spectrumon@gmail.com

  2. #2
    Join Date
    Sep 2012
    Posts
    1

    Default

    wahoo..3thx!

    SELECT
    -1 as id,"所有渠道" as name
    UNION
    select id,name
    from channel WHERE parent in (1002,1003,1004,1007,700);

  3. #3
    Join Date
    Jan 2007
    Posts
    485

    Default Problem setting an "All" Parameter in Filter Query

    Hi,

    I am trying to do something similar, but can't get it to work.

    Enclosed test report. The parameter is nor being accepted.

    The data of the dim_lines_of_business table is:

    ramo_detalle_ord, ramo_detalle
    1, 'Property & Casualty'
    2, 'Motor Vehicles'
    3, 'MMVAI'
    4, 'Surety & Credit'
    5, 'Accidents & Health'
    6, 'Life'

    Sorry for not using a Steel Whees / Sample Data....

    Could you help me out? I have tried a number of alternatives and none seem to work...

    Many thanks and kind regards.
    Attached Files Attached Files

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

    Default

    @abhishekshankar:

    Have a look at the attached report. It does exactly what you describe, following the steps you describe. So you may have some errors in your query that prevents it from working. But your principle is sound.

    The example report here does not use a union to produce the parameter dataset, but only because HyperSonic is horrible and does not easily allow static queries. It always insists on having a table and I could not be bothered to work around that.

    @DMurray: The sample report works for you too. Oh, and looking at you parameter query: Wouldn't it be smarter to use a auto-value that is safely out of range of your possible keys? You explicitly exclude '9' from the parameter set. If you use MySQL, then keys start at 0 and go upwards, so -1 would be safe value for your auto-indicator. Much better than 9


    Also note: This works ONLY for single-selection parameter. If you have multi-selection parameter, you have to derive a boolean flag for your parameter (via a hidden parameter) to use in your query. Whenever a multi-select parameter is used in a query, we do a parameter expansion that is suitable for an IN(..) clause. So each value in the parameter is listed as parameter list. For a two-value selection, this would rewrite the where clause in the original post to

    Code:
    .. where Facility_LOC = ?,? OR "All " = ?,?
    which is not a valid SQL statement any more.
    Attached Files Attached Files
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

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

    Default

    @DMurray: Btw: If you get a message claiming that the parameter is of an invalid value, then check what MySQL returns. It could be that the driver returns a "BigDecimal" instead of a "Integer".
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  6. #6
    Join Date
    Jan 2007
    Posts
    485

    Default

    Many Thanks Taqua.... your example did in fact solve my problem... Maybe you can find some time to provide or direct me where I could find an example of a multi-selection parameter.

    In appreciation, DMurray3

  7. #7

    Default

    Hi Taqua,

    I want to use remote database values as a parameter including "ALL". Table datasource is not the right option in my case. Could you help me how to configure my PRPT, so that I could use "ALL" option in the parameter values list.

    I followed the first post based on UNION, unfortunately when i select ALL it shows blank report. Need your assistance...

    Thanks,
    Abdur Rahmaan

Tags for this Thread

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.