parameter need to year instead of Date

[HPNAGA]

I am not sure if I am doing it in the right forum.
i am using Business Objects Designer V 11.5.0.0.

I have created a derived table which has prompts user to select a year like this

select * from ABC where YEAR (ABCDate) = YEAR[highlight #FCE94F](@Prompt('Select Date','D',,Mono,Free))[/highlight]

however this one shows the calendar with the dates, however I want to see the calendar so that the user can select the Year rather than a date. Is there anyway we can prompt for a year rather than a date. Please let me know if you need further information.

Thanks in advance.

 

[Charliy]

There may be other ways to do this but you could give this a try. Make your parameter a string, then make your select formula totext({datefield},"yyyy") = {?Year Parameter}

 

[HPNAGA]

Thanks for replying. Well I have simplified the query for posting it in the forum. we have the query like this

select * from Table where
Datefield >= date(substr(CHAR(YEAR((@Prompt('Select Date','D',,Mono,Free)))),1,4)|| '-1-1') AND
Datefield <= date(substr(CHAR(YEAR((@Prompt('Select Date','D',,Mono,Free)))),1,4)|| '-12-31').

The reason for not using any functions on the "Datefield" is that it will give performance boost. So I want something on the parameter side. SO that it make sense to the user to select year rather than a date.

 

[Madawc]

Define your parameter as Number rather than Date. Give it a few suitable years as default values.

It might also be more efficient if you can do it in SQL than Crystal, assuming you have SQL. First do the parameter, then use Database > Show SQL Query to see if it is sending the date selection to the server when it collects your data.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP