record selection doesn't show in WHERE clause

[yehong]

I have a record selection formula like the following but it does not appear in the WHERE clause portion of the SQL Query.
if {?GroupBy}='DEPT' then
(if {?GroupValue} <> 'No Filter' then
{table1.dept_name} in {?GroupValue}
else
{table1.dept_name}={table1.dept_name})
else
if {?GroupBy}='NAME' then......

Thanks for any idea or tips

 

[Reebo99]

THese things are very dependant on CR version. If you've got CR9 then :

(if {?GroupBy}='DEPT' then
(if {?GroupValue} <> 'No Filter' then
{table1.dept_name} in {?GroupValue}
else true)else true)
and

(if {?GroupBy}='NAME' then......

Will work. If youve got < 9, then I'm sure someone else can help.....



Reebo
UK

&quot;Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.&quot;
- Albert Einstein (1879-1955)

 

[yehong]

Reebo, thanks for your input. I am using CR8.5. I am still waiting on more input if someone can shade more light on this. Thanks,

 

[Ngolem]

Give us the whole record selection formula....not just a snippet of what you think is important

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[yehong]

Here is the formula that I am using in the record selection for one report. I have two parameters {?GroupBy} and
{?GroupValue}, when user selects 'DEPT' and selects 'No Filter' then report will return all department names and use another formula given at bottom to group the report accordingly.

if {?GroupBy}='DEPT' then
(if {?GroupValue} <> 'No Filter' then
{table1.dept_name} in {?GroupValue}
else
{table1.dept_name}={table1.dept_name})
else
if {?GroupBy}='EMP_NAME' then
(if {?GroupValue} <> 'No Filter' then
{table1.emp_name} in {?GroupValue}
else
{table1.emp_name}={table1.emp_name})
else
if {?GroupBy}='DIVISION' then
(if {?GroupValue} <> 'No Filter' then
{table1.division_name} in {?GroupValue}
else
{table1.division_name}={table1.division_name})

==============================
Conditonal grouping formula:
==============================
if {?GroupBy}='DEPT' THEN
{table1.dept_name}
ELSE
IF {?GroupBy}='NAME' THEN
{table1.emp_name}
ELSE
IF {?GroupBy}='DIVISION' THEN
{table1.division_name}


hope this helps in understanding the problem.

 

[synapsevampire]

Try the following:

(
if {?GroupBy}='DEPT' then
(
if
{?GroupValue} <> 'No Filter' then
{table1.dept_name} in {?GroupValue}
else {?GroupValue} = 'No Filter'
true
)
)

else etc…

Note that if you explicitly state the qualifying and disqulaifying Crystal does a better job. Please observe the parenthetical and Boolean True logic, just continue using the same method for the else conditions, and test after section.

-k

 

[Ngolem]

try this...instead

( if {?GroupValue} <> &quot;No Filter&quot; then
Switch
(
{?GroupBy} = &quot;DEPT&quot; ,
{table1.dept_name} in {?GroupValue},
{?GroupBy} = &quot;EMP_NAME&quot; ,
{table1.emp_name} in {?GroupValue),
{?GroupBy} = &quot;DIVISION&quot; ,
{table1.division_name} in {?GroupValue)
)
else if {?GroupValue} = &quot;No Filter&quot; then
true



Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[yehong]

hi Synapse, When I am trying your solution, the formula editor asking a ) Parenthesis is missing on the end of line 5. When I inserted a ) it asked same ) at same place again. Could you look into it please. Next I am going to try Ngolem's solution.


(
if {?GroupBy}='DEPT' then
(
if
{?GroupValue} <> 'No Filter' then
{table1.dept_name} in {?GroupValue}
else {?GroupValue} = 'No Filter'
true
)
)

 

[yehong]

Hi Ngolem, I tried your solution it did not return any errors but I am still not able to see a WHERE clause. I select GroupBy =&quot;DEPT&quot; and then selected a departement name, say &quot;FINANCE&quot;, report ran ok returned only Finance group but no WHERE clause was generated.

 

[synapsevampire]

Alan: you had to complete the formula with an end parenthesis, I didn't completely rewrite it, I just showed you how.

I have a FAQ in this forum about optimizing SQL pass through, but please just complete the formula, this will work.

-k

 

[Ngolem]

was the report correct?
did it run in a decent time?

I am not sure about why there is no where clause....but if further testing reveals no problems in data or report time...well????

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[synapsevampire]

Jim: If it doesn't pass the SQL, then it will likely eventually cause performance degradation.

The Switch function works well in most cases, but it occasionally flops too, which is why I wrote the FAQ and described the solution above.

Pass through works better in CR 9.

-k

 

[yehong]

Jim, It took same time (38 seconds) with both types of record selection formulae. So, I couldn't see any difference in performance while using SWITCH function.


Synapse, I tried your solution just to test the following part, and boom it returned same results in less than 10 secs and also a WHERE clause was generated.

(if {?GroupValue} <> 'No Filter' then {table1.dept_name} in {?GroupValue} else {?GroupValue} = 'No Filter')

Could you refine your original formula and fix that ) error?
I have tried using at different places still keeps giving me same error.