How to use page items in report queries

User_6L3S5

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!

Scott Wesley

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

@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

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