What part of this Record Selection

[crystalball]

What part of this Record Selection Formula is keeping it from being "pushed down" to the server. I am at a loss on this one.
Thanks to all

({?BusArea} = '*' or {DSSTTSLSCOMM.BUS_AREA} = {?BusArea}) and
({?Region} = '*' or {DSSTTSLSCOMM.REGION} = {?Region}) and
({?Branch} = '*' or {DSSTTSLSCOMM.BRANCH} = {?Branch}) and
({?Customer} = '*' or {DSSTTSLSCOMM.CUST_CODE} = {?Customer})

 

[synapsevampire]

What is it supposed to be doing with the *?

I assume that you're trying to eitehr select all rows or just what's in the parm, based on whether the parm has * in it, but it's unclear.

Try:

(
if ({?BusArea} <> '*' then
{DSSTTSLSCOMM.BUS_AREA} = {?BusArea})
else if
({?BusArea} = '*' then
true
)
and
(
if {?Region} <> '*' then
{DSSTTSLSCOMM.REGION} = {?Region})
else if
{?Region} = '*' then
true
)
and
...

You get the idea.

Crystal is very persnickety about SQL passing, note the addition of the parens and the fully qualified ELSE IF.

I do this to assure pass through, trust me, I spent a LOT of time working this stuff out....

-k

 

[crystalball]

synapsevampire -

Your assumption is correct. The * is intended to select all rows. Based on your recommendations, I re-constructed the record selection formula as follows. The GL_PERIOD and GL_YEAR is being pushed down, however, something is still amiss with the balance of the selection. Is it still a paren's issue? Also, I have since realized that I need the rtrim function. I have seen in some of your other posts that you were successfully passing the statements with functions.
Your help is very much appreciated, as I have been stuggling with this for some time now. -GG

This is what I am currently working with:

(
if {?BusArea} <> '*'then
(rtrim({DSSTTSLSCOMM.BUS_AREA}) = {?BusArea})
else if
{?BusArea} = '*' then
true
) and
(
if {?Region} <> '*' then
(rtrim({DSSTTSLSCOMM.REGION}) = {?Region})
else if
{?Region} = '*' then
true
) and
(
if {?Branch} <> '*' then
(rtrim({DSSTTSLSCOMM.BRANCH}) = {?Branch})
else if
{?Branch} = '*' then
true
) and
(
if {?Customer} <> '*' then
(rtrim({DSSTTSLSCOMM.CUST_CODE}) = {?Customer})
else if
{?Customer} = '*' then
true
) and
({DSSTTSLSCOMM.GL_PERIOD} <={?EndMonth}) and
{DSSTTSLSCOMM.GL_YEAR} in [{?EndYear}, {?prioryear}]

The above is generating this SQL:

WHERE
DSSTTSLSCOMM.&quot;GL_PERIOD&quot; <= '03' AND
(DSSTTSLSCOMM.&quot;GL_YEAR&quot; = '2002' OR
DSSTTSLSCOMM.&quot;GL_YEAR&quot; = '2003')

 

[synapsevampire]

I doubt that the rtim is getting you, but if you're certain...

Create a SQL Expression to rtrim the field, and use the SQL Expression in the Record Selection Formula, it should then pass the SQL.

-k

 

[crystalball]

synapse -
The expression is already there. Do you mean a formula, as below? -GG

(
if {?BusArea} <> '*'then
{@BusArea} = {?BusArea}
else if
{?BusArea} = '*' then
true
) and
(
if {?Region} <> '*' then
{@Region} = {?Region}
else if
{?Region} = '*' then
true
) and
(
if {?Branch} <> '*' then
{@Branch} = {?Branch}
else if
{?Branch} = '*' then
true
) and
(
if {?Customer} <> '*' then
{@CustCode} = {?Customer}
else if
{?Customer} = '*' then
true
) and
({DSSTTSLSCOMM.GL_PERIOD} <={?EndMonth}) and
{DSSTTSLSCOMM.GL_YEAR} in [{?EndYear}, {?prioryear}]

 

[crystalball]

OK. I have corrected many problems of my own making and have finally managed to push the entire record selection formula over to the server. The problem now is that approximately 3 out of 4 times that I run the report, it returns no data at all. When data is returned, it is correct and complete. Any ideas where to go from here?
Thanks -GG

(
if {?BusArea} <> '*'then
{%BusArea} = {?BusArea}
else if
trim({?BusArea}) = '*' then
true
) and
(
if {?Region} <> '*' then
{%Region} = {?Region}
else if
trim({?Region}) = '*' then
true
) and
(
if {?Branch} <> '*' then
{%Branch} = {?Branch}
else if
trim({?Branch}) = '*' then
true
) and
(
if {?Customer} <> '*' then
{%CustCode} = {?Customer}
else if
trim({?Customer}) = '*' then
true
) and
({DSSTTSLSCOMM.GL_PERIOD} <= {?EndMonth}) and
{DSSTTSLSCOMM.GL_YEAR} in [{?EndYear}, {?prioryear}]

The above is creating this WHERE clause:

WHERE (trim(DSSTTSLSCOMM.&quot;BRANCH&quot) = '13' AND
DSSTTSLSCOMM.&quot;GL_PERIOD&quot; <= '03' AND
(DSSTTSLSCOMM.&quot;GL_YEAR&quot; = '2002' OR
DSSTTSLSCOMM.&quot;GL_YEAR&quot; = '2003')