Help on Sql where statement for Crystal Reports 8.5

[dwalsh514]

Hello All,

I'm trying to establish a record selection formula to use against an Oracle 8.0 database using Crystal Reports 8.5

I've tried to read up on it and use some ideas from fellow posters, but I can't seem to get the record selection to work here's what I have in my Record selection formula.

(
{WORKORDER.WOPM1} = {?Plant}
)
and
(
If {?Worktype} <> "NONE"then
{WORKORDER.WORKTYPE} = {?Worktype}
else If {?Worktype} = "NONE"then
true
)
and
(
If {?PHONE} <> "NONE"then
{WORKORDER.PHONE} = {?PHONE}
else If {?PHONE} = "NONE"then
true
)
and
(
If {?STATUS} <> "NONE"then
{WORKORDER.STATUS} = {?STATUS}
else If {?Status} = "NONE"then
true
)

I'm trying to use a parameter field to aid in the users selection. I want to use "NONE" as a keyword to act as a skip the parameter function. In the Database there doesnot exist a "NONE" selection.
When I check the Show SQL statement, I don't see anything relevant to the Record Selection.

Show SQL
SELECT
WORKORDER."WONUM", WORKORDER."STATUS", WORKORDER."WORKTYPE", WORKORDER."DESCRIPTION", WORKORDER."WOPRIORITY", WORKORDER."PHONE", WORKORDER."WOPM1",
ASSIGNMENT."LABORHRS", ASSIGNMENT."LABORCODE"
FROM
"GAFMAX"."WORKORDER" WORKORDER, "GAFMAX"."ASSIGNMENT" ASSIGNMENT
Any ideas on what I might be doing wrong?
Thanks,
d.

 

[synapsevampire]

If they select NONE and nothing shows in thw SQL then it's doing it's job, right?

So anyway, what is it that it's not doing that you expect it to?

The part that bothers me is:

(
{WORKORDER.WOPM1} = {?Plant}
)

That should always be generating a WHERE clause, and your SQL doesn't show one?!?!?

What are the data types of each?

Anyway, your statement is wrong, if they enter NONE you should NOT get sql, it results in TRUE in the record selection which means do nothing to filter those...

-k

 

[dwalsh514]

Thanks synapsevampire for replying.

My goal is to have 4 parameters for the user to choose from. But, there may be a case when 3 out of the 4 parameters are not desired by the user. I found that if I use and "=" comparing the parameter to the database field, the parameter value has to match exactly to what is in the database field. I'm trying to find a way for the user to optionally use 3 of the parameters if the want too.

As far as the Show SQL not showing what I have in the record selection is mind boggling. It should be showing it correct? I can't find anyting wrong with the statement myself...
All of my data types are strings.

 

[synapsevampire]

You didn't read my post.

If you select NONE, then nothing is passed to the database, which is what you want, right?

Show what you are entering in each parameter and what the resulting SQL being generated is.

It is not mind boggling at all, think about it, if you enter NONE it should NOT pass anything.

-k

 

[dwalsh514]

Once again, Thank you for your patience with me. Ok, I got it to perfom as desired. I'm able to vary my parameters to obtain desired results.

As you can tell from my postings I'm very new to Crystal, I have the report behaving as I want it too. Now, how can I set the report up so when a user opens the report file, all they can do is enter parameters and print? If it is possible to just do that.

 

[synapsevampire]

You haven't shared how the users are executin g the report.

If they are using the Crystal Reports product, it is a designer, so they have all of the options that a designer should.

There is a EXE generator available in CR 8.5, but they can view, export, print, email, etc.

I can't think of a means to prevent people from doing what the majority of people want without using a 3rd party product, and even then it's unusual for people to just want to print these days, save the trees and all.

Besides, if you're thinking that limiting them to only printing buys you anything towards security, consider that there are various print drivers that allow for printing to a file in numerous formats, so they can always get around this anyway if they can modify printers.

-k

 

[synapsevampire]

I should have also mentioned that you could do this if you have the Developer edition and write code.

Also you've deviated from the original topic, if you wish to pursue this please start another thread, the purpose of the forums is to help people resolve specific problems, allowing for tailored searching, not provide a blog of one persons efforts towards implementing and learning Crystal.

Keep in mind that it isn't a development language, it's basically a nifty presentation layer, but it doesn't really allow for the types of things a development language does.

-k