Birt material

http://www.birt-exchange.org/documentation/BIRT_231/wwhelp/wwhimpl/js/html/wwhelp.htm#href=multipage.18.3.html

Birt demo links

http://www.birt-exchange.com/be/demos/birt-report-designers/#

Birt Reporting useful links

http://download.birt-exchange.com/products/demos/birt/birt2_2/birt_2.2_index_small.html


http://www.birt-exchange.org/wiki/Connecting_to_Data_in_the_BIRT_Designer/

http://www.birt-exchange.com/be/products/birt-report-designers/birt-report-designer-options/

http://www.birt-exchange.org/forum/designing-birt-reports/


http://www.eclipse.org/birt/phoenix/

Default value in Select Value in Oracle database

create table (id number,crdate date default sysdate)

Assigning a default value to an attribute in Applcation Designer

To define a field default value using the Default Value control:
1 In the Application Designer, open an application in the Workspace tab.
2 Open the Control Palette.
Defining a Default Filter for a Table
20 Application Developer Guide
3 Drag the Default Value control into the section that contains the field for
which you want to assign a default value.
If you do not see the Default Value control in the section, click Select Action >
Toggle Show All Controls.
4 Open Control Properties for the control, and perform the following actions:
a Enter the name of the attribute bound to the field to which you are
assigning the default value.
b Enter the desired default value in the Value field.
c Select Insert for the Default Type
5 Click Save.

Stored Procedure using Ref Cursor --02

Package Body

CREATE OR REPLACE PACKAGE BODY NET_TOT_CAL
IS
procedure NET_TOT_TICKET(p_start_date in date
,p_end_date in date
,p_ownerposition in varchar2
,p_owner in varchar2
,resultset in out nocopy ticket_out)
is
--p_ticketid varchar2(200):='SRQ16237';
t_start_date date;
t_end_date date;
--p_owner varchar2(200):='E003738';
i number;
v_net_date varchar2(200):=0;
v_time varchar2(200);
v_hours varchar2(20);
v_minutes varchar2(20);
v_seconds varchar2(20);
v_mseconds varchar2(20);
v_check_date number;
v_hours_tot varchar2(20);
v_minutes_tot varchar2(20);
v_seconds_tot varchar2(20);
v_mseconds_tot number;
v_tot_date varchar2(200):=0;
--v_display_name varchar2(200);
Tab NET_TOT_CAL.TABLE1;
Tab1 NET_TOT_CAL.TABLE1;
v_workhours number;
v_workdate date;
v_starttime date;
v_display_name varchar2(200);
v_endtime date;
t_stmt number;
v_workhours1 number;
v_workdate1 date;
v_starttime1 date;
v_endtime1 date;
v_display number;
v_changedate_next varchar2(200);
begin
i:=0;
t_stmt:=1;
v_display:=0;
delete from test_ticket;
commit;
for main_rec in (select xt.*
,xp.displayname
,xpc.shiftnum
,xpc.calnum
from ticket xt
,xx_person xp
,xx_personcal xpc
where xt.status='CLOSED'
and to_date(trunc(actualfinish),'DD-MON-RRRR') between to_date(p_start_date,'DD-MON-RRRR') and to_date(p_end_date,'DD-MON-RRRR')
and xt.owner=xp.personid
and xp.personid=xpc.personid
-- and ticketid=t_ticketid
and actualfinish is not null
and NVL(xt.ownergroup,'X')= Coalesce(p_ownerposition,xt.ownergroup,'X')
and xp.displayname=nvl(p_owner,xp.displayname)
)
Loop
t_stmt:=2;
v_net_date:=0;
v_tot_date:=0;
/* for rec in (select e.status
,e.tkstatusid
,e.changedate
,e.ticketid
,(select min(d.status)
from tkstatus d
where d.tkstatusid > e.tkstatusid
and e.ticketid = d.ticketid
and d.tkstatusid = ((select min(d.tkstatusid)
from tkstatus d
where d.tkstatusid > e.tkstatusid
and e.ticketid = d.ticketid))) next_status
,(select min(d.tkstatusid)
from tkstatus d
where d.tkstatusid > e.tkstatusid
and e.ticketid = d.ticketid) next_tkstatusid
,(select min(d.changedate)
from tkstatus d
where d.tkstatusid>e.tkstatusid
and e.ticketid=d.ticketid) changedate_next
,xp.shiftnum
,xp.calnum
from tkstatus e
,xx_personcal xp
where e.ticketid=main_rec.ticketid
and e.owner=main_rec.owner
and e.owner=xp.personid
order by 2
)*/
for rec in (select e.status
,e.tkstatusid
,e.changedate changedate
,e.ticketid
,lead(status) over (order by tkstatusid) next_status
,lead(tkstatusid) over(order by tkstatusid) next_tkstatusid
,lead(changedate) over(order by tkstatusid) changedate_next
from tkstatus e
where e.ticketid=main_rec.ticketid
order by 2
)
Loop
Tab.delete;
Tab1.delete;
--------------for calculating Net time--------------------
t_stmt:=3;
if rec.status in ('INPROG','QUEUED') then
t_stmt:=4;
--v_changedate_next:=to_char(rec.changedate_next,'DD-MON-RRRR HH:MI:SS PM');
NET_TOT_CAL.date_cal(TO_DATE(rec.changedate,'DD-MON-RRRR HH:MI:SS PM'),TO_DATE(rec.changedate_next,'DD-MON-RRRR HH:MI:SS PM'),Tab);
if tab.count>0 then
t_stmt:=5;
For k in tab.first..tab.last
Loop
if tab(k).next_date is not null then
t_stmt:=6;
Begin
select workhours
,to_date(workdate,'DD-MON-RRRR HH:MI:SS AM')
,starttime
,endtime
into v_workhours
,v_workdate
,v_starttime
,v_endtime
from xx_workperiod
where calnum=main_rec.calnum
and shiftnum=main_rec.shiftnum
and trunc(workdate)=trunc(TO_DATE(tab(k).next_date,'DD-MON-RRRR HH:MI:SS PM'));
Exception
when no_data_found then
dbms_output.put_line('No data exists in work period table for :'||main_rec.calnum||' and '||main_rec.shiftnum||' for the date :'||tab(k).next_date);
when too_many_rows then
dbms_output.put_line('Multiple data exists in work period table for :'||main_rec.calnum||' and '||main_rec.shiftnum||' for the date :'||tab(k).next_date);
End;

