[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • Last Modified:

Ignore parameter if no value entered

Crystal Reports IX
SQL 2014

I have a report with multiple parameter values.  There are four parameter's that check on single field.  I want to allow the report to ignore any of the second, third or fourth parameter's that are left blank if they are not needed in the query.

First the field I wish to search:
Table: CaseMaster
Field: UCRCodeAll  varchar()   Allows Nulls but would need to check for both NULLS or ""

A hardcoded query looks like this:
(({CaseMaster.UCRCodeAll} like "*8028*") or ({CaseMaster.UCRCodeAll} like "*5060*"))

A Parameter based query that requires the user to enter a value.
(({CaseMaster.UCRCodeAll} like "*" & {?UCR Code 1} & "*")
or ({CaseMaster.UCRCodeAll} like "*" & {?UCR Code 2} & "*"))

UCRCodeAll data looks like this:

     UCRCodeAll
            8028
            5060,8028
            5060,0100,4002
            0100,3534

Data would be returned on the 1st - 3rd row.


The parameter fields are:
UCR Code 1
UCR Code 2
UCR Code 3
UCR Code 4
0
MikeM670
Asked:
MikeM670
  • 10
  • 8
  • 4
2 Solutions
 
mlmccCommented:
What version of Crystal?

CR 2008 (v12) and later allow for optional parameters.

They have a special function you can use to test if it has a value

(HasValue({?UCR Code 1} ) AND {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 1} & "*")
 or 
(HasValue({?UCR Code 2} ) AND {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 2} & "*")
OR
(HasValue({?UCR Code 3} ) AND {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 3} & "*")
OR
(HasValue({?UCR Code 4} ) AND {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 4} & "*")

Open in new window


mlmcc
0
 
James0628Commented:
mlmcc,

 He has "Crystal Reports IX" at the beginning of his post.  :-)  So I guess HasValue is not an option (unless he gets a newer version of CR).


 MikeM670,

 If the parameters are strings and will just be blank if the user doesn't enter a value, then you should be able to use something like this:

(
({?UCR Code 1} = "" or {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 1} & "*")
 or
({?UCR Code 2} = "" or {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 2} & "*")
 or
({?UCR Code 3} = "" or {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 3} & "*")
 or
({?UCR Code 4} = "" or {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 4} & "*")
)

Open in new window


 James
0
 
mlmccCommented:
Missed the version.  You are correct HasValue and optional parameters were added in CR2008.

Your code may work or he may have to provide a value (space) for the parameters.  I don't have CR XI here to check if it allows the parameter prompt to be ignored.

If it does then may need to also check for NULL

I think you need to AND the tests together otherwise unless all 4 parameters have values one of the tests for "" will be true

(
({?UCR Code 1} = "" or {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 1} & "*")
AND
({?UCR Code 2} = "" or {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 2} & "*")
AND
({?UCR Code 3} = "" or {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 3} & "*")
AND
({?UCR Code 4} = "" or {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 4} & "*")
)

Open in new window


Adding the NULL test

(
(IsNull({?UCR Code 1}) OR {?UCR Code 1} = "" or {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 1} & "*")
AND
(IsNull({?UCR Code 2}) OR {?UCR Code 2} = "" or {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 2} & "*")
AND
(IsNull({?UCR Code 3}) OR {?UCR Code 3} = "" or {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 3} & "*")
 or
(IsNull({?UCR Code 4}) OR {?UCR Code 4} = "" or {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 4} & "*")
)

Open in new window


mlmcc
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
MikeM670Author Commented:
Hi Guy!

Yes my version is XI and the HasValue will not be available.  I'm pondering upgrading to 2016 to obtain this feature.  I really can see the value in it.  I'm surprised it was never in the original release.  Just have to talk it over with the boss.  He really wants me to move to Active Reports due to the web features.  

Do you know if I can do a upgrade directly from XI to 2016?  

I think the parameter will require the test for both NULL and "" if the user make a mistake and enters a wrong UCRCode value in the parameter field.

Mike
0
 
mlmccCommented:
Yes you can upgrade.  Report format didn't change so all reports should open and run.

mlmcc
0
 
