Home » Developer & Programmer » Reports & Discoverer » Lexical Reference (Reports 6i)
Lexical Reference [message #443289] Sun, 14 February 2010 23:36 Go to next message
athar.fitfd@hotmail.com
Messages: 193
Registered: October 2007
Location: pakistan
Senior Member
Hi everyone,

i want to user lexical reference in formula column of my reports.


how is it possible.


Thanks


Athar
Re: Lexical Reference [message #443291 is a reply to message #443289] Mon, 15 February 2010 00:06 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I guess the same way you'd do that elsewhere. Did you try?
Re: Lexical Reference [message #443340 is a reply to message #443291] Mon, 15 February 2010 03:44 Go to previous messageGo to next message
athar.fitfd@hotmail.com
Messages: 193
Registered: October 2007
Location: pakistan
Senior Member
i tried that in the same way as in report query but ends in a syntax error.

i think we can't use it in formula column.

Athar
Re: Lexical Reference [message #443359 is a reply to message #443340] Mon, 15 February 2010 05:02 Go to previous message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I think you are right; at least, I tried it now and guess what - it doesn't work.

However, there *might* be a workaround - not very elegant, though, but - it works.

For example: I have created a report based on Scott's schema whose query looks like
select * from emp &lex_where
Then I created additional user parameter "p_deptno" and After Parameter Form trigger as
function AfterPForm return boolean is
begin
  if :p_deptno is null then
     :lex_where := 'where 1 = 1';
  else
     :lex_where := 'where deptno = ' || :p_deptno;
  end if;
  
  return (TRUE);
end;

A formula column (you are asking about; in my example, it counts number of fetched records) can't use "&lex_where"; therefore, I have created additional placeholder column called "CP_PLACE" (which will contain number of records), as well as formula column (which will calculate that number).

Formula column code: I have to simulate lexical parameter and put it into "p_where" variable; then I do the calculation using SRW built-in package, and put the result into the placeholder column. Finally, placeholder column is displayed in the paper layout.
function CF_cnt_emp_in_deptFormula return Number is
  p_where varchar2(200);
  retval number;
begin
  p_where := 'where deptno = ' || :p_deptno;
	
  srw.do_sql('select count(*) into :cp_place from emp ' || p_where);  
  
  return (0);
end;

It appears that it works just fine; I don't know whether it helps or not, but I don't know any other way.

Note that I have done that in Reports Developer 10g (as I don't have 6i). The report is attached to this message - if possible, open it and see how it looks like.
Previous Topic: Migrating reports10g from windows to linux.
Next Topic: Calling a Report from Report in Report10g in new window
Goto Forum:
  


Current Time: Tue Dec 06 12:25:42 CST 2016

Total time taken to generate the page: 0.07921 seconds