t_stmt:=7;
-- dbms_output.put_line(tab(k).next_date);
t_stmt:=8;
if v_workhours<>0 then
t_stmt:=9;
if to_date(v_workdate,'DD-MON-RRRR HH:MI:SS AM')-to_date(tab(k).next_date,'DD-MON-RRRR HH:MI:SS PM')=0 then
v_net_date:=v_net_date+v_workhours;--((to_date(tab(k).next_date,'DD-MON-RRRR HH:MI:SS PM')-to_date(v_workdate,'DD-MON-RRRR HH:MI:SS AM'))*24);
-- dbms_output.put_line('v_net_date if : '||v_net_date||'---------'||v_workdate||'---------------'||tab(K).next_date);
else
v_net_date:=v_net_date+((to_date(v_workdate,'DD-MON-RRRR HH:MI:SS AM')-to_date(tab(k).next_date,'DD-MON-RRRR HH:MI:SS PM'))*24);
-- dbms_output.put_line('v_net_date else : '||v_net_date||'---------'||v_workdate);
end if;
end if;
else
v_net_date:=v_net_date+((tab(k).next_hrs)*24);
-- dbms_output.put_line('v_net_date final : '||v_net_date||'---------'||v_workdate);
end if;
End Loop;
End If;
End If;

--------------for calculating total time--------------------

if rec.status in ('OPEN','PENDING','RESOLVED','SCHEDULED','INPROG','QUEUED','NEW') and rec.next_status<>'CLOSED' and rec.changedate_next is not null then

