|
|
|
|
|
|
| Re: Matrix report for showing calender events in Oracle Report [message #542625 is a reply to message #542596] |
Wed, 08 February 2012 04:15   |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Ah, so he WANTS to create a calendar! I completely missed the point ...
In that case, I suppose that one of row generator techniques should be used to create dates. For example:
SQL> select to_char(trunc(sysdate, 'yyyy') + level - 1, 'dd') day_number,
2 to_char(trunc(sysdate, 'yyyy') + level - 1, 'day') day_name,
3 to_char(trunc(sysdate, 'yyyy') + level - 1, 'month yyyy') month_year,
4 to_char(trunc(sysdate, 'yyyy') + level - 1, 'ww') week
5 from dual
6 connect by level <= add_months(trunc(sysdate, 'yyyy'), 12) -
7 trunc(sysdate, 'yyyy');
DA DAY_NAME MONTH_YEAR WE
-- --------- -------------- --
01 sunday january 2012 01
02 monday january 2012 01
03 tuesday january 2012 01
04 wednesday january 2012 01
05 thursday january 2012 01
06 friday january 2012 01
<snip>
27 thursday december 2012 52
28 friday december 2012 52
29 saturday december 2012 52
30 sunday december 2012 53
31 monday december 2012 53
366 rows selected.
Matrix with group report: 'month year' as matrix group field, 'day names' as rows, 'weeks' as columns and 'day numbers' as cell fields.
It might (and probably will) require some more values in order to correctly set the ORDER BY clause, but that's mostly it.
[Updated on: Wed, 08 February 2012 04:16] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Matrix report for showing calender events in Oracle Report [message #543087 is a reply to message #542572] |
Mon, 13 February 2012 02:02 |
 |
mitsmi
Messages: 19
Registered: November 2011
|
Junior Member |
|
|
This is my query which is not showing the 1st date of :P_from_date
SELECT (TRUNC(:P_FROM_DATE)+LEVEL)DT,
TO_CHAR(TRUNC(:P_FROM_DATE)+LEVEL,'MON')MON_NAME,
TO_CHAR(TRUNC(:P_FROM_DATE)+LEVEL,'DD')DAY_NO,
TO_CHAR(TRUNC(:P_FROM_DATE)+LEVEL,'D')DA,
TO_CHAR(TRUNC(:P_FROM_DATE)+LEVEL,'DY')DAY_NAME,
TO_CHAR(TRUNC(:P_FROM_DATE)+LEVEL,'WW')WK_NO,
TO_CHAR(TRUNC(:P_FROM_DATE)+LEVEL,'YYYY')YR,
TO_NUMBER(TO_CHAR(TRUNC(:P_FROM_DATE)+LEVEL,'YYYYMM'))YR_MM
FROM DUAL
WHERE TRUNC(:P_FROM_DATE) BETWEEN TRUNC(:P_FROM_DATE) AND TRUNC(:P_TO_DATE)
CONNECT BY LEVEL < (TRUNC(:P_TO_DATE) - TRUNC(:P_FROM_DATE))
And the report showing wrong grids.
the column is overlapping. I attached the snapshot.
-
Attachment: Doc3.JPG
(Size: 39.58KB, Downloaded 2762 times)
[Updated on: Mon, 13 February 2012 02:03] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
