Solved

Parameter Fields/Filtering (Redux)

Posted on 2011-03-01
6
350 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 100

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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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 100

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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Suggested Solutions

Title # Comments Views Activity
Crystal Reports 9 and Subreports 3 89
Crystal Report sorting 6 26
Sum Before Standard Deviation 2 26
Crystal Reports - Record selection filter - formula editor 3 37
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. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

749 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