OraFAQ Forum: Reports & Discoverer » try to put two report into one

Home » Developer & Programmer » Reports & Discoverer » try to put two report into one

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

try to put two report into one [message #204670]

Tue, 21 November 2006 10:55

Akshar
Messages: 116
Registered: May 2006

Senior Member

Hello Everyone,

I have two separate reports running on two separate query only different with table name.

Now i have tried to put two report in one but i am facing problem
with replacing table name in the Report Data Model Query.

---Query For report one.

select reportgroupclassid AS reportgroupid, name
from  &tblreportgroupclass_d   <--------------------'&'---- This is not allowing in Report Data Model Query  
     -------------------------
where  reportgroupclassid = :p_reportgroupid

---Query For report two


select a.reportgroupid, a.name
from &tblreportgroup a         <--------------------'&'----This is not allowing in Report Data Model Query 
     -----------------
where a.reportgroupid = :p_reportgroupid

---After Parameter form function


  if :report_type = 'A'  THEN

  :tablename  := '   tblreportgroupclass_d ';
  
  else
  
  :tablename  := ' tblreportgroup a ';
  
  end if;

Is there any other way to work around for this ?

Report message to a moderator

Re: try to put two report into one [message #204936 is a reply to message #204670]

Wed, 22 November 2006 12:59

Akshar
Messages: 116
Registered: May 2006

Senior Member

Is there anyone out there to help to provide solution for the above ?

Report message to a moderator

Re: try to put two report into one [message #204942 is a reply to message #204936]

Wed, 22 November 2006 13:58

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

In Data Model create two queries, both fully qualified with their tables - do not substitute table names with a variable. Let them look like this:

-- QUERY 1
select empno, ename, job from emp
&where_1

-- QUERY 2
select deptno, dname from dept
&where_2

This will automatically create 2 user parameters: WHERE_1 and WHERE_2.

Now create third user parameter, let's call it P_1_OR_2. It will accept numeric value (restrict it to 1 or 2) which will decide whether to run 'query 1' or 'query 2'.

Finally, create an 'after parameter form' trigger:

function AfterPForm return boolean is
begin
  if :p_1_or_2 = 1
  then
    :where_1 := 'where 1 = 1';
    :where_2 := 'where 1 = 2';
  elsif :p_1_or_2 = 2
  then
    :where_1 := 'where 1 = 2';
    :where_2 := 'where 1 = 1';
  end if;  
  return (TRUE);
end;

This trigger, depending on parameter 'p_1_or_2' value, sets WHERE clauses to be either TRUE (1 = 1) or FALSE (1 = 2).

Draw a Page Layout and run the report. Tweak it a little bit more and create Format triggers (not to display anything that shouldn't be displayed - column labels etc. that belong to query which returns nothing (because 1 = 2)).

Now, this is just an idea (which, actually, works). See if you can adjust it to your needs - perhaps even to set table name as your first attempt was.

Report message to a moderator