Parameter Query

[dcjames]

In using Crystal 11, I created the following formula:

(if{?Business_Segment} = '*' then
{USR.USR_UDF_ORGANIZATION} like '*'
else
{USR.USR_UDF_ORGANIZATION} = {?Business_Segment}) and
(if{?Loc_Code} = '*' then
{USR.USR_UDF_LOCAT_CODE}like '*'
else
{USR.USR_UDF_LOCAT_CODE} = {?Loc_Code}) and
(if{?Division_Code}= '*' then
{USR.USR_UDF_DIVISION_CODE} like '*'
else
{USR.USR_UDF_DIVISION_CODE}= {?Division_Code}) and
(if{?JobCode} = '*' then
{USR.USR_UDF_JOB_CODE}like '*'
else
{USR.USR_UDF_JOB_CODE} = {?JobCode}) and
(if{?Department_Code}= '*' then
{USR.USR_UDF_DEPARTMENT_CODE} like '*'
else
{USR.USR_UDF_DEPARTMENT_CODE}= {?Department_Code}) and
(if{?Supervisors User ID}= '*' then
{USR_1.USR_LOGIN} like '*'
else
{USR_1.USR_LOGIN}= {?Supervisors User ID}) AND

{USR.USR_STATUS} = {?Emp_Status};

No records are being returned when a valid entry is used. Any ideas on how to correct and/or improve this formula is greatly appreciated.

Thanks

 

[kray4660]

From what I can tell there is nothing wrong with the structure of the selection statement. If the selection statement is removed do you get records? If so, then I would add each one of the If statements until you stop getting records, that may provide a clue of where the issue is.

 

[dcjames]

If I put * for all fields I do get records returned. Its only when I start "mixing" the parameters that I get no records returned.

 

[kray4660]

Not sure without having a good grasp of what the data looks like. Still I would break down and work with each If statement. FYI: You could put each one of those if statements in a formula and use the formulas in your select statement.

 

[pmax9999]

A couple of things to check:

> Does the Case of the data match the Case of the parameter?
> Is it possible the data has leading/trailing spaces, carriage returns etc?
> How is the parameter being entered - ie typed manually or from a drop down, or dynamic parameter? If it is manual of from a drop down list you created, check them for spelling etc.

Cheers
Pete

 

[dcjames]

Thanks for all the help...I really appreciate all of the hints.

After going back to the customer I found that some of the fields could have NULL values ( reason report wasn't working correctly). Here's the new formula that I'm using that appears to be working.

{USR.USR_STATUS} = {?Status} and
(
(if{?Business Segment} = '*' then
({USR.USR_UDF_ORGANIZATION} like '*' or isnull({USR.USR_UDF_ORGANIZATION}))
else {USR.USR_UDF_ORGANIZATION} = {?Business Segment}) and

(if{?Location Code} = '*' then
({USR.USR_UDF_LOCAT_CODE} like '*' or isnull({USR.USR_UDF_LOCAT_CODE}))
else {USR.USR_UDF_LOCAT_CODE} = {?Location Code}) and

(if{?Division Code} = '*' then ({USR.USR_UDF_DIVISION_CODE} like '*' or isnull({USR.USR_UDF_DIVISION_CODE}))
else {USR.USR_UDF_DIVISION_CODE}= {?Division Code}) and

(if{?Job Code} = '*' then ({USR.USR_UDF_JOB_CODE}like '*' or isnull({USR.USR_UDF_JOB_CODE}))
else {USR.USR_UDF_JOB_CODE} = {?Job Code}) and

(if{?Department Code}= '*' then ({USR.USR_UDF_DEPARTMENT_CODE} like '*' or isnull({USR.USR_UDF_DEPARTMENT_CODE}))
else {USR.USR_UDF_DEPARTMENT_CODE}= {?Department Code})
)