Home » Developer & Programmer » Reports & Discoverer » dates as columns (Reports 6i)
dates as columns [message #543959] Sat, 18 February 2012 05:20 Go to next message
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.
Re: dates as columns [message #543982 is a reply to message #543959] Sat, 18 February 2012 13:54 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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.
Re: dates as columns [message #543988 is a reply to message #543982] Sat, 18 February 2012 23:21 Go to previous messageGo to next message
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.
Re: dates as columns [message #544007 is a reply to message #543988] Sun, 19 February 2012 03:32 Go to previous messageGo to next message
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
Re: dates as columns [message #544029 is a reply to message #544007] Sun, 19 February 2012 07:59 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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>
Re: dates as columns [message #544074 is a reply to message #544029] Sun, 19 February 2012 23:27 Go to previous message
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.
Previous Topic: IS it possible formula column in lexical paramter
Next Topic: Matrix report
Goto Forum:
  


Current Time: Fri Mar 29 08:42:52 CDT 2024