James0628Commented:
mlmcc,

 You're right, of course.  The formula that I posted should have AND in between the parameter checks.  I didn't think about how the tests for blank parameters changed the overall logic.

 FWIW, as long as the parameter has a default value, the user isn't required to enter anything, and string parameters seem to automatically have an empty string as the default value.  So that shouldn't be a problem.

 James
0
 
MikeM670Author Commented:
OK tried the code you provided and it runs.  I tried it with two parameters filled in.  Interestingly enough it returned more records '1395'  then the values in those two parameters.  Closed and reran the report with just the first UCRCode field value of 5060 and it returned '1395' the exact number of records as before.  

Thoughts?
0
 
James0628Commented:
Did you use the corrected version of my formula that mlmcc posted, with AND in between the parameter tests?  I left OR in between them, which would end up matching any UCRCodeAll if you left any of the parameters blank.

 FWIW, if you like, you could also include the IsNull tests that he added.  I don't think it's possible for the parameters to be null if you're running the report from CR, but there's no harm in checking.

 James
0
 
MikeM670Author Commented:
Here is the code that I tried based on the AND added between the parameter checks.

(
(IsNull({?UCR Code 1}) OR {?UCR Code 1} = "" or {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 1} & "*") 
AND
(IsNull({?UCR Code 2}) OR {?UCR Code 2} = "" or {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 2} & "*")
AND
(IsNull({?UCR Code 3}) OR {?UCR Code 3} = "" or {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 3} & "*")
 or
(IsNull({?UCR Code 4}) OR {?UCR Code 4} = "" or {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 4} & "*")
)     

Open in new window


Found a OR in between the third and fourth parameter checks.    So I corrected it.

(
(IsNull({?UCR Code 1}) OR {?UCR Code 1} = "" or {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 1} & "*")
AND
(IsNull({?UCR Code 2}) OR {?UCR Code 2} = "" or {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 2} & "*")
AND
(IsNull({?UCR Code 3}) OR {?UCR Code 3} = "" or {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 3} & "*")
AND
(IsNull({?UCR Code 4}) OR {?UCR Code 4} = "" or {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 4} & "*")
)  

Open in new window


Reran the report and now it returns no data based on the query.  If my logic and that can be called into question on occasions is correct doesn't this query want all four parameter values and since there are none that match all these the report returns no data?

Mike
0
 
James0628Commented:
Sorry.  I realize now that the logic needs some work.  As it stands, if you enter a value for more than one parameter, you won't get any records, because the AND's mean that every parameter test has to be true, and that's not possible when you enter a value for more than one parameter (because the field can only match one of them at a time).

 Using OR's instead (as in my original formula) solves that problem, but then if you don't enter a value for one or more of the parameters, you end up getting every code.

 I'm pretty sure that there's a way to get this to work, but it's not immediately obvious.  I'll look at it and get back to you.

  James
0
 
James0628Commented:
OK.  Try this.

(
(if IsNull({?UCR Code 1}) OR {?UCR Code 1} = "" then
   False
 else
   {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 1} & "*")
OR
(if IsNull({?UCR Code 2}) OR {?UCR Code 2} = "" then
   False
 else
   {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 2} & "*")
OR
(if IsNull({?UCR Code 3}) OR {?UCR Code 3} = "" then
   False
 else
   {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 3} & "*")
OR
(if IsNull({?UCR Code 4}) OR {?UCR Code 4} = "" then
   False
 else
   {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 4} & "*")
)

Open in new window


 I'm separating the parameter tests with OR this time.  If a parameter is blank/null, return False for that test, meaning that that parameter won't affect the results.  If the parameter is not blank/null, compare it to the field and return that result.

 So, for example, if you enter values for the first 2 parameters, the tests become:

