Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
242 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Windows Script Host (WSH) has been part of Windows since Windows NT4. Windows Script Host provides architecture for building dynamic scripts that consist of a core object model, scripting hosts, and scripting engines. The key components of Window…
When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.

722 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