Solved

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

Posted on 2004-09-03
10
239 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
The purpose of this article is to demonstrate how we can use conditional statements using Python.
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 pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.

623 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