Solved

Parameter Fields/Filtering (Redux)

Posted on 2011-03-01
6
339 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
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 34

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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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 34

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

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
[NotSupportedException: Not supported within subreports.] 2 72
Crystal Reports Sub Report 4 86
Formatting a Crystal Reports Report 9 84
Crystal Report for VS2013 17 105
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…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

803 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