Solved

Parameter Fields/Filtering (Redux)

Posted on 2011-03-01
6
322 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

762 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now