BusinessObjects Board

How to create a ALL object in the Universe

building semantic layer
#1

I am trying to create a ALL object in the universe to create a select ‘ALL’ from DUAL in an union query in my customise LOV. How can I do that ?
Can’t figure out how to get DUAL in my object definition.

Cheers

gogo (BOB member since 2005-06-03)

Need help with Optional Prompts
Multi Prompts
#2

Try these 2 FAQ topics:

Is there any way to set up a prompt that will allow a user to select one value, many values, or type ‘ALL’ for all values in a list?
Now that my LOV lets me use ALL for all values, how do I get the ALL in the list?

BoB LoblaW :us: (BOB member since 2007-10-23)

#3

Hi there,

It’s been a little while, but it goes a little something like this:

First whe create the LOV with the ’ ALL’ value.

  1. Create an dimension object and define it with the constant ’ ALL’ (be sure to put a leading white space or some other character with sort precendence, this will help in sorting).
  2. Go the properties panel of the object to which you’re attaching your LOV.
  3. Create an inferred join to any table of your choice (object properties, Definition Tab, Tables button) and save that object.
  4. Go to the Dimension object which will return your LOV with other values other than ’ ALL’ and go to the properties tab and under the “Edit List of Values” section click “Edit…”
  5. In the Query Panel, create a UNION query and bring in the ‘ALL’ dimension. Depending on your unv parameters, you may need to do a DB-level sort at this point.
  6. Run the query. You should see ’ ALL’ at the top.

Second we create the Predefined Condition with the SQL expression.

  1. Create a Predefined Condition Object.
  2. Write the following code:
 ( MyTable.MyColumn IN @Prompt('Please select Widget','A','MyLOVObject',multi,constrained) 
OR ' ALL' IN @Prompt('Please select Widget' ,'A',MyLOVObject) )

Here’s the thinking:

If the user selects ’ ALL’ then the value of the 2d prompt instance is equal to the constant, ’ ALL’ after the OR operator; “ALL = ALL” which evaluates to true. Otherwise, the expression before the operator evaluates to true and the resultset get’s restricted.

Hope this helps!

vonwolf :us: (BOB member since 2002-10-21)

#4

Hi,
'I was reasing your post to do something similar,

1. Create an dimension object and define it with the constant ' ALL' (be sure to put a leading white space or some other character with sort precendence, this will help in sorting).

I could not undertans the above step.
Can you be more specific and explain in details, when i create a new dimension objects, what do I put in the select clause and other details…

Need help fast,
thanks :crazy_face:

americanmc :hong_kong: (BOB member since 2009-12-31)

#5

Did you read the FAQ entries linked previously?

Dave Rathbun :us: (BOB member since 2002-06-06)

#6

Yes, I did…
But when going through creating the object, that first step is not clear, as to exactly how… what table.column should be mentioned for it? etc…

americanmc :hong_kong: (BOB member since 2009-12-31)

#7

If you use version 3.1 you can make prompts optional. Which mean if you leave them blank then the parm is ignored. It works great. You LOV wont display ‘All’ per say but the functionality is the same.

mcliffordgoo :us: (BOB member since 2003-02-13)

#8

All Display Prompt:

  1. Object - this is hidden in the universe – custom SQL

SELECT DISTINCT
BI_IMPACTED_ORD_VW.DV_PAX
FROM
BI_IMPACTED_ORD_VW
UNION
SELECT ‘ALL’ FROM
BI_IMPACTED_ORD_VW

  1. Predefined Filter
    BI_IMPACTED_ORD_VW.DV_PAX IN
    @Prompt(‘Select value for Pax Movement’,‘a’,‘Bi Impacted Ord Vw\paxforprompt’,multi,free)
    OR (‘ALL’ in @Prompt(‘Select value for Pax Movement’,‘a’,‘Bi Impacted Ord Vw\paxforprompt’,multi,free))

OR Use this code to display the ALL if no field is selected –

= If (UserResponse([TxCode];“Select one or more Code(s) from the list:”)="") Then “All Codes” Else UserResponse([TxCode];“Select one or more Code(s) from the list:”)

Or Use this code in Drill Filters to display ALL in no field is selected –

=If(Not(DrillFilters([Job Type Category]) InList(“Part Time”; “Full Time”; “Both Time”));“ALL”; DrillFilters([Job Type Category]))

DK MC (BOB member since 2010-02-18)