t_stmt:=10;
--v_changedate_next:=to_char(rec.changedate_next,'DD-MON-RRRR HH:MI:SS PM');
NET_TOT_CAL.date_cal(TO_DATE(rec.changedate,'DD-MON-RRRR HH:MI:SS PM'),TO_DATE(rec.changedate_next,'DD-MON-RRRR HH:MI:SS PM'),Tab1);
if Tab1.count>0 then
t_stmt:=11;
For k in Tab1.first..Tab1.last
Loop
if Tab1(k).next_date is not null then
t_stmt:=12;
Begin
select workhours
,to_date(workdate,'DD-MON-RRRR HH:MI:SS AM')
,starttime
,endtime
into v_workhours1
,v_workdate1
,v_starttime1
,v_endtime1
from xx_workperiod
where calnum=main_rec.calnum
and shiftnum=main_rec.shiftnum
and trunc(workdate)=trunc(TO_DATE(Tab1(k).next_date,'DD-MON-RRRR HH:MI:SS PM'));
Exception
when no_data_found then
dbms_output.put_line('No data exists in work period table for :'||main_rec.calnum||' and '||main_rec.shiftnum||' for the date :'||Tab1(k).next_date);
when too_many_rows then
dbms_output.put_line('Multiple data exists in work period table for :'||main_rec.calnum||' and '||main_rec.shiftnum||' for the date :'||Tab1(k).next_date);
End;

t_stmt:=13;
-- dbms_output.put_line(tab(k).next_date);
t_stmt:=14;
if v_workhours1<>0 then
t_stmt:=15;
if to_date(v_workdate1,'DD-MON-RRRR HH:MI:SS AM')-to_date(Tab1(k).next_date,'DD-MON-RRRR HH:MI:SS PM')=0 then
v_tot_date:=v_tot_date+v_workhours1;--((to_date(tab(k).next_date,'DD-MON-RRRR HH:MI:SS PM')-to_date(v_workdate,'DD-MON-RRRR HH:MI:SS AM'))*24);
-- dbms_output.put_line('v_net_date if : '||v_net_date||'---------'||v_workdate||'---------------'||tab(K).next_date);
else
v_tot_date:=v_tot_date+((to_date(v_workdate1,'DD-MON-RRRR HH:MI:SS AM')-to_date(Tab1(k).next_date,'DD-MON-RRRR HH:MI:SS PM'))*24);
-- dbms_output.put_line('v_net_date else : '||v_net_date||'---------'||v_workdate);
end if;
end if;
else
v_tot_date:=v_tot_date+((Tab1(k).next_hrs)*24);

end if;
End Loop;
End If;
End If;

------------------------------------------------------------------------------------------------------------------------

End Loop;
v_net_date:=v_net_date/24;
v_tot_date:=v_tot_date/24;
select days
,TRUNC(A*24) Hours
,TRUNC(A*24*60 - 60*TRUNC(A*24)) Minutes
,TRUNC(A*24*60*60 - 60*TRUNC(A*24*60)) Seconds
-- ,TRUNC(A*24*60*60*100 - 100*TRUNC(A*24*60*60)) mSeconds
into v_mseconds
,v_hours
,v_minutes
,v_seconds
-- ,v_mseconds
from
(
select
trunc(v_net_date) Days,
v_net_date - trunc(v_net_date) A
from dual
);

select decode(length(v_hours),2,v_hours
,'0'||v_hours)
,decode(length(v_minutes),2,v_minutes
,'0'||v_minutes)
,decode(length(v_seconds),2,v_seconds
,'0'||v_seconds)
into v_hours
,v_minutes
,v_seconds
from dual;


select days
,TRUNC(A*24) Hours
,TRUNC(A*24*60 - 60*TRUNC(A*24)) Minutes
,TRUNC(A*24*60*60 - 60*TRUNC(A*24*60)) Seconds
-- ,TRUNC(A*24*60*60*100 - 100*TRUNC(A*24*60*60)) mSeconds
into v_mseconds_tot
,v_hours_tot
,v_minutes_tot
,v_seconds_tot
-- ,v_mseconds
from
(
select
trunc(v_tot_date) Days,
v_tot_date - trunc(v_tot_date) A
from dual
);


select decode(length(v_hours_tot),2,v_hours_tot
,'0'||v_hours_tot)
,decode(length(v_minutes_tot),2,v_minutes_tot
,'0'||v_minutes_tot)
,decode(length(v_seconds_tot),2,v_seconds_tot
,'0'||v_seconds_tot)
into v_hours_tot
,v_minutes_tot
,v_seconds_tot
from dual;

