[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


Handling multiple optional parameters in Crystal Reports.

Posted on 2006-07-03
Medium Priority
Last Modified: 2012-08-13
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
Question by:Vasanthsameena
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3

Expert Comment

ID: 17036543
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).


Author Comment

ID: 17039827
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

Expert Comment

ID: 17040545
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.

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.


Author Comment

ID: 17048112
I also need to diaplay the parameter in my report. So setting any default value would be a problem

Expert Comment

ID: 17048555
Create a formula in your report based on the user input to achieve this.

If LEN(@ToDate) = 0 Then
   "no date set"

Author Comment

ID: 17056285
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

Expert Comment

ID: 17056389
Glad I could help!

Accepted Solution

DarthMod earned 0 total points
ID: 17470095
PAQed with points refunded (400)

Community Support Moderator

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question