Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Question: How to add a select all option to select component in CDE

  1. #1
    Join Date
    Sep 2014
    Posts
    3

    Default Question: How to add a select all option to select component in CDE

    Hi community,

    for a students projects we are working with Pentaho CDE to create a dashboard.
    At first it works fine, but now we are hanging at the point adding more than one Select Component.
    We inserted the Parameters of the Select Components to the SQL-Queries in the Where statement but now we have the problem, that it is not possible to select all elements in one select component and only one in the others. The sample space is getting smaller and smaller the more parameters we add because we can't find out how to select all option in one select component and send this all to the query.
    Is it possible in CDE to add an "select all elements option" to the select component? Or even to deactivate a select component and send this deactivation to the SQL-Query?

    We noticed that there is a multiple select component but we were not able to handle the custom parameter needed for it.
    It would be fine if it works with the select components.

    Thanks in advance for your support.

  2. #2
    Join Date
    Nov 2009
    Posts
    688

    Default

    When your select component is query driven. The query must be something like
    select 'All elements' as value
    union
    select distinct(value) as value from tableX

    Then in the query something like
    select * from tableX
    where value = (case when ${param} = 'All elements' then value else ${param} end)

  3. #3
    Join Date
    Sep 2014
    Posts
    3

    Default

    Hi johanhammink,

    thanks for your answer.
    We successfully added the All option to the select component.
    But we are still struggling with the query to select all data from the database.

    In your where statement:

    where value = (case when ${param} = 'All elements' then value else ${param} end)

    how can we concatenate all our values in your value variable? At the moment we are only able to select one of our elements when the 'All elements' value is picked in the select component.

  4. #4
    Join Date
    Nov 2009
    Posts
    688

    Default

    I thought that you wanted the all element in the where statement but you want it in the select part of the query. I don't think it's possible to use a parameter in the select part of a query. But you can use a variable
    Then it must be something like


    declare @Par as varchar(100)
    set @Par = ${param}

    if @Par = 'All elements'
    begin
    select * from TableX
    end
    else
    begin
    execute ('select' +@Par+ 'from TableX')
    end
    Last edited by johanhammink; 09-05-2014 at 03:49 AM.

  5. #5
    Join Date
    Sep 2014
    Posts
    3

    Default

    Hi johanhammink,

    thanks to your last post we have solved the problem.
    We had a mistake in the if-else-syntax.

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.