Oracle APEX - Retrieve value from search and use it as a parameter in custom report

0
votes

I am creating custom reports on Oracle Application Express and I would like to know how I can retrieve the value in the search bar and use it as a parameter to generate reports.

Below is a screenshot for reference.

Screenshot from Oracle APEX

In the search bar I am selecting a need by date and I need my report to run based on that selected need by date. The need by date is a field in my SQL script and I have also defined a parameter.

select EBS_PO_IR_ITEM.ITEM_CODE as ITEM_CODE, 
EBS_PO_IR_ITEM.ITEM_DESCRIPTION as ITEM_DESCRIPTION,    
EBS_PO_IR_ITEM.UNIT_MEAS_LOOKUP_CODE as UNIT_MEAS_LOOKUP_CODE, 
EBS_PO_IR_ITEM.MARKED as MARKED, EBS_PO_IR_ITEM.REQUIRED_QUANTITY as 
REQUIRED_QUANTITY from EBS_PO_IR_ITEM EBS_PO_IR_ITEM where 
EBS_PO_IR_ITEM.MARKED = 'Y' and EBS_PO_IR_ITEM.NEED_BY_DATE = 
NVL(:P_NEED_BY_DATE, EBS_PO_IR_ITEM.NEED_BY_DATE)

I did some research and could find any information regarding the above. If you can give me some hints or provide me a link, I will be grateful.

1
oracle-apex
I am trying to simplify the APEX page and maybe there could be a simpler solution. I will still generate the report by clicking a button which a linked to a URL. I will then add an item (date picker) on the same page so that user can select the need by date. Now I need to understand how can I link the date selected to the button that generates the report. Else if there is a way to pass the value selected from the date picker to the URL. – oubinghose

1 Answers

0
votes

This sort of restriction in an IR happens automatically, you can check the debug log to see the SQL it builds.

If you want to know the query/variables used programatically, you can use APEX_IR.get_report() http://docs.oracle.com/cd/E59726_01/doc.50/e39149/apex_ir.htm#AEAPI29379

You should also to_date(:P1_DATE) any dates in page items, and to_number() any references to numerics, since the session state is stored in a varchar2 column.