My Business Users require an All option to select a list of market names as their are plenty of names, for this I created a Dummy table with one column and made a union of market names object with this dummy table.
then is the where clause of another new object called market_prompt I used this generic defination recomended by BO:
Table.Field IN @Prompt(‘Message (type ALL for all values)’,‘A’,‘MyClass\MyObject’,multi,free) or ‘ALL’ in @Prompt(‘Message (type ALL for all values)’,‘A’,‘MyClass\MyObject’,multi,free)
–this gives me two different prompts when I open and refresh the report, and on clicking on the Values tab to see the LOVs, I see the ALL option along with different market names. On select the ALL option no data is pulled and if I select any individual market name then it is reflected in the report. I tested this using the prompt object in the result objects tab of the query panel, Does anyone has any idea why am getting this or am I wrong in any of my steps. We have BO 6.5.1, oracle 10g. Also I need to show single prompt and not two.
And if need to edit the SQl manually such that the ALL option in the LOVs selects all the values from the market name column of Stores table, then how to do that?
can someone please help on this.
and I get two different prompts as I already have but the link (FAQ: Designer ) you forwarded me(thanks for that) says to override the sql, but how to override and save the sql, i went through the steps written but it does not save it,
and the second approach was to create a universe object(how can we associate it with sys.dual table and how to locate this table?),
thirdly do we need to follow both the approaches or either of them will let me get the all or one option in a single prompt?
Can you post your exact prompt syntax? You should not be getting two prompts.
Here is an example that works: @Prompt(‘Select Legal Parent Code or enter ALL for all codes’,‘a’,‘Customer Hierarchies for Bill To\Legal Parent Code’,multi,free)
or (‘ALL’ in @Prompt(‘Select Legal Parent Code or enter ALL for all codes’,‘a’,‘Customer Hierarchies for Bill To\Legal Parent Code’,multi,free))
Notice that there is a set of parentheses around (‘ALL’…) that were not in your example.
I am moving this thread to a more appropriate forum. I will move to Reporter, if you are coding this in Designer let me know and I will move it there.
here is the exact syntax which I have in one of my report:
STORE_DIM.STORE_NAME in @Prompt(‘3. select individual stores’,‘a’,‘Store Dim_Issue\Store Name IF’,multi,free) or (‘ALL’ in @Prompt(’ Type ALL for all store values’,‘a’,‘Store Dim_Issue\Store Name IF’,multi,free))
This gives me two different prompts, am I missing out something, also I have inserted this object in the Result Objects portion. :?
the text of the prompt needs to be same in both @prompt functions. So try it this way:
STORE_DIM.STORE_NAME in @Prompt('3. select individual stores (type ALL for all)','a','Store Dim_Issue\Store Name IF',multi,free) or ('ALL' in @Prompt('3. select individual stores (type ALL for all)','a','Store Dim_Issue\Store Name IF',multi,free))
Well your prompt texts are different so of course you are presented with two prompts, instead try:
STORE_DIM.STORE_NAME in @Prompt('3. select individual stores or type ALL', 'a', 'Store Dim_Issue\Store Name IF', multi, free)
OR
'ALL' in @Prompt('3. select individual stores or type ALL', 'a', 'Store Dim_Issue\Store Name IF', multi, free)
This works excellent, thanks much for your help. :D.
I had a small question like when I refresh the report then we see the prompts with the values entered when we refreshed it last time, so is their any way to keep this promp blank.
To put blank when the user opens or refreshes the report, follow this link, there is a VBA code. You can make use of it by modifying it and putting in the right event.
(BOB member since 2005-09-26)
(BOB member since 2004-06-09)
(BOB member since 2003-11-27)
(BOB member since 2002-06-20)
