Forum Stats

  • 3,769,695 Users
  • 2,253,009 Discussions
  • 7,875,155 Comments

Discussions

How to use page items in report queries

User_6L3S5
User_6L3S5 Member Posts: 13 Green Ribbon
edited Oct 19, 2021 11:58AM in APEX Discussions

Dear Community,

i'm using the shared component "report query" to export data from my application. The sql-query looks sth like this:

select * from my_table where year >= :P1_START_YEAR AND year <= :P1_END_YEAR

The problem is that my page items aren't working in report query. If I'm using a static number instead if my page items the query is working and i get a correct export. Apex is suggesting to use bind variables but i couldn't figure out a way to use them.

Thanks for the help!

Answers

  • Scott Wesley
    Scott Wesley Member Posts: 6,127 Gold Crown

    If you're comparing item values that store dates, you should explicitly convert them, eg:

    select * from my_table where year >= to_date(:P1_START_YEAR) AND year <= to_date(:P1_END_YEAR)
    

    This is because session state values are stored & bound as strings.

    Possibly try this before looking into how/why you're using 'report query' to export data.

  • User_6L3S5
    User_6L3S5 Member Posts: 13 Green Ribbon

    @Scott Wesley

    Thanks, but unfortunatly this isn't the solution. Because "year" is stored in my database as a 4-digit number (f.e. 2019) there is no need to convert my items to date. When I'm using the exact same query in my sqldeveloper and instead of my items (:P_YEAR etc.) use a number like 2017 and 2018, the query is working perfectly. Therefore I think the problem has something to do with "report queries" not getting the values i typed in my page items into the query.

  • Mike Kutz
    Mike Kutz Member Posts: 5,816 Silver Crown

    You have to "save the values to the server".

    IIRC - there is a section for your report query for the WHERE section. One of the values you fill in will be the name(s) of the page items you need to run the query properly.

    { reserved for screenshot}

    Too early; no coffee. I could be wrong