Home » Developer & Programmer » Reports & Discoverer » try to put two report into one
try to put two report into one [message #204670] Tue, 21 November 2006 10:55 Go to next message
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 ?


Re: try to put two report into one [message #204936 is a reply to message #204670] Wed, 22 November 2006 12:59 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member


Is there anyone out there to help to provide solution for the above ?
Re: try to put two report into one [message #204942 is a reply to message #204936] Wed, 22 November 2006 13:58 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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.
Re: try to put two report into one [message #204943 is a reply to message #204942] Wed, 22 November 2006 14:24 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member

Thanks a lot Littlefoot!

Let me put the idea in practical and let you know.

Thanks again for your help.

Re: try to put two report into one [message #205143 is a reply to message #204942] Thu, 23 November 2006 08:05 Go to previous message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
Hi Littlefoot,

yes it is working and also a new thing for me ...

bye
ashu
Previous Topic: Oracle Discoverer doubts
Next Topic: Continued.
Goto Forum:
  


Current Time: Fri Mar 29 05:17:29 CDT 2024