This is working for me for char type data. But I am getting an error message when used for numeric data type. Instead of ALL I used a numeric data type 0 (Zero) to make this work as ALL did not work. Parcing is ok. I exported the universe. But when I try using it in my report I am getting the error message an internal error occurred while calling ANSWERPROMPTs API. (Error: WIS 30270). Find below the query used. I used it in pre-condition. Please let me know if I need to do something else.
(@Select(Sales Geography\Country Group) IN @Prompt(‘Select Country Group or
enter 0 for all groups’,‘N’,‘Sales Geography\Country Group’,multi,free) ) OR
(‘0’ in @Prompt(‘Select Country Group or enter 0 for all groups’,‘N’,‘Sales
Geography\Country Group’,multi,free))
@Select(SHARED_ACCUM_STNDRD_CLM\CONTROL_NUMBER) IN @Prompt('Choose CONTROL NUMBER or Enter 00 for All CONTROL NUMBERS','N','SHARED_ACCUM_STNDRD_CLM\CONTROL_NUMBER',multi,free,NOT_PERSISTENT,) or 00 in @Prompt('Choose CONTROL NUMBER or Enter 00 for All CONTROL NUMBERS','N,'SHARED_ACCUM_STNDRD_CLM\CONTROL_NUMBER',multi,free,NOT_PERSISTENT,) or 00 in @Prompt('Choose CONTROL NUMBER or Enter 00 for All CONTROL NUMBERS','N','SHARED_ACCUM_STNDRD_CLM\CONTROL_NUMBER',multi,free,NOT_PERSISTENT,)
Parsing ok. But it does not work in reports. Please let me know in case I need to do some formating.
Again for Char type of data it is working fine. But the total # of records are different from the total # of records available in the Database. Please let me know if I am missing some where. I am using BO XI R2 SP2.
You have 2 almost identical OR parts in the condition. Is it on purpose or is it a typo? I write identical because the second part has ,'N, and the third one has ,‘N’,
What does it mean that it is not working in report? Do you get any error message?
I dont know DB2 V8 but I assume that 0 (zero) should be 0 and not 00.
Thank you Marek.
It was a typo. Hence it was not working. Now it is working. But the problem is the total count is not matching.
Say I have 4 values.
0001, 0002, 0003 and 0004 and 0(for all). Individual counts are
0001 - 10(number of records)
0002 - 10(number of records)
0003 - 10(number of records)
0004 - 10(number of records)
Total- 40
So total # of records available is 40 (adding all four). So when I select 0 (meaning ALL) I should get all the 40 records. But I am getting less number of records. How can I resolve it?
It is really hard to help you if all that you provide is the syntax of a condition. If we dont know how the data looks like, how the universe objects are defined, how the SQL is constructed, how the report is formatted then nobody is able to do a magic and say why you are getting less rows and how you could resolve the problem
Just a thought:
How many rows do you see in Data Manager? Is not the problem due to the aggregation of the duplicate rows?
SELECT DISTINCT
xyz.dbo.table1.Appoinment_Date
FROM
xyz.dbo.table1
UNION
select ‘1/1/1900 12:00:00 AM’
In Where clause of the Object
@Select(xyz dbo table1\Appointment Date) IN @prompt(‘Enter values forAppointment Date’,‘A’,‘xyz Dbo table1\Appointment Date’,Multi,Free)
OR (‘1/1/1900 12:00:00 AM’) IN @prompt(‘Enter values forAppointment Date’,‘A’,‘xyz Dbo table1\Appointment Date’,Multi,Free)
This gives me single prompt with all and multiple selection of list of values
select Magic date '1/1/1900 12:00:00 AM’i n list of values it acts as All
Thank you for your help. I am facing a problem in sorting/prioritizing the prompts. I used the formula mentioned above to create some pre defined conditions and used these as prompts with some normal prompts in query filters panel. But when I click on run query, I can see all the prompts and the pre defined conditions (that are used as prompts) but not in the way I arranged. They are seen in sorted order. Is there a way to arrange them differently (not in sorted order)?
Note - I clicked on the edit query panel, then properties and prompt order. Here I can see only the prompts I used but not the pre defined conditions that I used as query filters.
Using below prompt we can add/select ALL the values. But I am wondering if we have a similar kind of option for N/A. Meaning if we select N/A, the query should run with the values given thru other prompts ignoring the prompt with value N/A.
Query to select all the values
(@Select(Sales Geography\Country Group) IN @Prompt('Select Country Group or
enter * for all groups','a','Sales Geography\Country Group',multi,free) ) OR
('*' in @Prompt('Select Country Group or enter * for all groups','a','Sales
Geography\Country Group',multi,free))
Appreciate any quick help from any one.
Thanks in advance.
Vicky
---
**its_vicky07** :us: _(BOB member since 2007-09-02)_
May I request you to help me with the question I raised few days back? Is there a way to ignore a prompt using N/A. I understand optional prompts may not be possible. But can we have N/A in LOV and select it if we want to ignore one prompt? I searched the forum for this kind of function. But did not find exactly what I want.
Is not the “ignoring a prompt using N/A” the same as using ALL in the prompt? I think it is the same. And here on BOB there are many posts about how to use ALL in the prompts.
For instance 2 first entries in Designer FAQ deal with it.
You do not “ignore” a prompt using some value like N/A. You code the prompt so that if N/A is selected then all values for that condition are returned.
Do you understand that with cascading propmts that you cannot stop in the middle of the cascade? You cannot get off of the train until the last stop. So if you make each option have N/A or ALL or some other special token, then you have to code your LOV queries to be able to handle that appropriately. Each step of the LOV cascade has to address the possibility that the user selected “ALL” or “N/A” at the prior level, and it gets complicated.
We can select all the values by selecting ALL. You can say it is a different way of ignoring it. But my requirement is different. I do not want to consider that prompt at all. An alternate of optional prompts. If I select N/A that prompt should not be considered at all. Is there a way to do so ?
I understand Dave.
But luckily it is the 1st prompt I need N/A. Is there a way we can do some thing at WEBi level? Please suggest. I do not want to select all.
No, that’s actually unlucky. Now you have to figure out how to pass the N/A all the way through to the last prompt. The last prompt is where you would be lucky.
You don’t select all, you select N/A. The logic in the prompt (or LOV in this case) has to then translate the selection of N/A into a match for every possible value. You don’t physically select them all, the logic does that for you.
Didn’t you say you had Country, State, City, Area, and Zip, or something like that? If you select N/A for Country you cannot abandon the rest of the cascading list of values. You must go all the way through to zip code, or whatever the final level of your cascade is.
I’m new to BO. I know the very basics of BO. Can any one of you please explain how the following query works? I read the explanation in designer FAQ. But, i can’t really understand the logic behind it.
I guess this query should be placed in Select clause of the object. Why are we using again @Select variable to point to the same object instead of the actual table?
(@Select(Sales Geography\Country Group) IN @Prompt(‘Select Country Group or enter All for all groups’,‘N’,‘Sales Geography\Country Group’,multi,free) )
OR
(‘All’ in @Prompt(‘Select Country Group or enter All for all groups’,‘N’,‘Sales Geography\Country Group’,multi,free))
When the user types ‘All’, how the BO recognizes ‘All’ means all the values listed not the literal ‘All’?
(BOB member since 2003-11-27)
(BOB member since 2007-07-12)
(BOB member since 2007-09-02)
So if you make each option have N/A or ALL or some other special token, then you have to code your LOV queries to be able to handle that appropriately. Each step of the LOV cascade has to address the possibility that the user selected “ALL” or “N/A” at the prior level, and it gets complicated.