Solved

Parameter Fields/Filtering (Redux)

Posted on 2011-03-01
6
359 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:Clif
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 12

Expert Comment

by:JayConverse
ID: 35011871


{?CompanyCode-CostCenter - Cost_Center} = ''

or

{Work_Process.Cost_Center} = {?CompanyCode-CostCenter - Cost_Center}
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35011952
The problem is probably that the parameter is NULL not blank.

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

mlmcc
0
 
LVL 35

Accepted Solution

by:
James0628 earned 500 total points
ID: 35015283
I think the main problem with your test is that you use {Work_Process.Cost_Center} = "*".
 = only does an exact match, so that will only match the records where the field is an "*".  You could replace = with like:

if {?CompanyCode-CostCenter - Cost_Center} = '' then
    {Work_Process.Cost_Center} like "*"
else
    {Work_Process.Cost_Center} = {?CompanyCode-CostCenter - Cost_Center}


 However, it might be more efficient to just eliminate the first test and replace it with True:

if {?CompanyCode-CostCenter - Cost_Center} = '' then
    True
else
    {Work_Process.Cost_Center} = {?CompanyCode-CostCenter - Cost_Center}


 Or, you could eliminate the if-then and write it as Jay did, which is actually how I would write a test like this.

 James
0
Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

 
LVL 10

Author Closing Comment

by:Clif
ID: 35017824
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.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35018154
What is the full selection formula?

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

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 35025493
({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
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question