OraFAQ Forum: Reports & Discoverer

Home » Developer & Programmer » Reports & Discoverer » 3 in 1 report?

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

3 in 1 report? [message #194372]

Thu, 21 September 2006 14:42

Akshar
Messages: 116
Registered: May 2006

Senior Member

Hi Guys,

I am using Oralce Application Server 10g.

I have three report with only difference by 3 TYPES
i.e. Annualy,Quartely,Carrier.

My question is that : Is there any way i can combine
these seperate reports into one single report and point this single report as three parameter to the existing parameters form ?

If need more info, Please let me know.

Here is my Query for three Reports:

===============annual Report query ============================


SELECT   a.patientnumber acute_patient_number, a.icd9 acute_icd9,
         c.descript acute_icd9_desc, SUM (a.sum_svcschedulerate) acute_cost
    FROM tblpatintake b,
         tblmedicaldiagnosis c,
         tblnetworkplanrptgroup nprg,
         authorized_utilization_s a
   WHERE nprg.reportgroupid = :p_reportgroupid
     AND nprg.planid = a.planid
     AND :p_year = a.yr_no
     --and :p_quarter = a.qtr_no
     AND b.patientnumber = a.patientnumber
     AND b.intakeid = a.intakeid
     AND b.operationcentercode = a.operationcentercode
     AND b.dischargetoloccode = 'ACUTE'
     AND c.icd9 = a.icd9
GROUP BY a.patientnumber, a.icd9, c.descript
ORDER BY SUM (a.sum_svcschedulerate) DESC


==============quaterly Report query==================


SELECT   a.patientnumber acute_patient_number, a.icd9 acute_icd9,
         c.descript acute_icd9_desc, SUM (a.sum_svcschedulerate) acute_cost
    FROM tblpatintake b,
         tblmedicaldiagnosis c,
         tblnetworkplanrptgroup nprg,
         authorized_utilization_s a
   WHERE nprg.reportgroupid = :p_reportgroupid
     AND nprg.planid = a.planid
     AND :p_year = a.yr_no
     AND :p_quarter = a.qtr_no
     AND b.patientnumber = a.patientnumber
     AND b.intakeid = a.intakeid
     AND b.operationcentercode = a.operationcentercode
     AND b.dischargetoloccode = 'ACUTE'
     AND c.icd9 = a.icd9
GROUP BY a.patientnumber, a.icd9, c.descript
ORDER BY SUM (a.sum_svcschedulerate) DESC




===========Carrier Report query=========================


SELECT   a.patientnumber acute_patient_number, a.icd9 acute_icd9,
         c.descript acute_icd9_desc, SUM (a.sum_svcschedulerate) acute_cost
    FROM tblpatintake b,
         tblmedicaldiagnosis c,
         tblnetworkplanrptgroup nprg,
         tblreportgroup e,
         authorized_utilization_s a
   WHERE e.reportgroupid = nprg.reportgroupid
     AND e.reportgroupid NOT IN
            (180,
             183,
             186,
             188,
             191,
             203,
             206,
             227,
             228,
             229,
             230,
             231,
             232,
             233,
             234,
             235,
             236,
             237,
             238,
             365,
             366,
             550,
             705,
             744,
             785,
             825,
             865,
             905,
             906,
             907
            )
     AND e.reportgroupclassid = 4                           --:p_reportgroupid
     AND nprg.planid = a.planid
     AND a.yr_no = :p_year
     AND a.qtr_no = :p_quarter
     AND b.patientnumber = a.patientnumber
     AND b.intakeid = a.intakeid
     AND b.operationcentercode = a.operationcentercode
     AND b.dischargetoloccode = 'ACUTE'
     AND c.icd9 = a.icd9
GROUP BY a.patientnumber, a.icd9, c.descript
ORDER BY SUM (a.sum_svcschedulerate) DESC

Report message to a moderator

Re: 3 in 1 report? [message #194397 is a reply to message #194372]

Fri, 22 September 2006 00:10

sandeepk7
Messages: 137
Registered: September 2006

Senior Member

The values you are selecting are same in all these queries but the number of tables used in from clause are different for last query. You may try the following for first two queries. You can pass dynamic where condition in you report and one additional parameter to identify that which type of report should run.

e.g
create a user parameter say wherecl, datatype char(4000) and modify your base query like

SELECT a.patientnumber acute_patient_number,a.icd9 acute_icd9,
       c.descript acute_icd9_desc,SUM(a.sum_svcschedulerate)acute_cost
FROM tblpatintake b,
     tblmedicaldiagnosis c,
     tblnetworkplanrptgroup nprg,
     authorized_utilization_s a
WHERE nprg.reportgroupid = :p_reportgroupid
      AND nprg.planid = a.planid
      AND :p_year = a.yr_no
      AND b.patientnumber = a.patientnumber
      AND b.intakeid = a.intakeid
      AND b.operationcentercode = a.operationcentercode
      AND b.dischargetoloccode = 'ACUTE'
      AND c.icd9 = a.icd9 &wherecl
GROUP BY a.patientnumber, a.icd9, c.descript
ORDER BY SUM (a.sum_svcschedulerate) DESC

now create one more user parameter say report_type, datatype char(1)

writer a after parameter form trigger like

function AfterPForm return boolean is
begin
  If :report_type='Q' then -- Q represent Quarter Report
     :wherecl:=' AND '||:p_quarter ||'= a.qtr_no ';
  else -- Annual Report
     :wherecl:=null;
  End if;
  return (TRUE);
end;

Try this.....

Sandy

Report message to a moderator

Re: 3 in 1 report? [message #194543 is a reply to message #194397]

Fri, 22 September 2006 10:34

Akshar
Messages: 116
Registered: May 2006

Senior Member

Sandy Thanks a lot!

I will try this approach & will come back to you.

Thanks again for you help.

Report message to a moderator

Re: 3 in 1 report? [message #194574 is a reply to message #194543]

Fri, 22 September 2006 15:16

Akshar
Messages: 116
Registered: May 2006

Senior Member

Hi Sandy,

I have made a demo which can combine 3 queries
(in my case it is '3 IN 1 REPORT')
into one report.

However it is a demo but my real report application
is not far away from this code.

Thanks for your hint and ideas.

Here is the code for after parameter form trigger:


function AfterPForm return boolean is
begin
  if :report_type = 'Q' then
  	 :wherecl := ' And to_char(HIRE_DATE,''YYYY'') <= 1998 ';
  elsif :report_type = 'A' then
  	 :wherecl := ' And to_char(HIRE_DATE,''YYYY'') > 1998 ';
  elsif :report_type = 'C' then
  	 :tablename := ' , DEPARTMENTS ';
  	 :wherecl := '  AND EMPLOYEES.DEPARTMENT_ID IN (SELECT dept_id from emp_dept)'
  	            ||' AND DEPARTMENTS.location_id = 2400'
  	            ||' AND DEPARTMENTS.department_id = employees.department_id '
  	            ||' And to_char(HIRE_DATE,''YYYY'') <= 1998 ';
   else
  	 :wherecl := null;
  end if;
  return (TRUE);
end;

Report message to a moderator

Re: 3 in 1 report? [message #195020 is a reply to message #194574]

Tue, 26 September 2006 11:03

Akshar
Messages: 116
Registered: May 2006

Senior Member

Hi guys,

Now have a problem in implimenting the following code with Real application.

I am using Oracle Application Server 10g.

I got error when i try to tun the report : i.e.

REP-1401:'afterpform': Fatal PL/SQL Error Ocurred.
ORA-06502:PL/SQL: numric or value error

Here is code. Where am I going wrong?


function AfterPForm return boolean is
begin
 If     :report_type='C' then   	-- C represent Carrier Report
        :tablename := ', tblreportgroup c ';  	
        :wherecl   :=' AND c.reportgroupclassid = '|| :p_reportgroupid 
					   ||' and c.reportgroupid =  d.reportgroupid '
					   ||' and d.reportgroupid NOT IN(180,183,186,188,191,203,206,227,'
             ||' 228,229,230,231,232,233,234,235,236,237,238,365,366,550,705,744,'
             ||' 785,825,865,905,906,907) and '
             ||:p_year ||' = a.yr_no and '|| :p_quarter ||'= a.qtr_no ' ;
 
 elsif :report_type ='Q' then     -- Q represent Quarter Report
       :tablename := '';
       :wherecl   := 'AND '|| :p_reportgroupid ||' = d.reportgroupid  and   a.yr_no = '||:p_year 
                    ||' and  '|| :p_quarter || '= a.qtr_no ' ;
 elsif :report_type ='A' then     -- A represent Annual Report
       :tablename := ''; 	 
       :wherecl   :=  'AND '|| :p_reportgroupid ||' = d.reportgroupid  and   a.yr_no = '||:p_year  ;  
 else
       :tablename := '';
       :wherecl   := '';
 End if;
  return (TRUE);
end;

And here is Main Report query which will get appended
with other condtions through the above conditioned code :


select a.icd9, 
          b.descript top_wound_care_descript, 
          count(distinct a.patientnumber) top_wound_care_patients,
          count(distinct a.intakeid) top_wound_care_intakes, 
          sum(a.sum_svcschedulerate) top_wound_care_amounts
from tblmedicaldiagnosis b,
         tblnetworkplanrptgroup d,
        authorized_utilization_s a &tablename
where  d.planid = a.planid        &wherecl
and a.icd9 IN ('7070','2508','7079','9985','9983','9490',
                        '9583','9461','9462','9463','6829','8798')
and b.icd9 = a.icd9
group by a.yr_no, 
               a.qtr_no, 
               a.icd9,
               b.descript
order by top_wound_care_amounts desc

Report message to a moderator

Re: 3 in 1 report? [message #195084 is a reply to message #195020]

Tue, 26 September 2006 23:23

sandeepk7
Messages: 137
Registered: September 2006

Senior Member

Check datalenght of Wherecl. is it quite long enough to hold the data. and try the below code..

function AfterPForm return boolean is
begin
 If     :report_type='C' then   	-- C represent Carrier Report
        :tablename := ', tblreportgroup c ';  	
        :wherecl   :=' AND c.reportgroupclassid = '''|| :p_reportgroupid ||'''
 and c.reportgroupid =  d.reportgroupid  and d.reportgroupid NOT IN(180,183,186,188,191,203,206,227,228,229,230,231,232,233,234,235,236,237,238,365,366,550,705,744,785,825,865,905,906,907) and '''||:p_year ||''' = a.yr_no and '''|| :p_quarter ||'''= a.qtr_no ' ;-- if for number datatype then remove '''
 
 elsif :report_type ='Q' then     -- Q represent Quarter Report
       :tablename := '';
       :wherecl   := 'AND '''|| :p_reportgroupid ||''' = d.reportgroupid  and   a.yr_no = '''||:p_year ||''' and  '''||:p_quarter ||''' = a.qtr_no ' ;
 elsif :report_type ='A' then     -- A represent Annual Report
       :tablename := ''; 	 
       :wherecl   :=  'AND '|| :p_reportgroupid ||' = d.reportgroupid  and   a.yr_no = '''||:p_year||''''  ;  
 else
       :tablename := '';
       :wherecl   := '';
 End if;
  return (TRUE);
end;

Sandy

Report message to a moderator

Re: 3 in 1 report? [message #195409 is a reply to message #195084]

Thu, 28 September 2006 08:06

Akshar
Messages: 116
Registered: May 2006

Senior Member

Sandy, appreciate your insight!
You are right 'wherecl' size was not set to 4000.

Otherewise the code is fine till Option 'A' AND 'Q'
BUT for 'C' option i am getting some weird error.

Here is attached screenshot of error.

Please guide.

Thanks a lot.

Attachment: ERRORS.JPG
(Size: 95.51KB, Downloaded 906 times)

[Updated on: Thu, 28 September 2006 08:07]

Report message to a moderator

Re: 3 in 1 report? [message #195489 is a reply to message #195409]

Fri, 29 September 2006 01:15

sandeepk7
Messages: 137
Registered: September 2006

Senior Member

I am not sure for this kind of error. Try to run report with lesser values in NOT IN (1 or 2 values only).

Sandy

Report message to a moderator

Previous Topic:	PREVIOUS YEARS
Next Topic:	Stopping Access to reports

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 15:27:33 CST 2024