asked on

Parameter Fields/Filtering (Redux)

I'm usng Crystal XI

I have set up all my Parameter Fields and now need to get Crystal to filter on them. What I would like is to tell Crystal that is a Parameter Field value is blank, then skip that filter (or select *, however you want to put it.)

Here is the code where I get messed up...

//This works
{Work_Process.Company_Code} = {?CompanyCode-CostCenter - Company_Code}
    AND

//This doesn't
if {?CompanyCode-CostCenter - Cost_Center} = '' then
    {Work_Process.Cost_Center} = "*"
else
    {Work_Process.Cost_Center} = {?CompanyCode-CostCenter - Cost_Center}

Open in new window

Any ideas?

TIA

JayConverse

{?CompanyCode-CostCenter - Cost_Center} = ''

or

{Work_Process.Cost_Center} = {?CompanyCode-CostCenter - Cost_Center}

Mike McCracken

The problem is probably that the parameter is NULL not blank.

I believe all parameters must have a value in CR XI.

mlmcc

ASKER CERTIFIED SOLUTION

James0628

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Clif

ASKER

Actually this works:
[code]
if {?CompanyCode-CostCenter - Cost_Center} = '' then
{Work_Process.Cost_Center} like "*"
else
{Work_Process.Cost_Center} = {?CompanyCode-CostCenter - Cost_Center}
[/code]
And this one doesn't:
[code]
if {?CompanyCode-CostCenter - Cost_Center} = '' then
True
else
{Work_Process.Cost_Center} = {?CompanyCode-CostCenter - Cost_Center}
[/code]
Well, technically the second works if there is only one Parameter Field. But if there is more than one, then the returned value True overrides all the others and shows all records no matter what other Parameter is selected.

I did come up with one small issue. If there are any null values in the Work_Process.Cost_Center field, selecting Like '*' omits the null records.

Mike McCracken

What is the full selection formula?

The one using TRUE should work and it will include the NULL records.

mlmcc

James0628

({Work_Process.Cost_Center} like "*") and True are virtually identical. The only exception would be when the field is null. The "like" would not handle that, while True would (of course) still be true. Which one appeared to be working better would depend on how you wanted to handle the nulls, and what else is in the formula.

If there are other tests in the formula (eg. for other parameters), then the if-else should be enclosed in (). For example:

{field A} = {?param A} and
(
if {?CompanyCode-CostCenter - Cost_Center} = '' then
True
else
{Work_Process.Cost_Center} = {?CompanyCode-CostCenter - Cost_Center}
) and
{field B} = {?param B}

If the field could be null, then you need to test for those separately. The question is, how do you want to handle them? If you want to include them if the user selects nothing, and exclude them if they select a specific cost center, you could use:

if {?CompanyCode-CostCenter - Cost_Center} = '' then
(IsNull ({Work_Process.Cost_Center}) or
{Work_Process.Cost_Center} like "*")
else
(not IsNull ({Work_Process.Cost_Center}) and
{Work_Process.Cost_Center} = {?CompanyCode-CostCenter - Cost_Center})

-- or --

if {?CompanyCode-CostCenter - Cost_Center} = '' then
True
else
(not IsNull ({Work_Process.Cost_Center}) and
{Work_Process.Cost_Center} = {?CompanyCode-CostCenter - Cost_Center})

James