| Author |
Message |
nmullins1
Newbie
Joined: 03 May 2007
Location: United States
Online Status: Offline
Posts: 5
|
   Topic: Select Expert
Posted: 03 May 2007 at 7:38am |
|
I am writing a report with multiple Parameter fields that are used in the select expert to find the values in the database. The problem I am having is that if I have three values to be inputed into the parameters fields, but I only fill two of them out. How can I make it if only two parameters have values to make the third one a wild card so that it will allow any value.
|
 IP Logged |
|
|
EscApe
Newbie
Joined: 13 Nov 2006
Location: Finland
Online Status: Offline
Posts: 37
|
Posted: 06 May 2007 at 4:31am |
|
I would find it easier to answer, if you posted your current code to the forum.
BR,
Esc
|
|
IP Logged |
|
nmullins1
Newbie
Joined: 03 May 2007
Location: United States
Online Status: Offline
Posts: 5
|
Posted: 08 May 2007 at 7:05am |
Here is some code that I am trying to use so that if a value is null, then it will not be factored into the search
------------------------
if not (isnull({?Mill Style})) then ({UserDefinedFields.UDFVCMD2} = {?Mill Style}) and
if not(isnull({?Size})) then ({UserDefinedFields.UDFVCLD1} = {?Size})
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 08 May 2007 at 8:05am |
I would do this in three formulas:
If isNull({?Mill Style}) then true else ({UserDefinedFields.UDFVCMD2} = {?Mill Style})
if isNull({?Size}) then true else ({UserDefinedFields.UDFVCLD1} = {?Size})
Do the same sort of thing for your third parameter.
Then, in the select expert, use each of these formulas and select "Is True".
-Dell
|
|
|
|
IP Logged |
|
nmullins1
Newbie
Joined: 03 May 2007
Location: United States
Online Status: Offline
Posts: 5
|
Posted: 08 May 2007 at 8:45am |
how would you select the "is true" in the select expert
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 08 May 2007 at 12:09pm |
Because these formulas evaluate to True or False, when you select them in the Select Expert "is true" and "is false" will be options in the first drop down list, just like "is equal to", "is greater than", etc. Crystal knows what to put in that list based on the type of value that the selected formula or field contains.
-Dell
|
|
|
|
IP Logged |
|
nmullins1
Newbie
Joined: 03 May 2007
Location: United States
Online Status: Offline
Posts: 5
|
Posted: 08 May 2007 at 12:25pm |
|
I tried this and it did not work. I am still getting empty values if I have a value for the first parameter and no value in the second paramter.
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 08 May 2007 at 1:02pm |
What do you mean by "empty values"? What do you expect for results?
What are the exact formulas you've set up? Also, please click on the "Show Formula" button in the select expert and post the formula here. That way I have the whole picture of what you're doing so that I can better help you get the results you want.
Thanks!
-Dell
|
|
|
|
IP Logged |
|
nmullins1
Newbie
Joined: 03 May 2007
Location: United States
Online Status: Offline
Posts: 5
|
Posted: 08 May 2007 at 1:22pm |
What I mean by empty values are values that are Null or there has been no entry for that parameter field. So if there are no values entered for that parameter field, then it should find all values in the database for that column.
I set up the two formulas exactly as you did above.
This is the formula out of the select expert
Ex
I have a field for department and managers and had a parameter field for both. I only want to look up a certain department so I put in "IT" for the department but I put no value in for manager, and this should give me all the managers for that one department.
Also, I appreciate all the help
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 08 May 2007 at 1:58pm |
I don't see any reason why this shouldn't be working.... :( Do you have anything else in your select expert?
I would break this down to see where the problem is...
1. Working from the parameters directly, use first one parameter by itself in the select expert and then the other by itself. Does this get you the results you expect?
2. Change the select expert to use first one formula and then the other. Use the same parameter values as you used in step 1 - don't leave them blank yet! Do the results match those from step 1? If not, then you'll need to tweak the formula(s) to get the results to match up.
3. Again, using just one formula and then the other, try running the report with a blank value in the selected parameter. Does your report show ALL of the data? If not, you'll need to tweak the formula(s) to get the data you expect when there's no value in the parameter. It may be that the value for a blank parameter is not actually "null", it may be an empty string (which is VERY different from null). In which case you'll have to check for that in your formulas instead of null.
4. After you've gone through all of the above and you're sure that both formulas work correctly, put them both back in the select expert and your report should work.
-Dell
|
|
|
|
IP Logged |
|
|
|
