Solved

Parameter Validation in Crystal Reports

Posted on 2004-09-16
9
1,848 Views
Last Modified: 2011-10-03
Hi,
Is there a way we could validate the parameter values in Crystal report. For example one of the reports I have takes the start date and end date as parameters. After the user enters the start date and end date and before runnign the query is there way I could check if the end date is less than the start date.

I would like to do this

Take user parametrs
 if  end_date < start_date
  prompt the user to enter valid parameters
else
 go ahead and execute the query.


Thanks,
Nila
0
Comment
Question by:nila
9 Comments
 
LVL 28

Expert Comment

by:bdreed35
ID: 12078653
You can validate the parameters and override the end_date, but you cannot prompt the user to reenter then end_date, at least not without creating a custom front end.

This 3rd party software might be an option for you:
http://www.milletsoftware.com/DataLink_Viewer.htm

I can give you an example of validating the parameters in Crystal alone if you like, just let me know.
0
 
LVL 10

Expert Comment

by:ebolek
ID: 12078656
the only way to accomplish that is to do it in an application . Then you can do that. you check in your application if the end date > start date and pass it to the query otherwise ask the user to enter the parameetr again.

Regards
Emre
0
 
LVL 42

Expert Comment

by:frodoman
ID: 12078845
Something I've done before is to write a selection formula that will look at both dates and handle the earliest as the start date and the latest as the end date -- this isn't very efficient but it allows your report to run even if the user enters the dates in the wrong order.  If that's something you want to pursue I can help with that, otherwise (as already said) you'll need to write your own front end or use a 3rd party package.

frodoman
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 250 total points
ID: 12079202
I'm surprised that nobody has mentioned this, but here goes:

1)  Modify your record selection criteria to validate the data parameters entered:

(
If
  {?Thru Date} >= {?From Date}
Then
  {Table.Date} In {?From Date} To {?Thru Date}
Else If
  {?Thru Date} < {?From Date}
Then
  False
)

2)  Insert a second Report Header (Report Header b)

3)  Insert the following message into a Text Box in Report Header b:

The 'Thru Date' parameter of ?Thru Date is < the 'From Date' paremter of  ?From Date.  Please run the report again.

4)  Conditionally Suppress Report Header b where {?Thru Date} >=  {?From Date}

5)  If you want, conditionally suppress other sections you want with the condition {?Thru Date} < {?From Date}

Voila!  The report won't return any data (unless you have records where the End Date < Begin Date) and the users will be prompted to run the report again!

~Kurt


0
 
LVL 42

Expert Comment

by:frodoman
ID: 12079216
Nice idea, Kurt. - frodoman
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 12079242
BTW, the message should read:

The 'Thru Date' parameter of {?Thru Date is} < the 'From Date' paremter of  {?From Date}.  Please run the report again.

You'll need to create the text and then insert the parameters in the appropriate place.  You'll also need to format the parameters so that the dates appear as you'd like, ala:

The 'Thru Date' parameter of 09/15/2004 < the 'From Date' paremter of  10/01/2004.  Please run the report again.
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 12079253
Thanks frodoman:)
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 12079298
btw, the message text is 'parameter', not paremter (sigh).

I've used this method quite a bit.  I actually require all of my report developers to include an intelligent 'No Data Found...'-type message in all of their reports.  In my experience, users equate a blank report to a broken report.  By having the report clearly state that there is no data for the combination of parameters entered, I mitigate the risk of unecessary support calls.
0
 

Author Comment

by:nila
ID: 12079321
I agree. I have the No Data Found for the search criteria you have entered too. It elimiates many of the questions from user.

Thanks,
Nila
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
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…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

832 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