When we use prompt which are defined in Universe, The ‘ALL’ works fine but if I use some object(dimension) say impact and then drag and drop that object to query panel and select the option ‘ALL’ from LOVs. then its not working…
the SQL query in this(failed) case shows
select **** from ***
where HPD_Impact.Impact IN (“ALL”)
that means it is seaching the impact string to be 'ALL"
but the for right result query should be like…
select *** from ***
where ( ( HPD_Impact.Impact ) IN @Prompt(‘Enter/Select Impact Type(s)’,‘Incident\Impact’,MULTI,FREE) OR ‘ALL’ IN @Prompt(‘Enter/Select Impact Type(s)’,‘Incident\Impact’,MULTI,FREE) )
Can anyone please help me to find the solution to this problem.
When you follow the steps to add “All” to the LOV, then you must use the predefined condition, else the query is not going to work. What I do is create a second copy of the object, add ALL to this one, and use it for the LOV for the predefined condition. Then I hide that object.
That way the general object left available to users does not have ALL since it won’t work anyway.
Take a object State in e-fashion universe. Go and check LOVs of the object.
You will get some values like:
California
Colorado
DC
Florida
Illinois
Massachusetts
NewYork
Texas
Now what I want is, I want to display LOVs like: ALL
California
Colorado
DC
Florida
Illinois
Massachusetts
NewYork
Texas
And If I use State as a filter and select ALL, then all the states should be included in query. I am not able to implement this.
Hi,
You can make use of the condition object to take care of the ALL value in the report. You have to build the condition object in the universe and use in the report.
The link you posted is for prompt. My question is bit different. If you drag object(which is dimension not prompt) to query filter and then want to use ‘ALL’.
like in ad-hoc reporting…
select State and Revenue and then drag the same State object to query filter there in LOVs I need to implement ‘ALL’.
In that case you need to create a dummy dimension object in the universe as
'ALL'
In the query panel drag the state object and create the prompt condition using INLIST operator
Then drag the dummy object and create another prompt condition using the same prompt text as above
Double click the ‘AND’ operator changing it to ‘IN’
It doesn’t work that way. In order to have the “ALL” logic take place, you have to use a predefined condition. If you add “ALL” to the standard list of values, and try to use a regular object, it will not ever match on the “ALL” and therefore the condition will fail.
Dave, it will very much work even in query panel by using the standalone object (created in the universe with no table associated to it)
The LOV object will have to be changed to include ‘ALL’ though
If you have an object “State” and you modify the LOV to include the value “ALL” then you cannot make a simple condition on the State object and select “ALL” and get results. There is no state value to match.
If you want to use the ALL logic with standard objects, then you have to build condition logic that replicates the “… OR ‘ALL’ In (‘ALL’)” logic used in a predefined condition. Then your user has to understand how to build that condition logic on the query panel. In re-reading your post, it seems that’s what you were talking about, is that true?
Right, just adding ALL keyword to the LOV is not enough, the condition in query panel will be created by using standalone ‘ALL’ object in combination with state object
What I meant was that the ‘ALL’ logic can also be built directly in the query panel (prerequisite being the standalone ‘ALL’ object created in the universe)
And yes the user has to be educated on how to simulate it
but if I use some object(dimension) say impact and then drag and drop that object to query panel and select the option ‘ALL’ from LOVs. then its not working… 
(BOB member since 2009-01-20)
(BOB member since 2005-07-18)
(BOB member since 2002-06-06)