asked on
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
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
Crystal Reports
Last Comment
James0628
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
Adding the NULL test
mlmcc
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} & "*")
)
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} & "*")
)
mlmcc
ASKER
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
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
Yes you can upgrade. Report format didn't change so all reports should open and run.
mlmcc
mlmcc
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
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
ASKER
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?
Thoughts?
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
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
ASKER
Here is the code that I tried based on the AND added between the parameter checks.
Found a OR in between the third and fourth parameter checks. So I corrected it.
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
(
(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} & "*")
)
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} & "*")
)
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
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
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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
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
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
James
ASKER
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
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
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
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
ASKER
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
2016-09-08--1-.png
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:
That _should_ be the same logic, with the if-else statements removed.
If you still don't get any results, try just:
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
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
)
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} & "*"
)
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
ASKER
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:
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
(
(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} & "*")
)
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
ASKER
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.
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
IAC, I'm glad that you got it working. I really couldn't see anything wrong with the formula. :-)
James
This should work
mlmcc
(
(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} & "*")
)
mlmcc
Yeah, I thought about that, but somehow the if-else approach seemed clearer to me at the time.
James
James
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:
Open in new window
James