Recored Selection formula

[busi1]

Hi,

I have 5 parameters
1) OwnerName
2) Offerings
3) from date
4) to date
5) status

All the parameters should be optional except from date and
to date.

These are the conditions how i select the parameters

1) name, from date and to date
2) offering, from date and to date
3) status, from date and to date
4) name , offering,from date and to date
5) name, status, from date and to date
6) offering, status, from date and to date
7) from date and to date

Here is the record selection formula i wrote, which is not working

{A.Org Class}<>"Mark" and
{A.BEGIN_DATE} in {?From} to {?To} and
(
if {?Owner}<> "None" then
{A.last name}={?Owner} else
if {?Owner}= "None" then
true
) and
(
if {?Offering} <> "None" then
{A.Offerings}={?Offering} else
if {?Offering} = "None" then
true
) and
(
if {?Status} <> "None" then
{A.Status}={?Status} else
if {?Status} = "None" then
true
)


i am using crystal v 10.0 and oracle 9
can anyone help me out?
Thanks

 

[synapsevampire]

Assuming that the NONE is the default value for each parameter, it looks pretty good.

(
{A.Org Class} <> "Mark"
)
and
(
{A.BEGIN_DATE} in {?From} to {?To}
)
and
(
if {?Owner}<> "None" then
{A.last name}={?Owner}
else
if {?Owner}= "None" then
true
)
and
(
if {?Offering} <> "None" then
{A.Offerings}={?Offering}
else
if {?Offering} = "None" then
true
)
and
(
if {?Status} <> "None" then
{A.Status}={?Status}
else
if {?Status} = "None" then
true
)

Then check the Database->Show SQL Query to learn what is being passed to the database as a result.

-k

 

[busi1]

Hi synapse,

i want to use this statement

(
{A.BEGIN_DATE} in {?From} to {?To} or
(A.END_DATE} in {?From} to {?To}
)

when i am including the or statement the record selection formula is not working


(
{A.Org Class} <> "Mark"
)
and
(
{A.BEGIN_DATE} in {?From} to {?To} or
(A.END_DATE} in {?From} to {?To}
)
and
(
if {?Owner}<> "None" then
{A.last name}={?Owner}
else
if {?Owner}= "None" then
true
)
and
(
if {?Offering} <> "None" then
{A.Offerings}={?Offering}
else
if {?Offering} = "None" then
true
)
and
(
if {?Status} <> "None" then
{A.Status}={?Status}
else
if {?Status} = "None" then
true
)

 

[lbass]

You need to explain what you mean by "not working". If you are getting unexpected results, please explain what they are. I see nothing particularly wrong with your selection formula.

-LB

 

[Madawc]

Rather than using a single complex formula to select, break it down into its component part. Try it as 'boolians', formula tests without an 'if'. Put these in a test version of the report and see which of the tests is not working as you expect.

I'd suspect nulls, maybe one of the dates can be null and needs a test like isnull({A.BEGIN_DATE})

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[busi1]

Hi Ibass,

Not working i mean, not getting the expected results as per the criteria i mentioned above.

for example:
These are the conditions how i select the parameters

1) name, from date and to date
2) offering, from date and to date
3) status, from date and to date
4) name , offering,from date and to date
5) name, status, from date and to date
6) offering, status, from date and to date
7) from date and to date

record seletion formula is working fine when i use only
{A.BEGIN_DATE} in {?From} to {?To} along with other criteria.

But when i use this
{A.BEGIN_DATE} in {?From} to {?To} or
{A.END_DATE} in {?From} to {?To}
its pulling all the records irrespective of the conditions i use in selecting the parameter.

Help is appreciated.

Thanks

 

[lbass]

If you entered your record selection as shown earlier, with all the parens, then you should get the correct results. Are you showing the entire statement or are there other criteria where you use an "or" statement?

I'm assuming you realize that when you use the "or" statement on the dates that you will get begin dates outside of the range when the end date is in the range, and that you will get end dates outside of the range when the begin date is in the range. To understand better what's going on, you might want to take a look at the detail section containing these fields.

-LB