-----------------------------queries for displaying other attributes---------------------------------------------
--selecting display name
Begin
select displayname
into v_display_name
from xx_person
where personid=main_rec.owner;
Exception
when no_data_found then
v_display_name:=null;
End;
/*
if v_display=0 then
dbms_output.put_line(' SR# NetTime TotalTime Last Name Userid Location Call Type Status Priority Created Date Status Date Created By ');
dbms_output.put_line('------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------');
v_display:=2;
end if;
dbms_output.put_line(main_rec.ticketid||' '||v_mseconds||'D'||v_hours||':'||v_minutes||':'||v_seconds||' '||v_mseconds_tot||'D'||v_hours_tot||':'||v_minutes_tot||':'||v_seconds_tot||' '||main_rec.displayname||' '||main_rec.affectedperson||' '||main_rec.location||' '||main_rec.externalsystem||' '||main_rec.status||' '||main_rec.internalpriority||' '||main_rec.creationdate||' '||main_rec.statusdate||' '||main_rec.createdby);
*/
insert into TEST_TICKET
values(main_rec.ticketid,v_mseconds||'D'||v_hours||':'||v_minutes||':'||v_seconds,v_mseconds_tot||'D'||v_hours_tot||':'||v_minutes_tot||':'||v_seconds_tot,v_display_name,main_rec.affectedperson,main_rec.location,main_rec.externalsystem,main_rec.status,main_rec.internalpriority,main_rec.creationdate,main_rec.statusdate,main_rec.createdby);

End Loop;
commit;
OPEN RESULTSET FOR
SELECT * from TEST_TICKET;

exception
when others then
dbms_output.put_line('Error at stmt: '|| t_stmt||Sqlerrm);End NET_TOT_TICKET;

PROCEDURE DATE_CAL( P_DATE1 IN DATE
,P_DATE2 IN DATE
,P_TAB OUT TABLE1
)
IS
/*-- date1 date:=to_date('11-MAR-2009 08.35.15 PM','DD-MON-RRRR HH:MI:SS PM');
-- date2 date:=to_date('24-MAR-2009 09.34.15 PM','DD-MON-RRRR HH:MI:SS PM');
day1 varchar2(20);
date3 date;
date4 number;
v_check number:=0;
k number;
j number;
Begin
j:=0;
LOOP
if v_check=0 and p_date2-p_date1>=1 then
date3:=p_date1;
--DBMS_OUTPUT.PUT_LINE('DATE3 IS:'||to_char(DATE3,'DD-MON-RRRR HH:MI:SS PM'));
j:=j+1;
P_TAB(j).Next_Date:=to_char(date3,'DD-MON-RRRR HH:MI:SS PM');
v_check:=1;
end if;
if p_date2-nvl(date3,p_date1)<1 then
if nvl(date3,p_date1)-p_date2<0 then
date4:=p_date2-nvl(date3,p_date1);
else
date4:=nvl(date3,p_date1)-p_date2;
end if;
--dbms_output.put_line('date4 is:'||date4);
j:=j+1;
P_TAB(j).Next_Hrs:=date4;
else
DATE3:=nvl(date3,p_date1)+1;
--DBMS_OUTPUT.PUT_LINE('DATE3 IS:'||to_char(DATE3,'DD-MON-RRRR HH:MI:SS PM'));
j:=j+1;
P_TAB(j).Next_Date:=to_char(date3,'DD-MON-RRRR HH:MI:SS PM');
end if;
exit when date4>0 or date3-p_date2=0;
END LOOP;
/*
for k in p_tab.first..p_tab.last
loop
DBMS_OUTPUT.PUT_LINE('Date is:'||p_tab(k).next_date);
end loop;
*/
/*
Type rec is record
(Next_Date varchar2(200),
Next_Hrs Number
);
Type Table1 is Table
of rec index by Binary_integer;

p_TAB Table1;

p_date1 date:=to_date('11-MAR-2009 08.35.15 PM','DD-MON-RRRR HH:MI:SS PM');
p_date2 date:=to_date('24-MAR-2009 08.55.15 PM','DD-MON-RRRR HH:MI:SS PM');
*/
day1 varchar2(20);
date3 date;
date4 number;
v_check number:=0;
k number;
j number;
v_exit number:=0;
Begin
j:=0;
LOOP
if v_check=0 and p_date2-p_date1>=1 then
date3:=trunc(p_date1)+1;
-- DBMS_OUTPUT.PUT_LINE('DATE3 IS:'||to_char(DATE3,'DD-MON-RRRR HH:MI:SS PM'));
-- j:=j+1;
-- TAB(j).Next_Date:=to_char(date3,'DD-MON-RRRR HH:MI:SS PM');
v_check:=1;
if date3>p_date1 then
date4:=date3-p_date1;
--dbms_output.put_line('DATE4 is:'||date4);
j:=j+1;
P_TAB(j).Next_Hrs:=date4;

