Parameter Selector to filter report via Single Number sequence or Date Range

[Megs628]

Hi. Using Crystal Reports 10. ODBC connection to database.

My existing report has a Record Selector - {Quote Arrival Date} in (?StartDate) to (?EndDate).
This provides the user a start date & end date selector to create report. This report will include all Quotes within their selected period.

I need to change the report so the user can either select a single Quote (format is number) or use the report in its existing date range selector format.

I tried to create another parameter in which the user chose "Single Quote" or "Date Range" but I'm having issues that the Single Quote is a number format and the Date Range selector is a Date.

e.g
If {?Selector} = "Single Quote" then {Quote#}
else {Quote Arrival Date} in (?StartDate) to (?EndDate)

Errors - the else statement isn't in number format

Perhaps my logic of the selector isn't best practise or there is a simple fix to this report error. It seems ridiculous to create a copy of this report with a different record selection.

Appreciate your assistance

 

[CoSpringsGuy]

are you wanting it to return a specific {Quote#} from the database?


_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[Megs628]

Yes, exactly.

Report needs to offer option of printing a single quote or all quotes that are within the users specified date range.

Unfortunately even if a single date range was selected multiple quotes will print.

The user has to be able to nominate the actual quote number required.

 

[CoSpringsGuy]

Trying to do this from my phone.....
first create a number parameter called quote#

In record selection try something like this

{Quote Arrival Date} in (?StartDate) to (?EndDate)
And
if If {?Selector} = "Single Quote" then
{Qoute#} = {?Quote#}



_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[Megs628]

Great thanks - that worked in removing the Number error.

I changed it slightly as I need it the user to choose one or the other option. i.e. choose a single quote & add quote number or choose a date range. The date range shouldn't be included at all if the quote number is nominated.

Record selection formula:

{QUOTE.ARRIVAL_DATE} in {?Start Date} to {?End Date}
or
if {?Selector} = "Single Quote" then
{?QUOTE} = {QUOTE.QUOTE#}

The issue is now it displays all parameters - start date, end date, selector and quote. Crystal is also enforcing a number to be entered for the quote parameter. This field should be null unless selector parameter single quote selected and a quote number is entered.

I tried this formula - but simply removes the selector parameter and enforces a quote number to be applied.

if isnull({?QUOTE}) then
{QUOTE.ARRIVAL_DATE} in {?Start Date} to {?End Date}
else
{?QUOTE} = {QUOTE.QUOTE#}


If possible I'd like to show the selector and then once option selected e.g. single quote the quote number entry field is displayed. Alternatively if date range is selected then the start and end date fields are displayed.

 

[CoSpringsGuy]

the other way to do this would be to get rid of the {?Selector} parameter and build a default value of say 9999 into {?QUOTE} with a description that describes this is used for all.
-Edit parameter> scroll down to value options > in Prompt text describe to the user that 9999 returns all values
-Edit parameter> scroll down to Value Options > in Default value enter 9999)

then your code would be

{QUOTE.ARRIVAL_DATE} in {?Start Date} to {?End Date}
AND
if {?QUOTE} < 9999 then {QUOTE.QUOTE#} = {?QUOTE}

This would leave you with only 3 prompts. It would return all accounts if user kept the default as 9999


_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[hilfy]

Another way to do this, without using the If statement which can cause Crystal to filter data in memory instead of pushing the filter to the database, would be to do something like this:

(
({?Selector} = "Single Quote" and {QUOTE.QUOTE#} = {?QUOTE})
or
({?Selector} <> "Single Quote" and {QUOTE.ARRIVAL_DATE} in {?Start Date} to {?End Date})
)

If you were using Crystal 2011 or 2013, there is an option in the parameter configuration to leave a parameter blank (null). This option does not exist in earlier versions of Crystal. In that case, your filter would look like this:

(
(HasValue({?QUOTE}) and {QUOTE.QUOTE#} = {?QUOTE})
or
(not HasValue({?QUOTE}) and {QUOTE.ARRIVAL_DATE} in {?Start Date} to {?End Date})
)

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[Megs628]

Thanks , tried running report on Crystal 2011.

Tried both suggested solutions and Crystal just isn't processing how anticipated.

It either renders a report with no data or enforces data into all parameters. I understand the coding is saying to do one or the other but Crystal isn't doing this. i.e. process the date range OR process the single estimate.

Even with leaving the default value(s) as blank the results were the same.

 

[hilfy]

Please post your full selection criteria - I suspect you may be missing some parentheses.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[Megs628]

Apologies should have included.

I ran it for date range 7/9/2015 - 11/9/2015

It includes following Quotes within this date range:
50439
50581
50642
50741
50760
50783
50845
50936
50941
49512
50652
50657
50712
50737
50174
50342
50410
50459
50608
50796
50950
50593
50717
50755
50969
50539

 

[Megs628]

Thanks for all your help. Fixed it. Missed "optional parameter" value in parameter options.

Changed and code works

Thanks!