problem with parameter selections

[staceyn]

Hello,
I am using Crystal XI rel 2 with an informix 10 database.
I am trying to add 4 optional parameters to a report.
We want to make it where the user chooses to select a parameter value or if it isn't selected, they would get all records for that parameter returned.

2 of these paramters are string types, one is a number and one is a date type.

I have added the default value of "ALL" the the string parameters, a default value of "1" for the numeric parameter and a default value of 1/1/2000 for the date parameter.

The problem is, the only one that seems to be working properly is buyer. If I refresh and select only buyer(string parameter = buyer) 7011 and markdown numbers (numeric parameter = price_change_number)of 60 through 65 ( Only 3 of these 5 records have buyer 7011 associated with them)I do only get buyer 7011 but all of the markdown numbers are being returned into the report.

I have setup the parameters with the appropriate data types and default values and have added the following to the selection criteria through formula editor.

if {?buyer} = "ALL" then true
else
{markheader.mh_by} = {?buyer}
and
if {?Store}= "ALL" then true
else
{markheader.mh_store} = {?Store}
and

if {?Price Change Number} = 1 then true
else
{markdetail.md_id} = {?Price Change Number} and

if {?date}= Date (2000, 01, 01)then true
else
{markheader.mh_date} = {?date}

Any thoughts as to how to make all 4 parameters either return all values or the specified parameter input values in this report?

Thanks in advance

 

[lbass]

The optimal syntax (see SynapseVampire's faq on this) would be:

(
if {?buyer} <> "ALL" then
{markheader.mh_by} = {?buyer} else
if {?buyer} = "ALL" then true
) and
(
if {?Store}<> "ALL" then
{markheader.mh_store} = {?Store} else
if {?Store} = "ALL" then
true
) and
(
if {?Price Change Number} <> 1 then
{markdetail.md_id} = {?Price Change Number} else
if {?Price Change Number} = 1 then true
) and
(
if {?date} <> Date (2000, 01, 01) then
{markheader.mh_date} = {?date} else
if {?date} = Date (2000, 01, 01) then
true
)

Try this--the parens should help, and the setup for the if/thens ensures passage to the SQL statement.

-LB

 

[staceyn]

When I use this formula I am now getting no data returned on the report:
(
if {?buyer} <> "ALL" then
{markheader.mh_by} = {?buyer} else
if {?buyer} = "ALL" then true
) and
(
if {?Store}<> "ALL" then
{markheader.mh_store} = {?Store} else
if {?Store} = "ALL" then
true
) and
(
if {?Price Change Number} <> 1 then
{markdetail.md_id} = {?Price Change Number} else
if {?Price Change Number} = 1 then true
) and
(
if {?date} <> Date (2000, 01, 01) then
{markheader.mh_date} = {?date} else
if {?date} = Date (2000, 01, 01) then
true
)

 

[lbass]

Well, what parameter options did you select? This presumes that the user selects one option for each parameter, either the default or specific value(s).

-LB

 

[staceyn]

I used the range of 60 though 65 for the price change number, the value of 7011 for the buyer and left the other 2 parameters at the defaults.

 

[lbass]

Are you saying that the price change number is set up to allow range values? If so, try changing that segment of the formula to:

(
if minimum({?Price Change Number}) <> 1 then
{markdetail.md_id} = {?Price Change Number} else
if minimum({?Price Change Number}) = 1 then true
) and

-LB

 

[staceyn]

I am still getting no records returned. Any other ideas?

Here is my updated selection formula:

(
if {?buyer} <> "ALL" then
{markheader.mh_by} = {?buyer} else
if {?buyer} = "ALL" then true
) and
(
if {?Store}<> "ALL" then
{markheader.mh_store} = {?Store} else
if {?Store} = "ALL" then
true
) and
(
if minimum({?Price Change Number}) <> 1 then
{markdetail.md_id} = {?Price Change Number} else
if minimum({?Price Change Number}) = 1 then true
) and
(
if {?date} <> Date (2000, 01, 01) then
{markheader.mh_date} = {?date} else
if {?date} = Date (2000, 01, 01) then
true
)

 

[lbass]

This should work. Do you get records when you choose a selection for every parameter? You should be doing that, not just leaving them blank.

To trouble shoot, try commenting out each section of the formula to narrow down the problem. There is nothing inherently wrong with the formula, so there is some piece of information we are missing here.

-LB