Parameter is null

[jbarbato]

Hi there. I am using crystal reports xi.

how do i program in select expert for a field to match to all values if nothing is chosen for a parameter?

Example - I have a parameter {?Discipline_Group}
I would like to have Discipline_Group in the report display for all selected {?Discipline_Group}. If nothing is selected for {?Discipline_Group}, then I would like all Discipline_Group to display.

I am not using Crystal to generate a list of values - that is done with our flex application.

Thanks!

 

[Naith]

I think if you just enter a blank parameter, the value is "", as opposed to a true null. I haven't tested this though. Just speculating.

(
 if ({?Discipline_Group} <> "" then
     {TABLE.DISCIPLINE_GROUP} = {?Discipline_Group}
 else if {?Discipline_Group} = "" then
     true
)

If this doesn't act as you expect, try adopting a default value of "ALL", and adapting the above formula to say "ALL" where it says "".

Naith

 

[Turkbear]

Hi,
It is best in Crystal to test for NULL first, then for any value so:

If (
Trim({?Discipline_Group} = ""
or IsNull({?Discipline_Group})
 ) Then
True
Else
If (
 Trim({?Discipline_Group} <>"" 
  and Not (IsNull({?Discipline_Group}))
)
then
{TABLE.DISCIPLINE_GROUP} = {?Discipline_Group}

Based on Synapse's ideas, I use explicit If..Then..Else..If ..Then..Else
instead of just If..Then..Else..







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Naith]

Sound approach, Turk, but now that I've had a look, it isn't possible for a string parameter to be null.

 

[kwalters110]

In your record seletion try this:

{TABLE.DISCIPLINE_GROUP} Like {?Discipline_Group} + "*"

If they leave the parameter blank then it should select all matching records. Be careful though, if you have 2 similar groups such as: GroupA and GroupA1. If the user selects GroupA then both groups will be selected. If that isn't an issue then this might do the trick.

 

[Naith]

The performance might be a bit of a bugger, too, being as "LIKE" will bypass any indexes.

 

[synapsevampire]

Naith: I was surprised to learn that a Like predicate can have better performance than a IN clause on SQL Server, so that's not always the case.


I suggest that the solutions offered here may bite you later on, especially if you use code or CE as they treat parameters and nulls differently.

The standard is to use prepopulate the parameter with a default, such as "All", and then build the record selection as:

(
If {?Discipline_Group} <> "All" Then
{TABLE.DISCIPLINE_GROUP} = {?Discipline_Group}
Else
If {?Discipline_Group} = "All" Then
true
)

Note that the approach with passing {table.field}+"*" is also overkill and an approach taken by lazy SQL coders, one should either pass a meaningful criteria, or nothing, the only thing that could result from that approach is that you could trip up the optimizer, there can be no pluses.

If one decides to later pass this from CE it works fine, however with lazy front end coders passing parms, they may just pass a null which you'd have to check for, but I always write their spec to enforce that they send "all" or real parms to avoid this.

-k

 

[Naith]

From an Oracle perspective, I would also be surprised if that were the case. LIKE shouldn't ever use indices for a full wildcard, unless applied to a partial string, and the wildcard is at the end of the string, in which case IN would also use the same index.

Are you sure you don't mean "NOT IN", which also can have horrendous overhead.

Naith

 

[synapsevampire]

NOT IN is generally a bad idea as well, a minus query usually performs better for the same requirement.

I'd appreciate seeing any information you can find published on this Naith as I'm currently in an Oracle environment.

-k

 

[Turkbear]

Hi,
Oracle, if it sees a full wildcarded where clause ( that is, where Somefield like '%' , and no other criteria ) , will most certainly use a full table scan since the % of records returned will be above the CBO's threshold for using any index - that clause is just like running the query without any criteria...

Oracle's use of indexes is controlled ( in the recent versions) by the Cost Based Optimizer ( CBO) - it,in turn, relies of regular updating of the Statistics on tables, so the tables most used need to be analyzed on a regular basis.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

There are many known myths put out by Oracle and other experts, one of which is that a select field1, field2, ...etc... from table is better than select * from table.

The performanbce is the same.

If someone passes a where clause of where field like % then you have a competency problem, not an Oracle concern, addres it as such.

I often switch backends, so the more subtle nuances are lost, however I remember being *educated* on an Oracle contract by a Crystal person about the use of wildcards as I'd suggested that they explicitly state the list by using IN, and they came back with an article from Oracle stating otherwise.

I can't recall the oracle version, nor the articles whereabouts, however our testing confirmed that it was at least as fast.

Annoying because it went against what I recalled as being the case years ago.

-k

 

[Turkbear]

Hi,
Its not the field list but the where clause that determines whether an Index will be used...

Using an Indexed field in your where clause ( or, especially, in your Joins) can greatly improve performance, assuming that using the Index is determined by the CBO to be the most efficient way to get the records..






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

I understand, I hadn't stated anything about the select line determining an index, I was speaking to myths in general.

Again, if they pass where field = % then train them, that's the work of a lazy coder.

-k

 

[jbarbato]

I went with the first option provided by naith - thanks to all for your advice.

i cannot use the "all" because we are passing in the parameters from a different application build with macromedia flex. we have a field in our database that contains a select statement that generates our list of values for a parameter from the database (oracle10g)

since we are setting up the parameter list of values outside of crystal, i am running into a little bit of trouble with the parameter editor - if i do not have a blank field added into the value/description area of the parameter editor, crystal is making me select a value for the parameter. it is a "multiselect" parameter, so if the user chooses another value, the blank value is still there, and all records are returned.

i could test what happens through enterprise, but that is not going to be an efficent way to test parameters in our reports...

any way that i can not add a blank field in the value/description area of the parameter editor and run the report with nothing specified as a parameter value? if specifying "all" is the only way to go, i can edit the database, but that would be a headache.

 

[Naith]

The select list would affect whether an index was to be used or not if the index concerned was clustered, otherwise, as Turk says, it's all about the where clause.

Anyway, I think we're usurping this poor guys thread to wax lyrical about stuff he's probably got zero interest in.

Sorry about that jbarbato. Back to the case in hand...

(
 if ({?Discipline_Group} <> "" or ubound({?Discipline_Group}) > 1)then
     {TABLE.DISCIPLINE_GROUP} = {?Discipline_Group}
 else if {?Discipline_Group} = "" ubound({?Discipline_Group}) then
     true
)

All the best,

Naith

 

[Naith]

There's an "and" missing between the last "" and ubound. Guess who didn't test...

 

[Naith]

*sigh*. I don't know where my mind's at. Here is the correct formula in it's entirety.

(
if ({?Discipline_Group} <> "" 
    or ubound({?Discipline_Group}) > 1) 
then
    {TABLE.DISCIPLINE_GROUP} = {?Discipline_Group}
else 
    if {?Discipline_Group} = "" 
    and ubound({?Discipline_Group}) = 1 
    then true
)

TGIF

 

[stephcrystaluser]

Hi All,

I am also running into the same issue - and I understand the "if then else" you've put here - problem is as jbarbato stated, it seems you HAVE to select a value from the parameter list.

I have a Dynamic parameter that selects values from a database, there is no "All" value or blank value to select from in my list.

I even spoke with someone at Crystal and they are telling me I have to add "All" (or some value) to my database so that the user can select it from the list and I can use a formula like you guys suggested to handle it. Ugly if you ask me.

Since adding the "All" value to the database is not an option I created a Command Object instead that unions a select 'All' from dual. I still think it is ugly.

If I am missing something here, and there is a better way - please let me know!

Thanks!
steph

 

[jbarbato]

this seems to work for me for both single select and multi select parameters...

if the parameter is a string...
if ({?Discipline_Group} = "" then true
else {TABLE.DISCIPLINE_GROUP} = {?Discipline_Group}

or if the parameter is a number...
if ({?Discipline_Group_ID} = 0 then true
else {TABLE.DISCIPLINE_GROUP_ID} = {?Discipline_Group_ID}

It would have been much better to have the option of passing null for the parameter. It would have been even more efficent if our application did not pass the parameter at all... Hopefully in the next version Crystal takes this into consideration.

So now I have worked around this issue with numbers and strings, but I have a bigger problem with dates. With every string parameter, I can pass in an empty string, and it works the same no matter what the parameter is. With every number parameter, I can pass in a 0 and it works the same, no matter what the number is.

If a user does not select a date, it needs to be handled differently for each parameter - We have Two parameters that take dates...

{?Date_From} - If the user enters a date, the report should return all items with dates after {?Date_From} If nothing is entered, all items should be returned.

{?Date_To} - If the user enters a date, the report should return all dates before {?Date_To} if nothing is entered, all items should be returned

There is no date that I can enter that would serve as a value that would return all dates for both {?Date_From} and {?Date_To}. If anyone has any suggestions about this, i would greatly appreciate it - i am on my last nerve