How to pass dynamic where clause in IR report

Question

shashi

View Profile

0

shashiProfessional

Asked: March 23, 20232023-03-23T14:08:20+05:30 2023-03-23T14:08:20+05:30

How to pass dynamic where clause in IR report

0

How to pass dynamic where clause to IR report from application item.Can you please suggest.

Example

select col1,colo2
from table
where col1 in(:P1_ITEM)

P1_ITEM contains in clause select query

2 Answers

You must login to add an answer.

OrclQA Wiki · Answer 1 · 2023-03-23T15:17:28+05:30

To pass a dynamic WHERE clause in an Interactive Report, you can use the following steps:

Create a hidden page item to store the dynamic WHERE clause:
a. Navigate to the Page Designer in APEX. b. Under "Items," click "Create" (the "+" icon) and choose "Hidden." c. Set the "Name" property for the hidden item, for example, "P1_DYNAMIC_WHERE." d. Save the changes.
Create or edit an existing Interactive Report region:
a. Navigate to the Page Designer in APEX. b. Under "Regions," click "Create" (the "+" icon) and choose "Interactive Report." c. Set the "Title" property for the Interactive Report region. d. In the "Source" section, set the "Type" property to "SQL Query." e. Write your SQL query, using the hidden page item to represent the dynamic WHERE clause. For example:

SELECT column1, column2, column3
FROM your_table
WHERE 1=1
AND &P1_DYNAMIC_WHERE.

Set the dynamic WHERE clause value:

You can set the value of the hidden page item (P1_DYNAMIC_WHERE) using a Dynamic Action, PL/SQL code, or JavaScript. Here's an example of setting the value using JavaScript:

// Define your dynamic WHERE clause.
var dynamicWhere = "column1 = 'value1' AND column2 = 'value2'";

// Set the value of the hidden page item.
apex.item('P1_DYNAMIC_WHERE').setValue(dynamicWhere);

// Refresh the Interactive Report to apply the dynamic WHERE clause.
apex.region('your_interactive_report_static_id').refresh();

Replace "your_interactive_report_static_id" with the actual Static ID of your Interactive Report region.

This should allow you to pass a dynamic WHERE clause in your Interactive Report in Oracle APEX.