(
   {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 1} & "*"
OR
   {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 2} & "*"
OR
   False
OR
   False
)

 I think that will work (but I've thought that before :-).

 James
0
 
MikeM670Author Commented:
For this particular report and many others that I can think of to create we would need at least one UCRCode value.  The way the current program and database are setup each incident would need at least a single UCRCode value in the record.  In the database there are the following ucrcode fields:

UCR Code 1
UCR Code 2
UCR Code 3
UCR Code 4

Now each one of those fields are combined into the single field called UCRCodeALL separated by a ",".
   5060
   5060,8028
   0100,5060,7720

That is why I want to check that field for the values as I don't know which one of the fields the code might be in.  But in the UCRCodeAll field they can be in any order and I can find it.

I tried changing all the AND's to OR's between parameter checks but it still fails to return data.

Mike
0
 
James0628Commented:
Did you see my last post?  It was posted just 20 seconds before yours, so you might not have noticed it after you added that last post.

 James
0
 
MikeM670Author Commented:
For your updated suggestion it still fails to return any data.  

I did add all four parameter values to the report along with the query to see what values it had.  Parameter 1 and 2 show the values entered in the report.  Parameter's 3 and 4 show blank so that appears to be correct.   I attached a image to show you.

Mike
0
 
James0628Commented:
I don't see an image.

 Can you u/l the report (.rpt file) itself?

 If not, is there anything else in the record selection formula?  If so, can you post the entire formula?  That last formula that I posted still looks OK to me.  I definitely don't see how you could get no data from that (assuming that the basic {CaseMaster.UCRCodeAll} like "*" & {?parameter} & "*" test is OK), unless there was something else in the record selection formula.

 James
0
 
MikeM670Author Commented:
Let my try the upload again.    Not sure I should upload the report itself.  Is it possible to email that directly to you?
2016-09-08--1-.png
0
 
James0628Commented:
Emailing it to me would probably be against the site rules (for one thing, it wouldn't be fair to any other experts that wanted to help).

 Try replacing that section of the formula with:

(
   {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 1} & "*"
OR
   {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 2} & "*"
OR
   False
OR
   False
)

Open in new window


 That _should_ be the same logic, with the if-else statements removed.

 If you still don't get any results, try just:

(
   {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 1} & "*"
OR
   {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 2} & "*"
)

Open in new window


 And if that doesn't work either, try each of those tests separately.  Try just the test on {?UCR Code 1}, and then the test on {?UCR Code 2}.

 James
0
 
MikeM670Author Commented:
OK I redid the parameter fields in the report by removing and adding them again.  I then reran the report based on your updated code:

(
(if IsNull({?UCR Code 1}) OR {?UCR Code 1} = "" then
   False
 else
   {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 1} & "*")
OR
(if IsNull({?UCR Code 2}) OR {?UCR Code 2} = "" then
   False
 else
   {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 2} & "*")
OR
(if IsNull({?UCR Code 3}) OR {?UCR Code 3} = "" then
   False
 else
   {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 3} & "*")
OR
(if IsNull({?UCR Code 4}) OR {?UCR Code 4} = "" then
   False
 else
   {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 4} & "*")
)

Open in new window


Now it is working properly.  I tested various UCR Codes in the parameter fields and even skipped the first parameter field and enter the test value in the second one and it returned the same data.   I wonder if something was corrupted with the original report.


Mike
0
 
MikeM670Author Commented:
Thanks to both of you for assisting with my question.  I awarded James a few more points because his final code was correct.  mlmcc your initial posting pointed us in the proper direction and if I was using a version greater then XI it would of been the more elegant one.
0
 
James0628Commented:
I guess something might have been "corrupt".  I've seen other posts here where something in a report just seemed to refuse to work correctly, until something in the report was removed and recreated, or maybe the report was recreated from scratch.  So that kind of thing does seem to happen.  Or maybe you did something slightly different when you recreated the parameters.  <shrug>

 IAC, I'm glad that you got it working.  I really couldn't see anything wrong with the formula.  :-)

 James
0
 
mlmccCommented:
This should work

(
(Not(IsNull({?UCR Code 1})) AND {?UCR Code 1} <> "" AND {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 1} & "*")
OR 
(Not(IsNull({?UCR Code 2})) AND {?UCR Code 2} <> "" AND {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 2} & "*")
OR 
(Not(IsNull({?UCR Code 3})) AND {?UCR Code 3} <> "" AND {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 3} & "*")
OR 
(Not(IsNull({?UCR Code 4})) AND {?UCR Code 4} <> "" AND {CaseMaster.UCRCodeAll} like "*" & {?UCR Code 4} & "*")
)  

Open in new window


mlmcc
0
 
James0628Commented:
Yeah, I thought about that, but somehow the if-else approach seemed clearer to me at the time.

 James
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 10
  • 8
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now