Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Parameters, TSQL and Formula Operators

  1. #1
    Join Date
    May 2017
    Posts
    20

    Default Parameters, TSQL and Formula Operators

    Hey all, this is what I am trying to do:

    Select <stuff>
    from blah
    where <stuff qualifies>
    having (${mathvalue_param})
    order by <stuff>

    ${mathvalue_param} values
    (sum(columnName) >= 500)
    (sum(columnName) < 500)

    BUT I cannot get the parameter to actually work where I have it in the SQL query. Everything works except the actual execution of the query when I click 'View Report'.

    I need the report to swap on the users choice of 500 or less or over 500
    1) Am I approaching this right\wrong?
    2) Is there another way that I am not thinking of?
    3) Any suggestions would be appreciated.

    Thanks!
    Iowabeef

  2. #2
    Join Date
    May 2017
    Posts
    4

    Default

    Hi,

    1) Am I approaching this right\wrong?
    You need to group the data ColumnName before you use aggregate functions into conditions on clause having. So Use 'GROUP BY ColumnName'
    2) Is there another way that I am not thinking of?
    Post that in reports you need to edit the source as per the conditional result stream.

    3) Any suggestions would be appreciated.
    This process is been weird. Instead, highlighting the user choices of <=500 & >500 to two diff colors would be easy.

  3. #3
    Join Date
    Aug 2016
    Posts
    142

    Default

    I posted this in another recent thread but the same idea will work here also

    Quote Originally Posted by Deanicus View Post
    You can't use parameters directly to edit the query structure as it is basically sql injection.

    You can however use query scripting to do so.

    Open your query and select the query scripting tab. Select Groovy as the language

    Something like this should then do it, assuming your parameter is set to string type and has a default value set. Paste the below in

    def computeQuery (query, queryName, dataRow)
    {
    query = query.replaceAll("schema_filter",dataRow.get("schema"))
    return query;
    }

    "schema" in the above is the name of your parameter. Then the part of your query that you want parameterised should look like this:

    schema_filter.user.table

    However there are a few differences in the script needed to get this to work

    1. Create a multi value parameter called "Report_Type" or something like that with 2 options, Greater/Lesser (or whatever you want to call them).
    2. Then using the same process I quoted above enter this script instead:

    Code:
    def computeQuery (query, queryName, dataRow)
    {
    if (dataRow.get("Report_Type")=="Greater"){query = query.replaceAll("Promo_Sign",">=")}
     
                    else
                    {
                    query = query.replaceAll("Promo_Sign","<")
                    }
    return query;
    }
    3. Then in your SQL use the full HAVING clause (rather than parameter you have now) so it should look something like this:

    HAVING SUM(columnName)Promo_Sign 500

  4. #4
    Join Date
    May 2017
    Posts
    20

    Default

    Heya,

    Yes, I forgot the Group By line, so here:

    Select <stuff>
    from blah
    where <stuff qualifies>
    group by <stuff in select>
    having (${mathvalue_param})

    order by <stuff>

    However I am displaying my returned information in a graph, that contains way too many entries, that are way too different. I would like to group the large users into a seperate graphed report than the smaller companies... be able to display in groups of 1000, vs groups of 100...

    however all of that is moot, and I can do that (mostly in my experimentation) unless I can get a parameter to register\accept ">=" and "<" as my arguments.

    Thanks in advance


  5. #5
    Join Date
    Aug 2016
    Posts
    142

    Default

    I submitted a solution for you but I got a "reply must be approved by moderator" message when I tried to submit it. Hopefully it will show up in a few minutes

  6. #6
    Join Date
    May 2017
    Posts
    20

    Default

    Quote Originally Posted by Deanicus View Post
    I submitted a solution for you but I got a "reply must be approved by moderator" message when I tried to submit it. Hopefully it will show up in a few minutes

    Thanks Deanicus, I'll go refill my coffee and hope for the best.

  7. #7
    Join Date
    May 2017
    Posts
    20

    Default

    This worked perfectly, Thanks for your time and for being correct Deanicus!


    Quote Originally Posted by Deanicus View Post
    I posted this in another recent thread but the same idea will work here also




    However there are a few differences in the script needed to get this to work

    1. Create a multi value parameter called "Report_Type" or something like that with 2 options, Greater/Lesser (or whatever you want to call them).
    2. Then using the same process I quoted above enter this script instead:

    Code:
    def computeQuery (query, queryName, dataRow)
    {
    if (dataRow.get("Report_Type")=="Greater"){query = query.replaceAll("Promo_Sign",">=")}
     
                    else
                    {
                    query = query.replaceAll("Promo_Sign","<")
                    }
    return query;
    }
    3. Then in your SQL use the full HAVING clause (rather than parameter you have now) so it should look something like this:

    HAVING SUM(columnName)Promo_Sign 500

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.