Parameter Fields/Filtering

[Former Member]

I'm using Crystal XI

I have a report that needs to filter on some or all of seven different fields. In most cases the filter will be one or all.

Once I add all the parameter fields (as Dynamic, since the values may change), how do I filter on some but not others? For example, I want to see all the subcontractors given a specific state (State = "CA", Subcontractors = "ALL"), then I want to clear the states and select a specific subcontractor and view all the states associated with him/her (State = "ALL", Subcontractor = "Joe Bleaux")

The problems are:

1. How do I filter, or should I say not filter? (ie {Table1.State} = "*")

2. I can't seem to select the elipses in the parameter dialog. If I do so, it says "The value is not valid"

3. Even on a single filter (State, for example), once I select a state to filter on, I cannot figure out how to then "unfilter" and show all states.

Hopefully I explained my problems.

Edited by: Clif001 on Mar 1, 2011 10:53 PM

[Former Member]

1.

IF {?StateParameter} = "ALL" THEN 1=1 ELSE {Table1.State} = {?StateParameter}

2.

No clue what you are talking about. What elipses?

3.

To "unfilter" you need to refresh the report and choose a different parameter value of simply remove that criteria from the Selection Expert.

HTH,

Jason

[Former Member]

1. Great, now if I can figure out how to add "ALL" to my Dynamic Parameter Field list, I'll be fine.

2. When the Parameter Field is set up as "Dynamic", Type "String" and "Allow Range Values = False", it produces a dropdown list. The top item on the list is an elipses (...). I thought one would select that meaning "ALL" or "No Filter". I don't know where the elipses comes from. It's not in my table/view.

3. By "Unfilter" I mean remove the filter from the previous parameter selection. For example, I have a list of salesmen across several states. I want to filter on one or none. I select "CA", and view/print the report. Now I want to see all states. How to I remove the "CA" filter? Or, how do I see all states (without setting up the State Parameter Field as "Allow Range Values = True" and literally selecting all states)? I cannot simply remove the code from the selection expert. The report is going to b viewed by a user who will be using Crystal, but doesn't want to edit the report every time she wants to change a filter.

[Former Member]

Sorry, but item 1 doesn't appear to work.

If I select "A"", I get all records in spite of whatever other filters are selected.

For example, if I select a single salesman and "ALL" states, I get all salesmen in all states.

Here's my code from the Record Selection Formula:

if 
    {?StateParameter} = "ALL" 
then 
    1=1 
else 
    {Table1.State} = {?StateParameter}

if 
    {?SalesmanParameter} = "ALL" 
then 
    1=1 
else 
    {Table1.Salesmen} = {?SalesmanParameter}

Thanks

[Former Member]

1. I provided an example on your other thread... [Adding "ALL" to a Parameter Field List|;

2. I'm still on CR XI R2. I don't show any ellipses in my multi-value parameters... So still no idea on that one.

3. There should be a "Remove All" button on the multi-value parameter. You should also have double arrows that will allow you to add all values at once.

As for the last post. The formula works. You just have to write it correctly.

if 
    {?StateParameter} = "ALL" 
then 
    1=1 
else 
    {Table1.State} = {?StateParameter}
AND // the devil's in the details...
if 
    {?SalesmanParameter} = "ALL" 
then 
    1=1 
else 
    {Table1.Salesmen} = {?SalesmanParameter}

[Former Member]

I'm not talking about Multi-value. I'm referring specifically to the single-value selection (Option "Allow muiltiple values" is False) This produced a single dropdown list box where only one item can be selected (I wish we could attach images). I was hoping for a way to unselect the item (without having to select another effectively leaving the selection blank).

At any rate the 1=1 really, honestly and truely doesn't work (even when I add the AND, which I always had).

Here's the code in my Record Selection Formula:

{Work_Process.Company} = {?Company}

and  // see, I remembered

if 
    {?Cost Center} = '--ALL--'
then
    1=1
else
    {Work_Process.Cost_Center} = {?Cost Center}

and  //  I remembered it here too

if
    {?Salesman} = '--ALL--'
then
    1=1
else
    {Work_Process.Salesman} = {?Salesman}

and  //  It's here too

if
    {?State} = '--ALL--'
then
    1=1
else
    {Work_Process.State} = {?State}

if I select "- -ALL- -" for, say, Cost Center and Salesman and select "CA" for state I will get all records irrespective of the state.

[Former Member]

Doh!!!

I know what the problem is... It's not a problem with the 1=1. It's with the IF THEN ELSE. When you're setting CostCenter to "ALL" it's evaluating to TRUE (1=1) and then stops evaluating.

A few parenthesis should fix you right up. Try it like this...

{Work_Process.Company} = {?Company}
 
and
 
(if 
    {?Cost Center} = '--ALL--'
then
    1=1
else
    {Work_Process.Cost_Center} = {?Cost Center}
 )
and 
 
(if
    {?Salesman} = '--ALL--'
then
    1=1
else
    {Work_Process.Salesman} = {?Salesman}
 )
and
 
(if
    {?State} = '--ALL--'
then
    1=1
else
    {Work_Process.State} = {?State}
)

Sorry... I've seen this before and should have caught it earlier.

Jason

[Former Member]

That did it!

Thanks for your help.