Handling multiple optional parameters in Crystal Reports.

Hi experts,

I am having trouble in handling multiple parameters in crystal reports all of which are optional.

The follwing is the best analogy of what i am supposed to do.

from date:: the date from which the record are to be fetched
to date:: the date upto which the records are to be fetched
job:: job parameter where records fetched are of specific job
deptno:: records required of specific deptno
ename:: record of that employee to be fetched.

the user can pass either a single parameter or all the above.passing more parameters wil help in filtering his requirement.

If only from date is passed the all records which have date of joing after that date should be fetched
If both from and to are mentiond then date of joining is supposed to between those dates.

I hope my problem is clear.I am using Crstal reports 6.5
Who is Participating?
DarthModConnect With a Mentor Commented:
PAQed with points refunded (400)

Community Support Moderator
How do you access your data? Are you using stored procedures?

I've done the parameter handling in the stored procedures because my queries are a bit complicated sometimes and I wanted to have one place where data is filtered.

You can define what you want to pass for optional parameters. I took an empty string and negative numbers and treat the parameter accordingly (e.g. empty Job string => show all Jobs).

VasanthsameenaAuthor Commented:
I am using a view based on tables from which i need to filter using these parameters , How can we do it using stored procedures, especially the one where i need to use the date range.I was unable to put logic if only from date is given instead of from and to date.for other parameters i could handle it by using nvl function
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

If you don't have a ToDate you have two possibilities:
- if you have defined the dates in the SP as datetime -> give a high date value to the stored procedure, e.g. 1.1.9999
- if you are using strings for the date (which I did last time) -> pass an empty string, check in the SP for the content length. If it is zero use the high date, otherwise convert the string date into a datetime.

@DateFrom as varchar(50)
@DateTo as varchar(50)


DECLARE @DateFromDte as datetime
DECLARE @DateToDte as datetime

    SELECT @DateFromDte = CONVERT(DATETIME, @DateFrom, 101)

    IF LEN(@DateTo) > 0
        SELECT @DateToDte   = CONVERT(DATETIME, @DateTo, 101)
        SELECT @DateToDte = CAST(@DateTo as datetime)

You then have two datetime variables which you can use to filter the query

FROM myview
WHERE OpenDate IS BETWEEN @DateToDte AND @DateFromDte

Hope that's clear enough.

VasanthsameenaAuthor Commented:
I also need to diaplay the parameter in my report. So setting any default value would be a problem
Create a formula in your report based on the user input to achieve this.

If LEN(@ToDate) = 0 Then
   "no date set"
VasanthsameenaAuthor Commented:
HI Thanks for your support.I was able to find the solution.I have set the default date value to be the minimum of all the date values and max of all the date values and used nvl function to handle the optiional parameters all this was done in the SP.
Thanks a lot
Glad I could help!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.