Solved

Handling multiple optional parameters in Crystal Reports.

Posted on 2006-07-03
9
469 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
9 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: 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.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

838 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