Parameter to pull all records when not used 2

[MaumeeScott]

I would like the report to pull all records when the parameter is not filled in. Essentially the user should be able to enter a parameter value and get the results desired or ignore the parameter and get all data. I need this to work on multiple parameters, be able to be passed to a sub-report, and the field that the parameter is written against may or may not have nulls. Any help is greatly appreciated.

 

[Turkbear]

Hi, In general try:

If ({?parameter} = "" or IsNull({?parameter}) then
 True
else
{field} = {?parameter}

Repeat as needed for each parameter..

 

[lbass]

I sometimes when creating default values for a parameter click on the right arrow without entering anything. This enters a "" as a default value. If you position this at the top of the default list, ignored parameters will default to all records. (You could also enter "All"--same principle). You would use a record selection formula like:

{?parm} = "" or
{table.field} = {?parm}

To get the same values passed to the subreport, go to edit subreport links and move {?parm} to the right and then choose the same {table.field} in the subreport as the field to link on.

-LB

 

[crystalreporting]

Or - how about making the default value for the parameter an asterix (*) - then, in your selection formula simply change your existing '=' to a 'like'. This serves a couple of purposes - it solves the problem you have, and allows people to enter wildcard values for the field if they don't know the value.

Peter Shirley

http://www.linkcorp.com

 

[Turkbear]

Hi,
IIRC, using the 'True' method I posted will be more efficient when no value is provided by the parameter, since no selection criteria for that field will be passed to the database..

Any 'test' will require checking each record to see if it 'matches' the criteria.

To be sure, however, use the 'Show Sql' option after trying each of the methods posted...The one that does not send a where clause using that field name will be fastest in most cases.

 

[skarra]

I have a realted question.. I would appreciate if you guys can help me regd this matter.

-----------------

These are 2 different fields in the DB. One is a ID which is INT and another is the name which is string.

It is upon the user to select either one of the values and not both.. it can be either int or string. I want to pass that value from the windows form which i have created to CR to get me the reports. How can i do this? What do i have to write?

I am using C#.NET 2003, SQL server as DB and CR.NET which comes along with the VS.NET 2003.

 

[SamSharma]

I cannot get this to work with date parameters (specificallly a date range)
It gives me no error message but selects no records.

(if isnull({?daterange})
then True
else
{tblAnalysis.SessionStartTime} = {?daterange})

this did give me an error
if {?daterange} =

 

[Madawc]

I'm puzzle to see you compairing a time or date-time to a date. Also is the error something you got from coding or at run-time? Maybe

if not isnull({?daterange})
and
{?daterange} <> ""
and
Date({tblAnalysis.SessionStartTime}) = {?daterange})

Also did you define your parameter as a date?

It helps to give your Crystal version, since newer versions have extra options, and some extra problems. I use Crystal 8.5.

it would also have been better to start a new thread, maybe with a reference to this one, as thread766-836315.


Madawc Williams (East Anglia, UK)

 

[lbass]

I think the date parameters always default to the currentdate, so the parameter will never be null. So, try something like:

if minimum({?daterange}) = currentdate and
maximum({?daterange}) = currentdate then true else
if minimum({?daterange}) <> currentdate then
{table.date} = {?daterange}

-LB

 

[SamSharma]

To madawc, I will remember the advice,

It's Crystal 8.5, with data from SQL Server.

I didn't start a new thread because I found this thread through a search.

I assume a similar search will point the next person here.

And thanks to lbass, I would never have guessed that answer.