Home » Developer & Programmer » Reports & Discoverer » Oracle Report
icon1.gif  Oracle Report [message #319332] Fri, 09 May 2008 21:27 Go to next message
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






Re: Oracle Report [message #319369 is a reply to message #319332] Sat, 10 May 2008 03:12 Go to previous messageGo to next message
Littlefoot
Messages: 20888
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.
Re: Oracle Report [message #319732 is a reply to message #319369] Mon, 12 May 2008 20:10 Go to previous messageGo to next message
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

Smile
Re: Oracle Report [message #319766 is a reply to message #319732] Tue, 13 May 2008 00:12 Go to previous message
Littlefoot
Messages: 20888
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).
Previous Topic: Reports 10g : SQL Query via Data Model freezes
Next Topic: Reports Output format
Goto Forum:
  


Current Time: Fri Dec 02 12:31:54 CST 2016

Total time taken to generate the page: 0.05795 seconds