OraFAQ Forum: Reports & Discoverer » Discoverer Parameters: Year to Date

Home » Developer & Programmer » Reports & Discoverer » Discoverer Parameters: Year to Date

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

Discoverer Parameters: Year to Date [message #216740]

Tue, 30 January 2007 04:00

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.

Report message to a moderator

Re: Discoverer Parameters: Year to Date [message #216756 is a reply to message #216740]

Tue, 30 January 2007 04:41

Littlefoot
Messages: 21823
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)

Report message to a moderator

Re: Discoverer Parameters: Year to Date [message #216759 is a reply to message #216756]

Tue, 30 January 2007 04:55

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

Report message to a moderator

Re: Discoverer Parameters: Year to Date [message #216770 is a reply to message #216759]

Tue, 30 January 2007 05:47

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: Discoverer Parameters: Year to Date [message #217350 is a reply to message #216770]

Thu, 01 February 2007 15:11

skooman
Messages: 913
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...?

Report message to a moderator

Re: Discoverer Parameters: Year to Date [message #217554 is a reply to message #217350]

Fri, 02 February 2007 14:40

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.

Report message to a moderator

Re: Discoverer Parameters: Year to Date [message #222346 is a reply to message #216740]

Sat, 03 March 2007 01:25

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

Report message to a moderator

Re: Discoverer Parameters: Year to Date [message #222347 is a reply to message #216740]

Sat, 03 March 2007 01:28

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

Report message to a moderator

Previous Topic:	Diffrent sections in report
Next Topic:	List Of Values In Discoverer

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 30 00:17:03 CST 2024