Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2004-09-03
10
Medium Priority
?
244 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 101

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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

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.

Question has a verified solution.

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

Having just graduated from college and entered the workforce, I don’t find myself always using the tools and programs I grew accustomed to over the past four years. However, there is one program I continually find myself reverting back to…R.   So …
This article will show, step by step, how to integrate R code into a R Sweave document
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.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.
Suggested Courses

916 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