i went over the semantic layer forum and found the solution to set up a prompt that will allow a user to type * for all values in a list. I have reached the stage where if i type ‘*’, it is selecting all the values. I am not able to incorporate the ‘All’ in my LOV. i read the post for this from:
i tried to follow the same steps, but am unsuccessful. instead of ‘all’ appearing in the LOV, it is appearing as a seperate row in my results. this is what my simple report has:
prompt: select market (i have included the steps from
in this prompt and it works fine)
report columns: brand,count(*) as count
i tried to override the sql by adding
union select ‘all’ as brand,0 as count to avoid errors.
please advise as to what i am missing. we are using XI R2 with netezza.
Can you please explain how to do that? i am new to BO and kind of confused. I have the following in a condition:
( admin.market_dimension.market_name IN @Prompt(‘Select Market or enter * for all groups’,‘a’,‘Market\Market Name’,multi,free) )
OR
(’*’ in @Prompt(‘Select Market or enter * for all groups’,‘a’,‘Market\Market Name’,multi,free))
Is this correct? I dont understand what you mean by edit ListOfValues SQL.
That looks ok for the condition logic. The next step is to modify the query that generates the values in the LOV for Market Name. You have two options
Modify the actual LOV query for Market Name.
Create a new object to contain the LOV query.
The second option is better since the first will cause “*” to appear in the LOV even if a user manually creates a condition on it.
For the second option, create a new object and hide it. Give it a name such as “Market Name LOV”. The SELECT and WHERE are irrelevant. Click the Properties tab, make sure “Associate a List of Values” is checked, check off “Export with Universe”. Click the Edit button, which will bring up a query panel. Hit the “SQL” button in the query panel, then plug in your LOV query (ex. “select ‘*’ from dual union select xxx from yyy”). Make sure “Don not generate SQL…” is checked off. Change the condition you already built to reference Market Name LOV in the @prompt() call.
Thanks. I am now able to see ‘All’ in the prompt. Can you please help me modify the condition. I would like to diisplay all the markets when the users select ‘All’.
( @Select(Dummy\MarketName LOV) IN @Prompt(‘Select Markets or Select All for all groups’,‘a’,‘Dummy\MarketName LOV’,multi,free) )
OR
(’*’ in @Prompt(‘Select Markets or Select All for all groups’,‘a’,‘Dummy\MarketName LOV’,multi,free))
I am using ‘All’. I was using ‘*’ inititally, but i want to use ‘All’ instead.
this is my condition right now. i would like to change the * to all, so that when users select all, it will print all markets.
( @Select(Dummy\MarketName LOV) IN @Prompt('Select Markets or Select All for all groups','a','Dummy\MarketName LOV',multi,free) )
OR
('*' in @Prompt('Select Markets or Select All for all groups','a','Dummy\MarketName LOV',multi,free))
ok… this is what i did and it worked… if Joe or someone has a better way, please let us know… as this may not be the smartest way…
( @Select(Dummy\MarketName LOV) IN @Prompt('Select Markets or Select All for all groups','a','Dummy\MarketName LOV',multi,free) )
OR
('All' not in (Select market_name from market_dimension where '*' = '*'))
( @Select(Dummy\MarketName LOV) IN @Prompt('Select Markets or Select All for all groups','a','Dummy\MarketName LOV',multi,free) )
OR
('All' in @Prompt('Select Markets or Select All for all groups','a','Dummy\MarketName LOV',multi,free))
(BOB member since 2002-08-29)