Thursday, October 1, 2009

Setting up an "All" Parameter

Setting up an "All" Parameter

This short tutorial is fairly similar to the last one, let's say it's an improved version:
We work again with Pentaho Report Designer 3.5. Imagine you want to give your users the possibility to choose either one of the parameter values or all. So how can we implement this with a MySQL query? 

The approach is as follows:

Set up the query to for the parameter values like this:

SELECT
"All Countries" AS country_name
UNION
SELECT
country_name
FROM
table
;

We do the same for the channels query. Please keep in mind that UNION will remove any duplicates. If you are sure that you have no duplicates, you can use UNION ALL to improve the performance.

Set up a new parameter called "country_name", tick "mandatory", choose the above query as the source, set the type to string.

Now change the main query that feeds the report like this:

SELECT
[...]
WHERE
(cc.channel_name = ${channel_name} OR "All Channels" = ${channel_name}) AND
(country_name = ${country_name} OR "All Countries" = ${country_name}) AND
the_date >= ${start_date} AND
the_date <= ${end_date}
GROUP BY 1,2,3,4
;

Save everything and run the report ... you should see the all options now in your pull down menu:


As some of you might use Oracle as well, a user named "zulu" from the pentaho forum pointed out that:
"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.
"

Just a remark from my side: My original post included a query like this one "(media = ${media} OR 'All Media' = ${media})"
You can find the original post here.

