Solved

Ignore parameter if no value entered

Posted on 2016-09-07
22
34 Views
Last Modified: 2016-09-08
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
Comment
Question by:MikeM670
  • 10
  • 8
  • 4
22 Comments
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 150 total points
ID: 41788928
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
 
LVL 34

Expert Comment

by:James0628
ID: 41789306
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 41789590
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
 

Author Comment

by:MikeM670
ID: 41789964
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 41790021
Yes you can upgrade.  Report format didn't change so all reports should open and run.

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 41790052
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
 

Author Comment

by:MikeM670
ID: 41790074
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
 
LVL 34

Expert Comment

by:James0628
ID: 41790133
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
 

Author Comment

by:MikeM670
ID: 41790206
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
 
LVL 34

Expert Comment

by:James0628
ID: 41790249
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
 
LVL 34

Accepted Solution

by:
James0628 earned 350 total points
ID: 41790282
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:MikeM670
ID: 41790284
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
 
LVL 34

Expert Comment

by:James0628
ID: 41790295
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
 

Author Comment

by:MikeM670
ID: 41790297
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
 
LVL 34

Expert Comment

by:James0628
ID: 41790320
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
 

Author Comment

by:MikeM670
ID: 41790327
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
 
LVL 34

Expert Comment

by:James0628
ID: 41790353
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
 

Author Comment

by:MikeM670
ID: 41790449
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
 

Author Closing Comment

by:MikeM670
ID: 41790456
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
 
LVL 34

Expert Comment

by:James0628
ID: 41790524
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 41790682
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
 
LVL 34

Expert Comment

by:James0628
ID: 41790693
Yeah, I thought about that, but somehow the if-else approach seemed clearer to me at the time.

 James
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

706 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

20 Experts available now in Live!

Get 1:1 Help Now