Next and Prev from an Interactive Report 2

I have found a bit of time to re-look at how to retrieve the sql being generated in an Interactive Report with a view to using it to provide a Next and Previous button in an interactive report. From my previous work, Stew Stryker rightly pointed out that I didn't cater for global searches or saved interactive reports. I have incorporated his suggestions into the solution and I think it's just about cracked. If you notice any problems, please let me know.

Demo

How to do it

1. Create the following function and procedure:

create or replace FUNCTION split_columns
(
p_cond_expr VARCHAR2,
p_column VARCHAR2,
p_date_cols VARCHAR2)
RETURN VARCHAR2
IS
l_sql VARCHAR2(4000);
l_vc_arr2 apex_application_global.vc_arr2;
l_vc_arr1 apex_application_global.vc_arr2;
l_date_y VARCHAR2(1);
BEGIN
l_vc_arr2 := apex_util.string_to_table(p_column);
l_vc_arr1 := apex_util.string_to_table(p_date_cols, ',');
FOR z IN 1 .. l_vc_arr2.COUNT
LOOP
l_date_y := NULL;
FOR Y IN 1 .. l_vc_arr1.COUNT
LOOP
IF l_vc_arr2(z) = l_vc_arr1(Y) THEN
l_date_y := 'Y';
END IF;
END LOOP;
IF l_date_y is null THEN
l_sql := l_sql || 'OR INSTR(UPPER("' || l_vc_arr2(z) || '"),UPPER(''' || p_cond_expr ||
'''))>0 ';
END IF;
END LOOP;
l_sql := 'WHERE (' || ltrim(l_sql, 'OR')||')';
RETURN(l_sql);
END;
/
create or replace PROCEDURE next_prev_values
(
p_column_id IN VARCHAR2,
p_value IN NUMBER,-- The id value of the selected record
p_page_id IN NUMBER,-- Page number of the interactive report
p_report_id IN NUMBER,
p_bvar1 IN VARCHAR2,-- Bind variable value1
p_bvar2 IN VARCHAR2,-- Bind variable value2
p_bvar3 IN VARCHAR2,-- Bind variable value3
p_bvar4 IN VARCHAR2,-- Bind variable value4
p_next OUT NUMBER,
p_prev OUT NUMBER,
p_top OUT NUMBER,
p_bot OUT NUMBER,
p_cur_tot OUT VARCHAR2,
p_debug OUT VARCHAR2
-- Returns the report query
)
AS
lv_sql VARCHAR2(32767);
lv_predicate VARCHAR2(4000);
lv_sql_sort VARCHAR2(4000);
lv_comb VARCHAR2(32767);
lv_condtion_type VARCHAR2(30);
lv_search VARCHAR2(4000);
BEGIN
/*set the deliminator to be used in the string_agg funtion*/
string_agg_control.g_delim := ' and ';
/*Return the report query and any addition sort and filters from the Interactive Report*/
SELECT
z.sql_query,
nvl2(a.predicate, ' where '
|| a.predicate, NULL) sql_predicate,
nvl2(x.sort_column_1, x.sort_column_1
|| ' '
|| x.sort_direction_1
|| nvl2(x.sort_column_2, ', '
|| x.sort_column_2
|| ' '
|| x.sort_direction_2, NULL)
|| nvl2(x.sort_column_3, ', '
|| x.sort_column_2
|| ' '
|| x.sort_direction_3, NULL)
|| nvl2(x.sort_column_4, ', '
|| x.sort_column_2
|| ' '
|| x.sort_direction_4, NULL)
|| nvl2(x.sort_column_5, ', '
|| x.sort_column_2
|| ' '
|| x.sort_direction_5, NULL)
|| nvl2(x.sort_column_6, ', '
|| x.sort_column_2
|| ' '
|| x.sort_direction_6, NULL)||', rownum ', ' rownum ') sql_sort,
nvl2(a.search, split_columns(a.search, x.report_columns, b.date_cols), NULL) search
INTO
lv_sql,
lv_predicate,
lv_sql_sort,
lv_search
FROM
/*This inline view aggregates any filters added to the Interactive Report*/
(
SELECT
report_id,
string_agg( DECODE(condition_type, 'Filter', REPLACE(REPLACE(y.condition_sql,
'#APXWS_EXPR2#', ''''
|| condition_expression2
|| ''''), '#APXWS_EXPR#', DECODE(SUBSTR(condition_operator, 1, 9), 'is in the',
condition_expression, ''''
|| condition_expression
|| '''')))) predicate,
MAX(DECODE(condition_type, 'Search',y.condition_expression)) search
FROM
apex_application_page_ir_cond y
WHERE
y.condition_type IN ('Filter', 'Search')
AND y.condition_enabled = 'Yes'
GROUP BY
report_id) a,
/*Returns any date columns*/
(
SELECT
interactive_report_id,
STRING_AGG(COLUMN_ALIAS) date_cols
FROM
APEX_APPLICATION_PAGE_IR_COL
WHERE
column_type = 'DATE'
GROUP BY
interactive_report_id) b,
/* The following view contains the report original report query*/
apex_application_page_ir z,
/*The following view provides any column sorting*/
apex_application_page_ir_rpt x
WHERE
x.report_id = a.report_id(+)
AND x.INTERACTIVE_REPORT_ID = b.INTERACTIVE_REPORT_ID
AND x.interactive_report_id = z.interactive_report_id(+)
AND x.session_id = nv('SESSION')
AND x.application_id = nv('APP_ID')
AND x.base_report_id = p_report_id
AND x.page_id = p_page_id;
/* Build the query used in the Interactive Report */
lv_comb := 'select prev, next, top, bot, cur||'' of '' ||tot from (
select
' ||p_column_id || '
,lag (' || p_column_id || ') over (order by ' || lv_sql_sort ||') prev
,lead (' || p_column_id || ') over (order by ' || lv_sql_sort ||') next
,first_value (' || p_column_id || ') over (order by ' || lv_sql_sort ||') top
,last_value (' || p_column_id || ') over (order by ' || lv_sql_sort ||
' RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) bot
,count (' || p_column_id ||') over (order by ' || lv_sql_sort || ') cur
,count (' || p_column_id ||') over () tot
from (select * from (' || lv_sql || ')'||lv_search||')' ||
lv_predicate || ') where ' || p_column_id || ' = ' || p_value;
IF p_bvar4 IS NOT NULL THEN
EXECUTE IMMEDIATE lv_comb INTO p_prev,
p_next,
p_top,
p_bot,
p_cur_tot USING p_bvar1,
p_bvar2,
p_bvar3,
p_bvar4;
ELSIF p_bvar3 IS NOT NULL THEN
EXECUTE IMMEDIATE lv_comb INTO p_prev,
p_next,
p_top,
p_bot,
p_cur_tot USING p_bvar1,
p_bvar2,
p_bvar3;
ELSIF p_bvar2 IS NOT NULL THEN
EXECUTE IMMEDIATE lv_comb INTO p_prev,
p_next,
p_top,
p_bot,
p_cur_tot USING p_bvar1,
p_bvar2;
ELSIF p_bvar1 IS NOT NULL THEN
EXECUTE IMMEDIATE lv_comb INTO p_prev,
p_next,
p_top,
p_bot,
p_cur_tot USING p_bvar1;
ELSE
EXECUTE IMMEDIATE lv_comb INTO p_prev,
p_next,
p_top,
p_bot,
p_cur_tot;
END IF;
p_debug := lv_comb;
END next_prev_values;
/

2. Add this Javascript function into you JS file or between script tags in the page HTML Header

function goto(url) {
var newUrl = url.replace("$report_id$", $v('apexir_REPORT_ID'));
location.href = newUrl;}


3. Use this link for the IR column link

javascript:goto('f?p=&APP_ID.:17:&SESSION.::&DEBUG.:17:P17_EMPNO,P17_IR_REPORT_ID:#EMPNO#,$report_id$');

4. Create an 'On Load before Header' process as follows:

next_prev_values(
p_column_id => 'EMPNO'
,p_value => :P17_EMPNO
,p_page_id => 16
,p_report_id => :P17_IR_REPORT_ID
,p_next => :P17_NEXT
,p_prev => :P17_PREVIOUS
,p_debug => :P17_DEBUG
,p_top => :P17_FIRST
,p_bot => :P17_LAST
,p_cur_tot => :P17_CURRENT
,p_bvar1 => :P16_JOB
,p_bvar2 => NULL
,p_bvar3 => NULL
,p_bvar4 => NULL
);


*Where p_bvar are items that are used in the main report sql.

5. Create items that correspond to the process created above. The Next, Prev, First & Last are buttons; the rest are hidden items. The debug will contain the sql being generated and is useful for debugging.

6. The above requires the StringAgg function to be installed. If you don't already have it, then download it from here and install.

Comments

Stew said…
Shunt,

This looks pretty cool. I don't know why I didn't find this post sooner!

I look forward to getting some time to implement this in one of my applications in the near future

BTW, thanks for the shout-out, though my last name is spelled "Stryker".

Good luck!
Rich said…
Hi Simon - Thanks for this helpful post.

I'm having a bit of trouble getting it to work in my application. The IR report ID that gets passed to the page the report links to appears to be incorrect - when I search for it in the APEX_APPLICATION_PAGE_IR_RPT view, it's not there.

Would it be possible for me to (please!) take a look at the source of the demo app you posted on apex.oracle.com?

Thanks for your consideration.

Regards, Rich
shunt said…
No problem, just send an email to simon_hunt@me.com with your details and I will give you access. Simon