Optional Parameters

[xp8103]

This ought to be easy, so easy that I can't seem to figure out how to do it. I have a report with 5 parameters, all optional. If I enter nothing, I want everything. Or atleast, if I enter a wildcard, I want everything. With 5 parameters, if I leave one out I get nothing.
What the heck am I missing?

 

[LV]

Are all of these parameters participating in the report record selection formula?

 

[Roadkill150]

Hello xp8103, try this:

To set your selection formula so it will ignore a parameter field set to a blank value, modify your selection formula to the following:

({Table.Field1} = {?parameter1} or {?parameter1} = "&quot and
({Table.Field2} = {?parameter2} or {?parameter2} = "&quot and
({Table.Field3} = {?parameter3} or {?parameter3} = "&quot


cheers,
-Bruce Seagate Certified RCAD Specialist.
-Bruce Thuel-Chassaigne
roadkill150@hotmail.com

 

[xp8103]

Bruce! That was it! Many thanks. Oddly enough. The Crystal manual doesnt say anything about it.

Thanks!

 

[xp8103]

Bruce,
I need to qualify my "That was it!" It was almost it. By including fields in the select, the report will only pick up records with values in the fields in the select. Either ALL the records with values or those that I specify in the parameters. However, it wont give me records where some of the values in the records null. The SQL data is null in both string and int fields. How can I properly select these records as well?

 

[Trainingjason]

Another solution is to place a * as the default setting in the parameter field and then to place all the parameters in the selection as Like


Regards



Jason

 

[Roadkill150]

Hello,
For number fields, you can also set the default value to 0 and use the formula :
{Table.Field1} = {?parameter1} or {?parameter1} = 0)
-Bruce Seagate Certified RCAD Specialist.
-Bruce Thuel-Chassaigne
roadkill150@hotmail.com

 

[xp8103]

Bruce,
Acutally, the select formula editor wont allow you to do anything else. If you try to put
{Table.Field1} = {?parameter1} or {?parameter1} = "&quot when Field1 is an int, the editor will give an error. But I have the same problem. I get either ALL the records with values (not nulls) in the field or only those that I select. I don't get the one's with nulls.

 

[synapsevampire]

This can be annoying, but it's fairly easy to overcome.

BTW, this would be MUCH easier to explain were you to have provided the parms, database field names and their data types.

I use a standard sort of methodology which does 2 things, it assures that I pass through SQL, and it allows for all or selected values, here's an example of how I construct the Record Selection Criteria, note that I have a fairly complex model wherein the start and end date ranges are computed from formulas, and the parameters are multiple parameters (hence the [1] subscript) which have a default of "All" to return all rows, though I also return all rows if they pass nothing:

(
({MyTable.UTCTIME} >= {@Date Range Start})
and
({MyTable.UTCTIME} <= {@Date Range End})
)

and

(if {?Node}[1] <> 'All'
and {?Node}[1] <> ''
then
({MyTable.NODENAME} in {?Node})
else
if {?Node}[1] = 'All'
or {?Node}[1] = ''
then
true)

The parens are important, don't cheat this because it looks like overkill, it is intentionally formed to provide the best odds of getting pass through. Note that parameters NEVER create a NULL, so you don't want to check for null.

If the above post doesn't match your criteria, do us a favor and post your record selection criteria, it's hard to guess at what you might be constructing, and for what purpose.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[xp8103]

-k
Here is what I am looking at:
({Request.Id} = {?LR} or {?LR} = 0) and
({Document.LD_Number} = {?LD} or {?LD} = 0) and
({Document.Instrument_Type_code} = {?Law Type} or {?Law Type} = &quot;&quot and
({Document.Chapter_Number} = {?Chapter Number} or {?Chapter Number} = 0) and
({Legislative_Session.Abbreviation} = {?Session} or {?Session} = &quot;&quot

where Request.Id, Document.LD_Number, and .Chapter_Number are int and the rest are string.