Home » Developer & Programmer » Reports & Discoverer » 3 in 1 report?
3 in 1 report? [message #194372] Thu, 21 September 2006 14:42 Go to next message
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


Re: 3 in 1 report? [message #194397 is a reply to message #194372] Fri, 22 September 2006 00:10 Go to previous messageGo to next message
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
Re: 3 in 1 report? [message #194543 is a reply to message #194397] Fri, 22 September 2006 10:34 Go to previous messageGo to next message
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.
Re: 3 in 1 report? [message #194574 is a reply to message #194543] Fri, 22 September 2006 15:16 Go to previous messageGo to next message
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;


Re: 3 in 1 report? [message #195020 is a reply to message #194574] Tue, 26 September 2006 11:03 Go to previous messageGo to next message
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



Re: 3 in 1 report? [message #195084 is a reply to message #195020] Tue, 26 September 2006 23:23 Go to previous messageGo to next message
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
Re: 3 in 1 report? [message #195409 is a reply to message #195084] Thu, 28 September 2006 08:06 Go to previous messageGo to next message
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 242 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 Go to previous message
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

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


Current Time: Wed Dec 07 02:53:26 CST 2016

Total time taken to generate the page: 0.10788 seconds