end if;
end if;

if p_date2-nvl(date3,p_date1)<1 then
if nvl(date3,p_date1)-p_date2<0 then
date4:=p_date2-nvl(date3,p_date1);
v_exit:=1;
else
date4:=nvl(date3,p_date1)-p_date2;
v_exit:=1;
end if;
-- dbms_output.put_line('date4 is:'||date4);
j:=j+1;
P_TAB(j).Next_Hrs:=date4;
else
DATE3:=nvl(date3,p_date1)+1;
--DBMS_OUTPUT.PUT_LINE('DATE3 IS:'||to_char(DATE3,'DD-MON-RRRR HH:MI:SS PM'));
j:=j+1;
P_TAB(j).Next_Date:=to_char(date3,'DD-MON-RRRR HH:MI:SS PM');
end if;
exit when v_exit=1;
END LOOP;
/*
for k in p_tab.first..tab.last
loop
DBMS_OUTPUT.PUT_LINE('Date is:'||p_tab(k).next_date||'---------------'||p_tab(k).next_hrs);
end loop;
*/
end date_cal;

END NET_TOT_CAL;
/

Stored Procedure using Ref cursor --01

Normal Table:


CREATE TABLE TEST_TICKET
( SR_NO VARCHAR2(240),
NET_TIME VARCHAR2(240),
TOTAL_TIME VARCHAR2(240),
LAST_NAME VARCHAR2(240),
USERID VARCHAR2(240),
LOCATION VARCHAR2(240),
CALLTYPE VARCHAR2(240),
STATUS VARCHAR2(240),
PRIORITY VARCHAR2(240),
CREATEDDATE VARCHAR2(240),
STATUSDATE VARCHAR2(240),
CREATEDBY VARCHAR2(240)
)




Droping a table :

drop table TEST_TICKET

select * from TEST_TICKET

truncate table test_ticket.



Package specification...

CREATE OR REPLACE PACKAGE NET_TOT_CAL
IS

TYPE TICKET_OUT IS REF CURSOR RETURN TEST_TICKET%ROWTYPE;

TYPE REC IS RECORD
(NEXT_DATE VARCHAR2(200),
NEXT_HRS NUMBER
);
TYPE TABLE1 IS TABLE
OF REC INDEX BY BINARY_INTEGER;

PROCEDURE NET_TOT_TICKET (P_START_DATE IN DATE
,P_END_DATE IN DATE
,P_OWNERPOSITION IN VARCHAR2
,P_OWNER IN VARCHAR2
,RESULTSET IN OUT NOCOPY TICKET_OUT);

PROCEDURE DATE_CAL( P_DATE1 IN DATE
,P_DATE2 IN DATE
,P_TAB OUT TABLE1
);

END NET_TOT_CAL;
/
SHOW ERR;




Procedure to display output:


CREATE OR REPLACE PACKAGE NET_TOT_CAL
IS

TYPE TICKET_OUT IS REF CURSOR RETURN TEST_TICKET%ROWTYPE;

TYPE REC IS RECORD
(NEXT_DATE VARCHAR2(200),
NEXT_HRS NUMBER
);
TYPE TABLE1 IS TABLE
OF REC INDEX BY BINARY_INTEGER;

PROCEDURE NET_TOT_TICKET (P_START_DATE IN DATE
,P_END_DATE IN DATE
,P_OWNERPOSITION IN VARCHAR2
,P_OWNER IN VARCHAR2
,RESULTSET IN OUT NOCOPY TICKET_OUT);

PROCEDURE DATE_CAL( P_DATE1 IN DATE
,P_DATE2 IN DATE
,P_TAB OUT TABLE1
);

END NET_TOT_CAL;
/
SHOW ERR;