Hitachi Vantara Pentaho Community Forums
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Dynamic SQL query

  1. #1
    Join Date
    Jun 2015
    Posts
    16

    Question Dynamic SQL query

    Hi all !

    I have a CDE project in development and i wanted to display a chart wich depends on several parameters (like month, year, precise date, country, etc). I managed to put checkboxes (with an "all" parameter to check/uncheck all which works) but when i want to "add" another parameter to my query, it doesn't work anymore... So i'm sure i'm doing something wrong but what ? Please take a look for the parameter month for example :

    Select_months_query : (this is for my checkbox)
    SELECT
    "All" AS MONTH(TransactionDate)
    UNION
    SELECT DISTINCT MONTH(TransactionDate) FROM order ORDER BY MONTH(TransactionDate);

    Select_barchart_query : (this is for my chart, don't mind the other tables)
    SELECT pginit.Family, SUM(order.AmountEUR) AS SALES
    FROM pginit INNER JOIN statg ON pginit.PG = statg.PGInit INNER JOIN order ON statg.StatGroup = order.StatGroup
    WHERE (MONTH(order.TransactionDate) IN (${month}) OR "All" IN (${month}) OR ${month} IS NULL) AND
    /*/* Apply the same pattern for another parameter (like year for example) *\*\
    GROUP BY pginit.Family
    ORDER BY SALES;

    Any ideas on how to do it ?
    I read something here that said to use CASE clauses... But how ?
    http://forums.pentaho.com/showthread...hlight=dynamic

    Thank you for your help !

  2. #2

    Default

    Do you have all of your parameters listed bij de listeners component of the query?

  3. #3
    Join Date
    Jun 2015
    Posts
    16

    Default

    Yes i have listed them in the barchart component and i pass those parameters to my query
    But no success... Thaks for the answer though !

  4. #4

    Default

    I have the same thing in my dashboards, but i arranged it otherwise.
    I have a checkbox which fills parameter "Everything" with a 0 for unchecked and a 1 for checked.
    Then i have two select components which lists the months. The parameters for these are "FromMonth" and "ToMonth". This are the queries(i try to use your tables):

    For the select components:
    SELECT DISTINCT MONTH(TransactionDate) FROM order ORDER BY MONTH(TransactionDate);

    For the chart:
    SELECT whatever
    FROM wherever
    WHERE case when ${everything} = 1 then MONTH(TransactionDate) BETWEEN 1 and 12 else MONTH(TransactionDate) BETWEEN ${FromMonth} AND ${ToMonth} end
    AND /* Now you can add several parameters */

    Succes!

  5. #5
    Join Date
    Jun 2015
    Posts
    16

    Default

    Oh thank you a lot ! That guides me so much !
    Just a thing, so you put in a Javascript the parameter "Everything" to the value you want in your checkbox component ? Or you did something else ?

    And to finally be sure that i got it right, i think i'll adapt your nice query to :
    SELECT whatever
    FROM whatever
    WHERE case when ${everything} = 1 then MONTH(TransactionDate) BETWEEN 1 and 12 else MONTH(TransactionDate) in (${month}) end
    AND etc

    (i didn't know the between keyword, thanks !)
    I have to give the user the ability to choose some months, and it can be whatever he wants (and the client wants it in checkboxes...) so can i keep the $month parameter ?
    Anyway, you're awesome, that helps a lot, thanks again !

  6. #6

    Default

    For my idea to get it right:
    You have given your client 12 checkboxes for every seperate month? So they will be able to choose for example Jan, Mar, Aug?

  7. #7
    Join Date
    Jun 2015
    Posts
    16

    Default

    Yes that's exactly it ( i would have prefered to make a selection the way you wanted too ^^)

  8. #8

    Default

    Okay,

    Then you will have to create 12 parameters: Month1, Month2, Month3 .......
    Fill your parameters again with either 1 or 0 for checked or unchecked. (How do you read now if the checkbox is checked or not? Plz give me this intel)

    Your query will be a bit different.
    Code:
    SELECT ....
    FROM ....
    WHERE MONTH(TransactionDate) IN (Case when ${Month1} = 1 then 1 else null end,
                                                       Case when ${Month2} = 1 then 2 else null end,
                                                       Case when ${Month3} = 1 then 3 else null end /*This goes on till 12*/
    For your all component we write a little piece of javascript in de post fetch

    Code:
    function (){
    var all = Dashboards.getParameterValue("Everything");
    var yes = 1;
    if (all = 1){
    Dashboards.Firechange("Month1", yes);
    Dashboards.Firechange("Month2", yes);
    Dashboards.Firechange("Month3", yes);
    ....
    ....
    ....
    ...
    ...(Fill till 12)
    Dashboards.initEngine();
    
    }
    This fills all your parameters with 1. In your query all the months will be calculated.

  9. #9
    Join Date
    Jun 2015
    Posts
    16

    Default

    Yeah it really complicates the thing ^^
    About the checkboxes, i figured the parameter passed in the checkbox component fills itself when checked or not.
    For example, if you keep in mind that my months comes from a date (so they are in numbers), if Jan, Apr, Dec are checked then the parameter will be a string array : "1,4,12"
    Knowing that, you can check in your javascript (post-change for example) if a part of the string is present :

    if (parameter.indexOf("12") > -1) {
    //Then i detected 12 in my parameter so Dec is checked
    } else {
    //There's no "12" in my string so it's not checked
    }

    When nothing is checked the string is empty or null (i didn't make the test)

    Anyway, thank you very much with your help, you give me another angle on my problem and that helps a lot
    Hoping that the checkbox part helped you !

  10. #10

    Default

    Of course you have your own style,

    I like it but in your way you have to add a piece of javascript code for every month end maybe even for every checkbox you want to add.
    In my way you also have to add code for every parameter, but then it is in one source (The query). When you want to make some moderations
    this way is easier.

    Let me know if you fixed it

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.