problem with Parameters in Data Set SQL

sixsigma
edited February 11, 2022 in Analytics #1
Am trying set up a Data Set from a Oracle db and the Parameter is not being used.
Looks like the parser is not seeing the ? inside the to_date() function.

eg. this works in BIRT (no parameter)

SELECT *
FROM CALL
WHERE TS_INIT >= to_date('2007-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND TS_INIT <= to_date('2007-12-01 00:01:00', 'YYYY-MM-DD HH24:MI:SS')

But this does not (with the parameter set)

SELECT *
FROM CALL
WHERE TS_INIT >= to_date( ?, 'YYYY-MM-DD HH24:MI:SS')
AND TS_INIT <= to_date('2007-12-01 00:01:00', 'YYYY-MM-DD HH24:MI:SS')


Am I just missing something or have I found a bug.
Also have other data sets using parameters but to a mysql db and they work fine.

Comments

  • Virgil Dodson
    Virgil Dodson E admin
    edited December 31, 1969 #2
    Hi sixsigma,

    I tried the same thing and saw that the parameter was not getting automatically recognized on the parameter tab. However, I was still able to press the [New...] button on the parameter tab in the Data Set and assign the default value and the question mark was correctly replaced with the default value when I previewed the data. I set it up like the screenshot enclosed shows.
    Warning No formatter is installed for the format ipb
  • sixsigma
    edited December 31, 1969 #3
    Thanks vdodson,

    You found something that I missed. Though its not must help as I do really do need to pass in a parameter, not just use a default one. In the report that I am trying to build, I need to pass in 3 parameters. Bit of a problem if I cant get even one to work.

    Are well, guess I will have to raise it as a bug after all.
    Thanks again for the help, its good to know I was not doing something stupid.
  • Virgil Dodson
    Virgil Dodson E admin
    edited December 31, 1969 #4
    This approach should still work even for multiple parameters... and not just for the default value. If you are passing these parameters in, first create your parameters, then when setting up the datasource, select the report parameter from the bottom drop-down (see the screenshot). This binds the report parameter to the parameter needed to fill the query.
    Warning No formatter is installed for the format ipb