Parameter entered or not entered

[Roncayenne]

I am running CR8.5 against an Oracle d/b. I am running a query that used CR Sql designer to pull up records.
I have CR setup with 1 parameter. If the parameter is entered, I only want the records like that parameter like an account code. If the parameter is blank, not entered, I would like all the records.

Just started with CR, so hope this is not to simple to show how much I don't know!

Thanks for the help in advance!

Ron

 

[synapsevampire]

First, even Crystal doesn't recommend using their SQL Designer anymore.

That aside, try setting a default for the parameter of "All"

The record selection formula should be modified to:

If {?parm} <> "All" then
{table.field} = {?parm}
else
If {?parm} = "All" then
true

Note that the record selection formula is intentionally set up to fully qualify the else (rather than just else) to ensure SQL pass through.

-k

 

[Roncayenne]

This is what I have in the record selection:

{Query.'01'} = {?month} and {Query.FGBOPAL_FSYR_CODE} = {?year};

if {Query.FGBOPAL_ORGN_CODE} <> "All" then
{Query.FGBOPAL_ORGN_CODE}= {?Orgn}
else
if {Query.FGBOPAL_ORGN_CODE} = "All" then
true;

The month and year parms are in the SQL query. They are ok. I set the Orgn code default to 'ALL'. When I enter the month,year, and an orgn, I get data ok. When I enter the month and year only, I get no data. I must be missing something.
Ron

 

[Roncayenne]

Sorry, the default is set to 'All' not 'ALL'.

I am new to Crystal so can you tell me what is used to access an Oracle d/b and create SQL queries rather than the Crystal Designer?

 

[synapsevampire]

When you create/edit the parameter in CR, select set default values and enter "All" and hit the arrow key to make it the default value.

You were checking the field for the value of "All", not the parameter:
(
{Query.'01'} = {?month}
)
and
(
{Query.FGBOPAL_FSYR_CODE} = {?year}
)
and
(
if {?Orgn} <> "All" then
{Query.FGBOPAL_ORGN_CODE}= {?Orgn}
else
if {?Orgn} = "All" then
true
)

-k

 

[Roncayenne]

Got it! Sometimes ya look at this stuff TOO long!!
Thanks for the help...
Just getting into CR and have to get a book, having people like you respond, certainly is a great help and time-saver.

Again, can you tell me what is used now rather that Designer?

Ron

 

[synapsevampire]

You're changing the name here. First you said the CR SQL Designer, now it's the Crystal Designer, which I'm not sure what you mean.

Crystal can directly use tables/views/sp's by simply using the Database->Visual Linking Expert

I implement using database Views or Stored Procedures for every report for a few reasons, the most important ones being reusability and maintenance related.

Or when you start Crystal, select the report expert and you can then point at the data source(s) add and join the tables/views.

Make sure that any filtering ios passed to the database using the Database->Show SQL Query, and it should be fine.

-k

 

[Roncayenne]

On some reports I have gone into Crystal SQL Designer to make a query to limit the number of records that I have to report on. I guess another way would be to create a view and user CR to go against that view. Just starting with Crystal and with the users wanting the reports yesterday doesn't help trying various ways. I will get there eventually though.
Thanks for the help and the info!!

Ron