?
Solved

Handling multiple optional parameters in Crystal Reports.

Posted on 2006-07-03
9
Medium Priority
?
503 Views
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
0
Comment
Question by:Vasanthsameena
  • 4
  • 3
8 Comments
 
LVL 1

Expert Comment

by:SnyTek
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).

Cheers
Roger
0
 

Author Comment

by:Vasanthsameena
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
0
 
LVL 1

Expert Comment

by:SnyTek
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)

  AS

DECLARE @DateFromDte as datetime
DECLARE @DateToDte as datetime

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

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

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

--\\
SELECT *
FROM myview
WHERE OpenDate IS BETWEEN @DateToDte AND @DateFromDte
--//

Hope that's clear enough.

Cheers
Roger
0
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.

 

Author Comment

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

Expert Comment

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

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

Author Comment

by:Vasanthsameena
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
0
 
LVL 1

Expert Comment

by:SnyTek
ID: 17056389
Glad I could help!
Cheers
0
 
LVL 1

Accepted Solution

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

DarthMod
Community Support Moderator
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

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…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
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…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses
Course of the Month15 days, 23 hours left to enroll

850 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