How do I use a string and a date parameter in the same case statement?

[rorymo]

CR XI
SQL Server 2000

I am having problems in using two types of parameters in a report, one date type and one string type.

I want to show one only when it is picked, not both of them.

For my parameter {?Timeperiod} the choices are:

choice 1 "month" - which would return records with an open date in the lastfullmonth

choice 2 "customized range" - which would let the user pick a date range

The {?timeperiod} parameter is defined as a string type.

values are "month" and "customized range"
default value is blank.
allow custom values is false
allow multiple values is false.
allow discrete values is true.
allow range values is false.


I have another parameter {?Date) that is defined as a date type.
(I want to reference this to enable the user to pick a date range.)
default value is blank.
allow custom values is true.
allow multiple values is false.
allow discrete values is false.
allow range values is true.


What I am trying to do is this:
- When the user chooses "month" the record selection references a formula that uses last full month.
- when the user chooses "customized range", the record selection references the {?Date} parameter
so that they can choose a date range.

If I try to do a case statement in the record selection:

select {?Timeperiod}
case "month" : {@Last Full Month}
case "customized range" : {?date}

it tells me that a boolean is needed at the "customized range" statement.

If I try to use an if/then/else,
if {?Time Period} = "month" then {@Last Full Month} else
if {?Time Period} = "customized range" then {?Date}

it tells me that a boolean is needed at the "customized range" statement.


Alternate way to try this:
If I try to use two formulas with case statements (one for the first scenario and the other for the second),
it keeps wanting an "else" after the last "if" when I use
if {?Time Period} = "month" then {@Last Full Month} else
if {?Time Period} = "customized range" then {?Date}

I want to show the month parameter selection or the date parameter selection, not both, because if i do, it
keeps making me fill in the date range even though I select the "month" choice.

I think this can be done, but I am missing something. I hope I have been fairly clear in what I am trying to do.

All help is greatly appreciated.
(Pls forgive if I don't answer right away - I am going on leave for a week)

Thanks in advance.
Rory

 

[CoSpringsGuy]

One way i have done this, probably a better way out there, was to create a string parameter with options of month or range. Allow multiple values false. Keep your current range parameter. My selection formula reads:

if {?month/range} = "range" then
{Payment_History.OCCURENCE_DATE} = {?daterange}
else if {?month/range} = "month" then
year({Payment_History.OCCURENCE_DATE})=year(CurrentDate)
and
month({Payment_History.OCCURENCE_DATE})=month(CurrentDate)

The month option will ignore whatever values are in the daterange parameter. This option also does degrade performance a bit so consider your database size if you use it. For that reason there may be a better option available

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[rorymo]

Thank you COSpringsGuy,

That worked when the "range" is chosen first, but when the "month" is chosen first, it forces you to make an entry into the range field.

This is the code I put in:

if {?Month or Range} = "customized range" then
{@Open} = {?Range}
else
if {?Month or Range} = "month" then
{@Open} = LastFullMonth

(NOTE: "@Open is a date formula - we have to use formulas to convert the dates since the dates are in numeric format)

Should I put an "or" in place of the "else"?

I also wish that I could only have the parameter that was chosen display instead of both every time.

Thanks again for your help.
Rory

 

[CoSpringsGuy]

No your formula looks correct to me. Read this from Ken Hamadys review of Crystal 2008. I assume you are not using that version

Make a parameter optional:
Now users can skip a parameter. There is even a function that allows a formula to check if a parameter was skipped, so it can know what to do. This means we can do things like have users skip a date parameter and have the report use today, yesterday or any other date calculation as the default (skip) behavior. This feature even applies to dynamic parameters so we now can say a skipped parameter means ALL, or some other default value without resorting to a union query.

I pulled that from his website

http://www.kenhamady.com/

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[lbass]

In XI, the user will always be prompted for both parameters regardless of the selection of month or customized range. It is just that the range will only affect the record selection if the Customized Range is chosen. You could create a default for the range so that they don't have to enter anything, and you might want to mention that in the prompt text.

-LB

 

[rorymo]

Thanks to both of you for the reply.
I'll check the link to Ken H.
And I'll create a default for the range.
I really appreciate your help!
Rory