OraFAQ Forum: Reports & Discoverer

Home » Developer & Programmer » Reports & Discoverer » dates as columns (Reports 6i)

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

dates as columns [message #543959]

Sat, 18 February 2012 05:20

arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates

Senior Member

I have a small requirement for report, i want to develop a report based on dates and employee performance.Suppose i give the parameter from date as 01'st jan 2011 and end date as 31'st Jan 2011 then employee hrs will be as summary as rows distributed among days with the given date range as columns.How to make each day as column automatically.

Report message to a moderator

Re: dates as columns [message #543982 is a reply to message #543959]

Sat, 18 February 2012 13:54

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

Senior Member
Account Moderator

Sounds like a matrix report. Employees names as rows, dates as columns, hours as cells.

Report message to a moderator

Re: dates as columns [message #543988 is a reply to message #543982]

Sat, 18 February 2012 23:21

arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates

Senior Member

yes very much like matrix report but dates to be generated based on from and to parameters.

Report message to a moderator

Re: dates as columns [message #544007 is a reply to message #543988]

Sun, 19 February 2012 03:32

ranamirfan
Messages: 535
Registered: January 2006
Location: Pakistan / Saudi Arabia

Senior Member

One Suggestion create one table use Before Report trigger & Insert data into a date column on the bases of from & to parameter and select this column in main query.

LD number;
begin	
Select  to_number(to_char(:To_Date,'dd'))  into LD  from dual;
Delete from Date_Range;
commit;

for i in 1..LD loop
Insert into Date_Range values(i||To_Char(:FromDate,'Mon-YYYY'));
end loop;
Commit;
  
return (TRUE);

end;

Output


01-FEB-12
02-FEB-12
03-FEB-12
04-FEB-12
05-FEB-12
06-FEB-12
07-FEB-12
08-FEB-12
.
.
.
29-FEB-12

Hope you'll get idea.

Regards,
Irfan

Report message to a moderator

Re: dates as columns [message #544029 is a reply to message #544007]

Sun, 19 February 2012 07:59

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

Senior Member
Account Moderator

I prefer row generator.

SQL> set verify off
SQL> get p
  1  select to_date('&&date_from', 'dd.mm.yyyy') + level - 1 res
  2  from dual
  3  connect by level <= to_date('&date_to', 'dd.mm.yyyy') -
  4*                     to_date('&&date_from', 'dd.mm.yyyy') + 1
SQL> /
Enter value for date_from: 01.01.2012
Enter value for date_to: 31.01.2012

RES
----------
01.01.2012
02.01.2012
03.01.2012
04.01.2012
05.01.2012
06.01.2012
07.01.2012
08.01.2012
09.01.2012
10.01.2012
11.01.2012
12.01.2012
13.01.2012
14.01.2012
15.01.2012
16.01.2012
17.01.2012
18.01.2012
19.01.2012
20.01.2012
21.01.2012
22.01.2012
23.01.2012
24.01.2012
25.01.2012
26.01.2012
27.01.2012
28.01.2012
29.01.2012
30.01.2012
31.01.2012

31 rows selected.

SQL>

Report message to a moderator

Re: dates as columns [message #544074 is a reply to message #544029]

Sun, 19 February 2012 23:27

arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates

Senior Member

Thanks ranamirfan and Littlefoot for the solution , i am going to try both ways now.

Report message to a moderator

Previous Topic:	IS it possible formula column in lexical paramter
Next Topic:	Matrix report

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 30 05:22:08 CST 2024