query Prompts

[naga5566]

hi,
i have a report with 2 prompts.
First prompts queries the values from the database.
Second prompt should display the values like with resp to current date.
The second prompt should contain all the 1 st day of the months prior to current date upto 12 months.
If the current date is 5 march 2003 then the prompt should display 1 march 2003 ,1 feb 2003...so on up to 1 feb 2002.
in the list.


Does any one know about how to do this.Thanks
in advance

 

[skrandel]

How would you do it in SQL? You simply need to replicate that in the LOV.

Something like; Select Trunc(month, yourdate) where yourdate < currentdate

I don't know the syntax, but this should give you an idea. Steve Krandel
BASE Consulting Group

http://www.baseconsulting.com

 

[Subhashab]

Modidying the LOV is the way to solve this problem. However the list of values which you want does it exist in any table. Because you cannot fire an sql without an underlying data.
In case you have a period table like in a DW enviornment then this can be achieved. Else you might have to write VB code for showing the code in the way you want.

 

[naga5566]

There is a database .for instance there is a date of birth column in the table.When i run the report it should show the prompt &quot;select the date&quot; with lov's contain the 1st of every month for the past one year(only 12 value).I have tried the vb code ,in that i used an array which returns 12 valus.Can i use @script to hold those 12 values.I also written the similar code in pl/sql.But not able to slove it.Can anyone tell me in detail to approach this problem

 

[Subhashab]

The underlying table you have mentioned contains date of birth. In case there is not birthdate for a particular month then how will the SQL return the value for this month.

If you have written the VBA code in BO then you can pass the value selected in your combobox directly into the prompt using codes and can also refresh the report.

If you go through the SDK documents you can achive this.

the PL SQL code will not be of any help

 

[blom0344]

Start considering the use of calenderfiles and if needed special views on them. I have a few of them that include such niceties as daynumber of the month,relative month, weeknumber , juliandate (to easily subtract days from each other). The thing you want can be done by populating a database view with just the dates you want in them..... T. Blom
Information analyst
tbl@shimano-eu.com

 

[Subhashab]

yes right blom
such period or calender table is omnipresent in all DW