Home » Developer & Programmer » Reports & Discoverer » Matrix report for showing calender events in Oracle Report (Oracle reports Builder 10g)
Matrix report for showing calender events in Oracle Report [message #542572] Wed, 08 February 2012 01:52 Go to next message
mitsmi
Messages: 19
Registered: November 2011
Junior Member
I have to create the matrix report which shows calender. Looks Like this. Suggest me some Ideas.
/forum/fa/9819/0/e
  • Attachment: Report.JPG
    (Size: 42.78KB, Downloaded 2229 times)
Re: Matrix report for showing calender events in Oracle Report [message #542573 is a reply to message #542572] Wed, 08 February 2012 01:57 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This image is very beautiful.

Your code, though, seems to be malfunctioning because - data it shows is incorrect. For example:
- April has 30 (not 31) days
- May 1st 2011 was on Sunday, not Monday

Suggestions? Ideas? Make it work!

[EDIT: typo]

[Updated on: Wed, 08 February 2012 01:58]

Report message to a moderator

Re: Matrix report for showing calender events in Oracle Report [message #542596 is a reply to message #542573] Wed, 08 February 2012 03:15 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I don't think he has any code LF. that was probably knocked up in excel.
Re: Matrix report for showing calender events in Oracle Report [message #542625 is a reply to message #542596] Wed, 08 February 2012 04:15 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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 #542661 is a reply to message #542625] Wed, 08 February 2012 07:14 Go to previous messageGo to next message
mitsmi
Messages: 19
Registered: November 2011
Junior Member
I want it to display in financial year ie start from april-2011 to mar-2012

[Updated on: Wed, 08 February 2012 07:14]

Report message to a moderator

Re: Matrix report for showing calender events in Oracle Report [message #542663 is a reply to message #542661] Wed, 08 February 2012 07:20 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK; just substitute SYSDATE with your start date.
Re: Matrix report for showing calender events in Oracle Report [message #542738 is a reply to message #542572] Fri, 10 February 2012 00:34 Go to previous messageGo to next message
mitsmi
Messages: 19
Registered: November 2011
Junior Member
I m accepting user parameters from_month & to_month. example form april-2011 to jan-2012
How query will not work for that?
Re: Matrix report for showing calender events in Oracle Report [message #542745 is a reply to message #542738] Fri, 10 February 2012 00:59 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
My "sysdate" would be your "from date".
Your "connect by level" would subtract "to date - from date".
Re: Matrix report for showing calender events in Oracle Report [message #543087 is a reply to message #542572] Mon, 13 February 2012 02:02 Go to previous messageGo to next message
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.

/forum/fa/9835/0/
  • Attachment: Doc3.JPG
    (Size: 39.58KB, Downloaded 2191 times)

[Updated on: Mon, 13 February 2012 02:03]

Report message to a moderator

Re: Matrix report for showing calender events in Oracle Report [message #543093 is a reply to message #543087] Mon, 13 February 2012 02:08 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
query which is not showing the 1st date of :P_from_date

Compare your query and mine (especially LEVEL use, as well as < vs. <= in CONNECT BY clause). You first need to make your query return desired result set in SQL*Plus, then copy it to Reports.

As of overlaping fields: adjust their properties (such as fixed horizontal elasticity and such). I can't tell what you did wrong (and can't develop a report for you, sorry).
Re: Matrix report for showing calender events in Oracle Report [message #544371 is a reply to message #542572] Tue, 21 February 2012 07:28 Go to previous messageGo to next message
mitsmi
Messages: 19
Registered: November 2011
Junior Member
Can anyone sort out the problem why the cell are place in same week and not in the next. See my nls territory is india where week starts from sunday. Give me the solution.
/forum/fa/9876/0/
  • Attachment: untitled.JPG
    (Size: 51.50KB, Downloaded 2095 times)
Re: Matrix report for showing calender events in Oracle Report [message #544374 is a reply to message #544371] Tue, 21 February 2012 07:39 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm afraid I don't understand what you are saying. It is obvious that Saturdays in 2011 are shifted, but ... what does "why the cell are place in same week and not in the next" mean? Could you mark it (in MS Paint, for example) and post it back here?
Re: Matrix report for showing calender events in Oracle Report [message #544463 is a reply to message #542572] Wed, 22 February 2012 00:06 Go to previous messageGo to next message
mitsmi
Messages: 19
Registered: November 2011
Junior Member
/forum/fa/9878/0/
  • Attachment: untitled.JPG
    (Size: 54.28KB, Downloaded 1906 times)
Re: Matrix report for showing calender events in Oracle Report [message #544468 is a reply to message #544463] Wed, 22 February 2012 00:20 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As I said: "It is obvious that Saturdays in 2011 are shifted". It seems that it is what you are saying as well.

OK then; I don't know what to say except: fix the code.
Re: Matrix report for showing calender events in Oracle Report [message #544543 is a reply to message #542572] Wed, 22 February 2012 05:55 Go to previous message
mitsmi
Messages: 19
Registered: November 2011
Junior Member
Is there any solution for the problem.
Previous Topic: oracle reports builder
Next Topic: XML Publisher: Invalid Character found in text content
Goto Forum:
  


Current Time: Fri Mar 29 10:09:52 CDT 2024