Selecting All in 1st parameter overrides settings in lower parameters 1

[ChasK]

I have created a report that has four dynamic parameters in Crystal XI r1. The list of values is supplied by a SQL Command that also includes the option “All”. I have also created a select formula that will display “All” records when all is selected, or the appropriate data when an item other than all is selected. This works fine if I choose an option other than “All” in all four parameter options. However, If I choose “All” for any of the first three selection options, the report will return all records for the levels below even if I choose specific items at the lower levels, egg

Parameter Value
Team Consultancy
Sector Education
Responsibility John
Type of work Inspection

The above will filter the records to show records for the Team Consultancy, where the Sector is Education, and John is Responsible and where the Type of Work is Inspection.

Parameter Value
Team All
Sector Education
Responsibility John
Type of work Inspection

The above will return all records in the database regardless of the selections made for Sector, Responsibility and Type of Work – This is not the result I am looking for. Although I have selected “All” at Team level, I would like the report to filter by the lower options such as Sector, Responsibility and Type Of Work. I get a similar problem If I choose all at any point, in that all records for the levels below are shown.

My record selection formula is:-

(If {?Super Team} = '..All' then true

else if {?Sector} = '..All' then
{CST_COSTHEADER.CH_SORT} = {?Super Team}

Else If {?Responsibility} = "..ALL" Then
{CST_COSTHEADER.CH_SORT} = {?Super Team} and
{CST_COSTHEADER.CH_USER1} = {?Sector}

else if {?Type of Work} = '..All' then
{CST_COSTHEADER.CH_SORT} = {?Super Team} and
{CST_COSTHEADER.CH_USER1} = {?Sector}and
{CST_COSTHEADER.CH_USER2} = {?Responsibility}

else
{CST_COSTHEADER.CH_SORT} = {?Super Team} and
{CST_COSTHEADER.CH_USER1} = {?Sector} and
{CST_COSTHEADER.CH_USER2} = {?Responsibility} and
{CST_COSTHEADER.CH_USER3} = {?Type of Work})

Any advise gratefully received. Thank You.

 

[Turkbear]

Hi,
Once you say ..All for Team, your If test is met so no need to check the Else If or Else parts of your formula..therefore all records will be returned.

Since more than one parameter value can be '..All' you need to make a more extensive test and clarify to Crystal what criteria to apply..for instance, in your code if
{?Sector} = '..All' then is it true that the ONLY further selection will be CST_COSTHEADER.CH_SORT} = {?Super Team}?
So all records, regardless of the values in
{CST_COSTHEADER.CH_USER1},{CST_COSTHEADER.CH_USER2} and {CST_COSTHEADER.CH_USER3} should be returned?




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

Try this:

(
{?Super Team} = '..All' or
{CST_COSTHEADER.CH_SORT} = {?Super Team}
) and
(
{?Sector} = '..All' or
{CST_COSTHEADER.CH_USER1} = {?Sector}
) and
(
{?Responsibility} = "..ALL" or
{CST_COSTHEADER.CH_USER2} = {?Responsibility}
) and
(
{?Type of Work} = '..All' or
{CST_COSTHEADER.CH_USER3} = {?Type of Work}
) and

-LB

 

[Turkbear]

Hi,
As usual, lbass , you were able to figure a way to do it...Star for you..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ChasK]

lbass and Turkbear, thank you both for your suggestions.

Turkbear, I copied your revised code into my reports and with the exception of the last "and" it worked exactly as I had wanted. Thank you very much. This has saved me hours of head scratching.

 

[Turkbear]

Hi,
That was Lbass's code....she( I think lbass is a she, not that it matters here) is the one to credit...






To Paraphrase:"The Help you get is proportional to the Help you give.."