i have a report .. it generates multiple transaction like debit and credit and generate opening balance i calculate opening balance in report builder formula but the query is too slow can u help me out to calculate opening balance in main query?? i have 2 date parameter _date_from and _date_to opening balance calculate main query + transaction_date < P_date_from in report builder formula column plzz help me


that is my query

function tryFormula return Number is
n number;
begin
  if :account_type in ('R','O','L') then
   select sum(nvl(ENTERED_CR,0)-nvl(ENTERED_dR,0)) into n
   from  GL_JE_HEADERS_V gh, GL_JE_LINES_V gli
  
  where gli.JE_HEADER_ID=gh.je_header_id
and :code_combination_id=gli.code_combination_id
and ( gh.DEFAULT_EFFECTIVE_DATE<=:p1 or :p1 is null);
  else
  
   select sum(nvl(ENTERED_dR,0)-nvl(ENTERED_cR,0)) into n
   from  GL_JE_HEADERS_V gh, GL_JE_LINES_V gli
  
  where gli.JE_HEADER_ID=gh.je_header_id
and :code_combination_id=gli.code_combination_id
and ( gh.DEFAULT_EFFECTIVE_DATE<=:p1 or :p1 is null);

  end if;
  return n;
end;

Try to get the balances from this view gl_balances_v it is much faster

Hi.

I believe you should use GL_BALANCES where all figures are already calculated.