Solved

"Crystal Reports" parameter field - how can I check to see if the user entered anything?

Posted on 2004-09-03
10
228 Views
Last Modified: 2009-12-16
I'm using parameters to select/filter records in a report. There's about five different selections/filters the user can select. If the user does not select that filter, then I want to select all records for that filter. For instance, if the user does not select a Customer ID, then I want all Customer IDs.

I'm building the SQL 'Where' clause based on the parameter selection. I try testing the parameter for null and blanks, but it doesn't seem to work. The only solution I have so far is adding boolean parameter that states whether or not the driver parameter will be used. Then I can test the boolean parameter to know whether or not to include the driver parameter in the SQL. So, if the user wants to select by Customer ID, they have to set the boolean parameter to 'true', then select the Customer ID paramater and select the Customer ID.

This is how I am controlling the final WHERE clause. It works, but it's not pretty.

Any ideas?



0
Comment
Question by:teknorapex
10 Comments
 
LVL 13

Expert Comment

by:EwaldL
ID: 11979342
i's suggest to pass the five parameters to a stored procedure, which can then have some clever logic to return the data you are interested in.

alternatively, it should be possible to write a conditional where clause in crystal, but this would pull all data to the client and would only then be filtered. so that's wont be too efficient....
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 11985877
Since you asked this in the programming TA, I assume you are using an application to run the report.  If this is so, then build the report using all records (no filters or record selection formula).

In the application build the where cluase based on user entry.
Here is what we do from VB.  
eg

If (txt_CustomerID <> "") then
   strSQL = "{View.CustomerID} = '" & txt_CustomerID.Text & "'"
endif

If (txt_NextOption <> "") then
   strSQL = strSQL & " AND {View.NextOption} = '" & txt_NextOption.text & "'"
endif

etc for each option

If left(strSQL,4) = " AND" then
   strSQL = right(strSQL,len(strSQL)-4)
endif

crRpt.RecordSelectionFormula = strSQL

open the report

mlmcc
0
 
LVL 4

Expert Comment

by:sgalzin
ID: 11991225
hi,

i'm not sure this is your problem, but in crystal reports you need to beware of null values. if you write a statement on the same line as a conditional test for a null statement, the script stops ...

try doint this for a test :

if ( isnull ( {yourParameter} ) ) then
("")
else
( {yourParameter} )

you can save this formula as getYourParameter. when you call it, it will either return "" if the string is null, or the string you want to retrieve. you can then use this fomula (instead of your original parameter) for all your tests.

good luck,

stephane.
0
 
LVL 13

Expert Comment

by:bochgoch
ID: 12045540
To apply your filters on the Crystal Report (in the 'Edit Selection Formula') itself. Pass all parameters over to the report from your front end, then to apply the parameters use:

STRINGS:
(if length({?stringparameter}) > 0 then {field} in {?stringparameter} else {field} like '*')

DATES:
({Datefield} >= Date({?dateparameter}) OR {Datefield} = Date(0,0,0))

Date(0,0,0) = null date

Hope that helps....
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:teknorapex
ID: 12048352
It seems that I can test for null or blanks if the parameter is defined as a discrete, but not if it's defined as range.
0
 
LVL 13

Expert Comment

by:bochgoch
ID: 12055077
Depends on exactly what database your connecting to / what frontend you're using, but...in general I don't use range parameters but instead express a range using discrete values passed as separate parameters (x & y) and then using BETWEEN x AND y  or IN (x,y) -- obviously this only works where you've got a set number of parameters, to handle variable numbers of parameters use the IN clause and preformat a parameter on your front-end into a string containing comma separated values.

For example, you allow selection of 1+ values in a listbox, process these into a string in the format:



Pass this string as a parameter, then apply



0
 
LVL 13

Expert Comment

by:bochgoch
ID: 12055123
Sorry -- add to my above comment:

For example, you allow selection of 1+ values in a listbox, process these into a string in the format:

1,2,3,4 < assuming 1 2 3 & 4 are the values selected in your listbox.

Pass this string as a parameter, then apply as an IN clause:

{field} IN ([?parameter]) which would 'decode' as {field} IN (1,2,3,4)

This way null handling shouldn't be a problem.

Helpful?




0
 

Author Comment

by:teknorapex
ID: 12059357
I have a solution. I need to use the Minimum & Maximum function against the parameter. For instance:

if length(minimum({?Customer ID})) > 0 or length(maximum({?Customer ID})) > 0 then
"The user entered somthing"
else
"The user did not enter anything"

Thanks for everyone's help.
0
 

Accepted Solution

by:
ee_ai_construct earned 0 total points
ID: 12102292
Please read entire thread.  Asker solved or content important.
Closed, 250 points refunded.
ee_ai_construct (replacement part #xm34)
Community Support Admin
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
The purpose of this article is to demonstrate how we can use conditional statements using Python.
The goal of this video is to provide viewers with basic examples to understand and use switch statements in the C programming language.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.

706 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

20 Experts available now in Live!

Get 1:1 Help Now