I have a strange problem. I have created a predefined condition to enable user to select all values or specific Account_numbers.
The psuedocode for this is:
‘*’ in (‘Select Account_no or Enter * for All’,‘A’,MULTI,free)
or
Account_no In (‘Select Account_no or Enter * for All’,‘A’,MULTI,free)
However, this should enable user to select ALL or specific Account_numbers but not both. This code is allowing users to select both * and specific Account_no in prompt. What changes do I need to do to enable this?
Actually, Michael, if I read the question correctly the FAQ doesn’t help.
Once you add “All” or in your case the * to the LOV, there is nothing you can do to prevent the user from selecting it along with any number of account numbers. If you use = and mono rather than “In” and multi then you can restrict them to a single value, but in that case they would not be able to select more than one account number.
Dave, you’re correct, and so I would suggest not adding the “All” or “*” to the LOV. Simply include something in the prompt text to indicate that “All” can be typed in to get all values.
Not adding it to the LOV will not help. I have removed LOV from the Account_no at the universe level and the * appears to the right in the prompt dialog box. If the * is entered before - it is already there and if user enters an account number and hits the > button, both * and account number will be there. Is there a way to remove the * automatically when user selects an account number(s)?
If you purge the Data Provider before publishing the report, I believe that this will remove the * when the report is opened next time. If users are not allowed to save their changes, then the * will be gone each time the report is opened.
How to you set the ALL to work though? I still can’t get it to work right where it will allow a user to either select (or enter in) ALL or be able to choose values from the LOV?
It’s interesting that this topic sprang up about the same time I needed a resolution. In my dilema, I’m asking the user for either “*” for all, a partial entry (to retrieve those items which are LIKE it) or enter a list. In the code below, the first two options work great. However, either I need to format the list being entered differently, or there’s code missing to handle it. This is being placed in a predefined condition.
Any ideas?
( ( DC_QL_LABEL.CSN IN (
select DC_QL_LABEL.CSN
from DC_QL_LABEL
where DC_QL_LABEL.CSN like '%' || @variable('Enter Customer S/N or enter * if entering our S/N','a','DC_QL_LABEL\Customer SN',multi,free) || '%' ) )
OR ('*' IN @variable('Enter Customer S/N or enter * if entering our S/N','a','DC_QL_LABEL\Our SN',multi,free) ) )
AND ( ( DC_QL_LABEL.QLSN IN (
select DC_QL_LABEL.QLSN
from DC_QL_LABEL
where DC_QL_LABEL.QLSN like '%' || @variable('Enter Our S/N or enter * if entering customer S/N','a','DC_QL_LABEL\Customer SN',multi,free) || '%' ) )
OR ('*' IN @variable('Enter our S/N or enter * if entering customer S/N','a','DC_QL_LABEL\Our SN',multi,free) ) )
Thanks in advance…
p.s.: After working on this statement all day, I see that trying to search for a partial serial number blows the response of the report from a few seconds to 30 minutes or more since there are potentially a couple million records. This is something I don’t want my users to encounter. I’m guessing the engine is smart enough to first try to find the entered serial number by using each table’s index, then, if not found, to look for a partial (the reason for the || ‘%’ in a couple of places).
RAD…
(BOB member since 2002-08-08)
(BOB member since 2002-06-06)
(BOB member since 2006-01-23)