Home » Developer & Programmer » Reports & Discoverer » Discoverer Parameters: Year to Date
Discoverer Parameters: Year to Date [message #216740] Tue, 30 January 2007 04:00 Go to next message
credgrave
Messages: 2
Registered: January 2007
Junior Member
Hi guys,

At the moment im working on a database warehousing project. I'm using discoverer as a front end for a user friendly environment. Does anyone know how i can create a parameter to show the year to date? i need to put the following condition into the paramater:

FULL_DATE BETWEEN =add_months(sysdate,-12) AND =sysdate

However, as im sure you'll find, you cannot use the 'BETWEEN' function when using parameters Sad - Ideally i'd like the user to be able to choose between two buttons, one showing all the data and one showing year to date. I would prefer to use a parameter to do this rather than the user adding/removing conditions from worksheet.

Thanks in advance,

Chris.
Re: Discoverer Parameters: Year to Date [message #216756 is a reply to message #216740] Tue, 30 January 2007 04:41 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't use Discoverer so this probably won't help much, but - if you'd like to select records where 'full_date' is always one year back from 'today' (sysdate), there's no need to use BETWEEN operator: put it as

FULL_DATE >= ADD_MONTHS(SYSDATE, -12)
Re: Discoverer Parameters: Year to Date [message #216759 is a reply to message #216756] Tue, 30 January 2007 04:55 Go to previous messageGo to next message
credgrave
Messages: 2
Registered: January 2007
Junior Member
Thanks for the reply Smile

That does take care of the fact that i cant use the 'BETWEEN' function, however, discoverer will not let me use a calculation as a default value Mad
Re: Discoverer Parameters: Year to Date [message #216770 is a reply to message #216759] Tue, 30 January 2007 05:47 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh well, it seems that you'll have to wait for Sabine to show up. She is something like a Supergirl and will help you as soon as she comes back. Hopefully.
Re: Discoverer Parameters: Year to Date [message #217350 is a reply to message #216770] Thu, 01 February 2007 15:11 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Hihi, can't resist answering this one of course...


You can create one condition with two parameters and a between comparison, like:
<item> BETWEEN <:p_1> AND <:p_2>

For :p_1 and :p_2 you can choose create new parameter twice, while creating the condition.

However, in the example you show us, I can't see where the parameter comes in. Or do you mean the parameter should be like Yes or No to limit the data to the last 12 months? Or do you want the parameter to give the number of months to select? Or...?
Re: Discoverer Parameters: Year to Date [message #217554 is a reply to message #217350] Fri, 02 February 2007 14:40 Go to previous messageGo to next message
Ericle
Messages: 44
Registered: April 2006
Location: United States of America ...
Member

Since he's talking about YTD I would assume he means that the parameter would be the user entered date and there would need to be a calculation of YTD total that would be between January 1st of the current year and the date entered. But I'm not sure if that's what he wants. Based on the code he gave, it looks like he may want the total from the last 12 months? Hard to say for sure. We need a little more clarification before we can help find a solution.
Re: Discoverer Parameters: Year to Date [message #222346 is a reply to message #216740] Sat, 03 March 2007 01:25 Go to previous messageGo to next message
zafarkarachi
Messages: 29
Registered: November 2006
Location: Karachi Pakistan
Junior Member
try this one

full_date between trunc(to_date(sysdate,'dd-mon-yyyy'),'year')
and sysdate

Zafar Iqbal
Re: Discoverer Parameters: Year to Date [message #222347 is a reply to message #216740] Sat, 03 March 2007 01:28 Go to previous message
zafarkarachi
Messages: 29
Registered: November 2006
Location: Karachi Pakistan
Junior Member
Try the better one for month to date and year to date
query.

select
trunc(to_date(sysdate,'dd-mon-yyyy'),'month') month_to_date,
trunc(to_date(sysdate,'dd-mon-yyyy'),'year') year_to_date
from dual

Zafar Iqbal
Karachi
Previous Topic: Diffrent sections in report
Next Topic: List Of Values In Discoverer
Goto Forum:
  


Current Time: Sat Dec 10 20:28:07 CST 2016

Total time taken to generate the page: 0.09462 seconds