problem with Parameters in Data Set SQL

sixsigmasixsigma Junior Member
edited December 31, 1969 in Designing Analytics Reports
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')<br>
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')<br>

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

  • VirgilVirgil Administrator EM admin
    edited December 31, 1969
    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.

    Virgil Dodson
    OpenText Analytics Developer Community
    https://forums.opentext.com/forums/categories/analytics

  • sixsigmasixsigma Junior Member
    edited December 31, 1969
    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.
  • VirgilVirgil Administrator EM admin
    edited December 31, 1969
    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.

    Virgil Dodson
    OpenText Analytics Developer Community
    https://forums.opentext.com/forums/categories/analytics

Sign In or Register to comment.