OraFAQ Forum: Reports & Discoverer

Home » Developer & Programmer » Reports & Discoverer » Oracle Report

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

Oracle Report [message #319332]

Fri, 09 May 2008 21:27

yjOy
Messages: 8
Registered: May 2008

Junior Member

hi! can anyone heLp me?? Sad

i'm working on a report in oracle (report builder). In the parameter form, i want that the user will input the month and the corresponding year in this format:

example:
Enter month and year: MAY 2007

however, the data in the database is in this format:
01-MAY-07

how will i filter all the data in a certain month and year that will include to this period?
01-MAY-07 to 31-MAY-07
the other one is
15-MAY-07 to 15-APR-07

or can i attach a caLEndar in the parameter form?

thanks! i hope you can heLP me asap.. Smile

Report message to a moderator

Re: Oracle Report [message #319369 is a reply to message #319332]

Sat, 10 May 2008 03:12

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

Senior Member
Account Moderator

You can not attach a calendar to a Reports parameter form, but you might create your own parameter form (using Forms Builder) and attach calendar to it; then you'd call a report and pass parameters from this form.

As of parameters' values, MAY 2007 isn't the best example. Is DATE format mask of "MAY" equal to "mon" or "month"? I guess it is "mon", so here's how to deal with it:

SQL> SELECT TO_DATE('MAY 2007', 'mon yyyy') first_of_month,
  2         LAST_DAY(TO_DATE('MAY 2007', 'mon yyyy')) last_of_month
  3  FROM dual;

FIRST_OF_M LAST_OF_MO
---------- ----------
01.05.2007 31.05.2007

SQL>

For "May 15th" (and similar), you'll have to add 15 days to the first of month.

Report message to a moderator

Re: Oracle Report [message #319732 is a reply to message #319369]

Mon, 12 May 2008 20:10

yjOy
Messages: 8
Registered: May 2008

Junior Member

thank you for the heLp!!!

it reaLLy heLped me aLOt..hihi Smile

nwei, can i ask a question again? i am new to oracLE, and im still on the process of learning it..

"how can i convert amount into words with decimal values?
ex:
126.68

here is my sql statement:
select SUM(PREQAMOUNT),
TO_CHAR(TO_DATE(TO_CHAR(SUM(PREQAMOUNT), '999999999'), 'J'), 'JSP') || ' AND ' ||
SUBSTR((TO_CHAR(SUM(PREQAMOUNT) , '9999999.90')), -2) || ' /100' WORDS,

FROM PREQ, dual
WHERE PREQNO=853

output:
one hundred twenty seven AND 68/100

Actually its working but somehow there are instances that when the decimal value can be rounded off, it automatically rounded off, w/c should not be the case. i want it to convert the amount exactly as it is.... i dont have any problem in showing the decimal value since i was able to show the number such as this "68/100" so, the only problem is converting the whole number into words...

hoping u can help me on this.......thanks a lot... Smile

Report message to a moderator

Re: Oracle Report [message #319766 is a reply to message #319732]

Tue, 13 May 2008 00:12

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

Senior Member
Account Moderator

Basically, this IS the way to do that. You might also take a look at this AskTom discussion.

Quote:

there are instances that when the decimal value can be rounded off, it automatically rounded off, w/c should not be the case

Do you have an example? Post it here, please! Format your code so that it would be readable (how to do that? Check the OraFAQ Forum Guide).

Report message to a moderator

Previous Topic:	Reports 10g : SQL Query via Data Model freezes
Next Topic:	Reports Output format

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 01 02:33:36 CDT 2024