Need some input on 'All' Option

[bmpsu]

I’m using:
Crystal Reports XI
SQL Server 2000

I have two parts that I have been testing and I believe it to be working. I am looking for some fresh eyes to see if anyone can see anything I am missing.

I am using a SQL command for my report.

Select a.license_name, b.profession_name From a, b where a.license_id = b.license_id
Union
Select a.license_name, ‘All’ From a, b where a.license_id = b.license_id
Union
Select ‘All’, b.profession_name From a, b where a.license_id = b.license_id 
Union
Select ‘All’, ‘All’ From a, b where a.license_id = b.license_id

Then my Record Selection Formula looks like this:

If {?License_parameter} = ‘All’ AND {?Profession_parameter} <> ‘All’ Then
   ({Command.license_name} = 'All') and ({Command.profession_name} = {?Profession_parameter})
Else If {?License_parameter} <> ‘All’ AND {?Profession_parameter} = ‘All’ Then
   ({Command.license_name} = {?License_parameter}) and ({Command.profession_name} = ‘All’)
Else
   ({Command.license_name} = {?License_parameter}) and 
   ({Command.profession_name} = {?Profession_parameter})

I tried to use the idea the Business Objects site had. But theirs was an example for a dynamic prompt. I am not going to be able to use dynamic prompts with this report and was trying to see if I couldn’t make this work with static parameters using the append values option when setting up my LOV.

Have I completely lost it or will this work.

 

[IdoMillet]

What is the problem?

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[bmpsu]

I guess that is part of my question. I should have been more specific. I can have three scenarios when the user:

-Selects 'All' for License Name and a specific Profession Name
-Selects a specific License name and 'All' for Profession Name
-Selects 'All' for each parameter.

Trying to be more specific... From what I have tested, it seems to be working. I guess I didn't have too much confidence and needed some approval.

With the Unions in the SQL; what used to be one row with no Unions is now 4 rows looking something like this:

license_name profession_name
All profession_name
license_name All
All All

Does my record selection formula account for the scenarios above to pull the correct row?

Any Better?

 

[IdoMillet]

The result of the union query is a single result set. The record selection criterion would act on it exactly as would be expected.

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[bmpsu]

Thanks, seems to work.