Solved

Handling multiple optional parameters in Crystal Reports.

Posted on 2006-07-03
9
455 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:Vasanthsameena
Comment Utility
I also need to diaplay the parameter in my report. So setting any default value would be a problem
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Expert Comment

by:SnyTek
Comment Utility
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
Comment Utility
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
Comment Utility
Glad I could help!
Cheers
0
 
LVL 1

Accepted Solution

by:
DarthMod earned 0 total points
Comment Utility
PAQed with points refunded (400)

DarthMod
Community Support Moderator
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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…
This video discusses moving either the default database or any database to a new volume.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now