Solved

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

Posted on 2004-09-03
10
231 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help with my python script 6 154
Making a javascript parser in javascript language 3 64
has22 challenge 11 80
countClumps  challenge 10 107
Navigation is an important part of web design from a usability perspective. But it is often a pain when it comes to a developer’s perspective. By navigation, it often means menuing. This is less theory and more practical of how to get a specific gro…
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 …
The goal of this video is to provide viewers with basic examples to understand and use conditional 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.

910 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

25 Experts available now in Live!

Get 1:1 Help Now