Hi,
I have created interactive report which is based on parameter, I need a total to be displayed at the bottom of each column.
Please suggest.
Thanks.
❇️ OrclQA.Com is a question and answer forum for programmers.
❇️ Here anyone can ask questions and anyone can answer to help others.
❇️ It hardly takes a minute to sign up and it is 100% FREE.
Lost your password? Please enter your email address. You will receive a link and will create a new password via email.
OrclQA.Com is a question and answer forum for programmers.
Here anyone can ask questions and anyone can answer to help others.
It hardly takes a minute to sign up and it is 100% FREE.
apex4ebs
I have used a union in the sql of the report. With the union SQL being the totals
afzal
STEP -1
CREATE REPORT LIKE--
select
SAL AS "SALARY",
NVL(COMM,0) AS "COMMISION",
DEPTNO
from EBA_DEMO_CHART_EMP
NOTE--STATIC ID --- emp
step-2
column-- SALARY
HTML Expression-- <span style="display:block; min-width:110px">#SALARY#</span>
static id -- SAL
column-- COMMISION
HTML Expression-- <span style="display:block; min-width:110px">#COMMISION#</span>
static id-- COMM
column-- DEPTNO
HTML Expression-- <span style="display:block; min-width:110px">#DEPTNO#</span>
static id-- DEPTNO
step-3 create ajax callback process--
name--AGG_PRO
pl/sql --
DECLARE
STR VARCHAR2(3000) :='';
BEGIN
APEX_JSON.OPEN_OBJECT;
SELECT JSON_OBJECT(
'SAL' VALUE SUM(SAL),
'COMM' VALUE SUM(COMM),
'DEPTNO' VALUE SUM(DEPTNO),
'DUMMY' VALUE TO_CHAR(SUM(1))
FORMAT JSON
) INTO STR
ROM EBA_DEMO_CHART_EMP;
apex_json.write('AGG', STR);
apex_json.close_object;
apex_json.close_all;
END;
step -4 create javascrip functin "add_agg_row" and keep this function code at "Function and Global Variable Declaration"
function add_agg_row(){
if( !$('#emp tbody tr:last').hasClass('gnr_agg')){
apex.server.process( "AGG_PRO", {
x01: "test",
}, {
success: function( data ) {
// if control break exist
if ($('#emp_control_panel').length > 0){
insert_agg_rec_control_break(data);
}
else{
insert_agg_rec(data);
}
},
error: function( jqXHR, textStatus, errorThrown ) {
}
} );
}
function insert_agg_rec(data){
var obj= JSON.parse(data.AGG);
var last_rec= $('#emp tbody tr:last');
var cell_html='';
var cell_txt='';
var agg_rec ='';
last_rec.children().each(function(index,element){
if($(this).text().length ==0){
$(this).text('^');
}
});
last_rec.children().each(function(index,element){
cell_html=element.outerHTML;
//console.log($(cell_html).text());
cell_txt=$(this).text();
if (typeof obj[$(this).attr('headers')] !=='undefined'){
cell_html=cell_html.replace('min-width:110px','$$$$');
cell_html=cell_html.replace(cell_txt,obj[$(this).attr('headers')]);
cell_html=cell_html.replace('$$$$','min-width:110px');
agg_rec=agg_rec+cell_html;
}else{
cell_html=cell_html.replace(cell_txt,'');
agg_rec=agg_rec+cell_html;
}
if($(this).text() =='^'){
$(this).text('');
}
});
$('#emp tbody tr:last').after('<tr class="gnr_agg">'+agg_rec+'</tr>'); //first
$('#emp .gnr_agg').children().each(function(){
if ($(this).text().length > 0){
$(this).css("background-color","yellow");
}
});
}
//console.log($('#emp_control_panel').length);
function insert_agg_rec_control_break(data){
var obj= JSON.parse(data.AGG);
var last_rec= $('#emp tbody tr:nth-child(3)');
var cell_html='';
var cell_txt='';
var agg_rec ='',
cell_headers='';;
last_rec.children().each(function(index,element){
cell_html=element.outerHTML;
cell_txt=$(this).text();
console.log($(this).attr('headers').split(' '));
cell_headers=($(this).attr('headers').split(' '));
if (typeof obj[cell_headers[0]] !=='undefined'){
cell_html=cell_html.replace(cell_headers[1],'');
cell_html=cell_html.replace('min-width:110px','$$$$');
cell_html=cell_html.replace(cell_txt,obj[(cell_headers[0]).trim()]);
cell_html=cell_html.replace('$$$$','min-width:110px');
agg_rec=agg_rec+cell_html;
}else{
cell_html=cell_html.replace(cell_txt,'');
agg_rec=agg_rec+cell_html;
}
});
$('#emp tbody tr:last').after('<tr class="gnr_agg">'+agg_rec+'</tr>'); //last
$('#emp .gnr_agg').children().each(function(){
if ($(this).text().length > 0){
$(this).css("background-color","yellow");
}
});
}
}
step -5 keep below defined code at "Function and Global Variable Declaration"
apex.jQuery( apex.gPageContext$ ).on( "apexreadyend", function( e ) {
add_agg_row();
} );
step -6 create a dynamic action
name- refresh report
event - after refresh
select type -region
region -- your report region
action- javascript code
add_agg_row();