Update 2012-05-30: Somebody asked me how to achieve the same with mulitselect parameters. Here is the approach that works for me (It's a different dataset, but you get the idea):

SELECT
     `city_sales`.`date`,
     `city_sales`.`continent`,
     `city_sales`.`country`,
     `city_sales`.`city`,
     `city_sales`.`revenue`
FROM
     `city_sales`
WHERE
(city IN (${CITY}) OR "All cities" IN (${CITY}))

Note: I defined the CITY parameter as type STRING.

29 comments:

  1. Thank you for sharing this. I wanted to write more in my blog but I have been too busy lately. I'm looking forward to read more from you!

    ReplyDelete
  2. This is really something that I always wanted to use in my reports. It is very good to know now how to integrate this functionality with the Report Designer!
    I really like the new Report Designer 3.5! It's a huge step forward!

    ReplyDelete
  3. Thank you for sharing this. But i have same requirement as you put on diagram.

    I want All city and All Data type (in my case ) for selection of user which one not available in my database.

    I want to add in selection parameter. I am trying your way and ALL City constant in not showing in parameter drop-down.

    I am using mysql database.

    Thanks
    Ajay
    ajaysrmcse@gmail.com

    ReplyDelete
  4. Hi Ajay,
    Can you post your SQL query here? Then I might be able to help you better.
    Best regads,
    Diethard

    ReplyDelete
  5. This trick is great but there also is a big pitfall with this approach. This works great when you have a flat dataset (no join). But when the column where the "All" trick is needed have to be fetch with a join, the query needs to join one table even when "All *" is selected. Repeat that 8 or 12 times, now you have a recipe for bad performance, especially that users only select 2 or 3 fields at a time.

    ReplyDelete
  6. It seems to not work anymore. If you use the same parameter name twice in the query, an error comes up saying that the parameter count is not corrent.

    ReplyDelete
  7. Which version of PRD are you using? If I find some time I can try to check it later on.

    ReplyDelete
  8. Thanks for the quick reply.

    3.80 GA

    ReplyDelete
  9. Hm, I am quite sure that it works in GA. Try to double check your settings and some debugging. Does the parameter work on its own (if you only one in your query) etc.

    ReplyDelete
  10. Thank you for posting this. This makes it a lot easy to implement "All" filter in Report Designer. Is there anything similar for Mondrian Schema/Analyzer Reporting?

    ReplyDelete
  11. Thanks for your feedback! Check out my post about parameters in PRD ... you will have to manipulate your MDX query therefore.

    ReplyDelete
    Replies
    1. What about if the parameter is "multi value" list? If the selection is on 2 or more values the WHERE clause: country_name IN ${country_name} does work?

      Delete
    2. Sounds like some nice challenge. Did you try it yourself? I came up with this approach which seems to work fine (not the same dataset, but you get the idea):
      SELECT
      `city_sales`.`date`,
      `city_sales`.`continent`,
      `city_sales`.`country`,
      `city_sales`.`city`,
      `city_sales`.`revenue`
      FROM
      `city_sales`
      WHERE
      (city IN (${CITY}) OR "All cities" IN (${CITY}))

      Delete
  12. Hello Diethard,
    great aproach! but what about when a parameter value completely change the query?
    For example, something with a year parameter and
    if year is less than 2000 then the query should be
    select sum(sales) from old_table where sale_year = ${year}
    else the query should be
    select sum(sales) from new_table where sale_year = ${year} and sales_class in (1,2,25)

    how can i do that? can you sugest me some documentation or article on the web? The closest article i founded in this way was yours and one of Michel Tarallo.
    Thanks in advance.

    ReplyDelete
    Replies
    1. You can use the custom data query therefore.

      Delete
  13. hi Diethard

    could you please post an example for oracle database..

    thanks

    ReplyDelete
    Replies
    1. Right now I do not have an Oracle DB available for testing. I would imagine that it just works on Oracle as well as mentioned above. Just try to run your query with the following in the where clause in Oracle sql developer (or command line): channel_name = "All Channels" OR "All Channels" = "All Channels". This is based on the example given above ... you can just adjust it to your needs. If this runs fine, you can paste it into your PRD query and replace "All Channels" with the parameter.

      Delete
  14. This comment has been removed by the author.

    ReplyDelete
  15. If am using MQL and a metadata datasource am I stuck or there is a way to simulate an "All" option?

    ReplyDelete
    Replies
    1. See if the approach mentioned at the end of this post http://forums.pentaho.com/showthread.php?79174-quot-select-all-quot-parameter-option-using-metadata works for you

      Delete
  16. Great post! I had a hard time trying to figure it out (no SQL-speaker here ;-( ). I have a completelly diferente question: let's say I show a country, but filter my query by a value. You know this is possible thanks to the parameters' options Value and Display Name. What if I want to see the parameters Display Name on the report, so I can print it and still know the filter values? If I add the parameter to the report I'll end up showing a value, like country_id. How would I print the country's name on the report?

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. Use the SINGLEVALUEQUERY formula ... excellent info about it can be found in this blog post: http://www.on-reporting.com/blog/using-queries-in-formulas-in-pentaho/

      Delete
  17. I have been unsuccessful in getting this to work. I'm using a db2 database and I have a list of staff members in my report and would like to have a non mandatory text box for users that want to search by name. The default layout should have all of the staff displayed that are within the date range selected in the date picker by the user. Right now my report renders blank results until a staff last name is typed in the text box rather than showing all names then filtering down to the specific staff name that is typed in. Any help is appreciated.

    ReplyDelete
    Replies
    1. If you run your main report query manually does it work? Try to run it exactly in the way you expect PRD to send it to the DB. If that works, then start debugging your params etc. It's difficult to give remote help on a report that I am not familiar with. Good luck!

      Delete
  18. Hi,
    I am trying to use ALL condition as parameter, to get the the all the rows related to parameter on master report.
    I am using HIve as connection with PRD and back end database is NOSQL cassandra with shark database.
    When I am trying to show data without ALL parameter it is showing.
    When I added ALL condition with parameter query than it is showing in drop-down box.
    When I trying to select particular database value from drop-down parameter than related value to data is showing on report. When I am selecting ALL condition from parameter than on report there is no value is showing.
    I used your above condition for mysql & postgresql it is working but in Nosql database condition, not working.
    I added parameter variable to master query as given above. Any idea ?

    ReplyDelete
    Replies
    1. I would just try to run the query all query hard coded in a standard SQL client (Squirrel etc) and see if Hive supports this syntax.

      Delete
  19. Hi, I have no idea why this just didnt work for me, the suggested way did run but gave me incorrect data, so i finally settled for this....

    SELECT
    .
    .
    .
    FROM
    table1
    WHERE

    case when ${country} = 'All Countries' then country IN (select
    distinct country
    from table2)
    else country in (${country}) end

    - country table stores the values i used in my parameter for multiple select
    - to get 'All Countries' i used your union example.

    Hope this helps someone, also do advice if this is in-efficient in any way.

    Cheers

    ReplyDelete
    Replies
    1. Thanks for sharing your solution! It's a viable options as well.

      Delete