BusinessObjects Board

All or * for any prompt in universe XI R2

building semantic layer
#1

Hi I am new at this. I have read almost all the topics in this forum. And I am way confused as to how to actualy implement this. I need to have all as a selection for prompts in a universe. I am working currently in Xtreme Sample Universe 11.5 and this is what I have so far:

Customer.City IN @Prompt(‘Select City’,‘A’,‘Customer\City’,‘multi’,‘free’) OR ‘ALL’ in @Prompt(‘Select City’,‘A’,‘Customer\City’,‘multi’,‘free’)

I have the refresh and export with set up. This should be pretty straight forward. But I do not see ‘ALL’ as the first selection in the prompt values.

Anyone see what I am doing wrong.

Thanks!

muffntuf :us: (BOB member since 2006-01-04)

"ALL" as Default Value in Prompt created in Univer
#2

You need to alter the LOV query in order to have the “All” value show up there. That’s a separate step from creating the prompt.

Please read the first two questions in the Designer FAQ as they cover both parts of this issue.

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

#3

If you need ‘ALL’ in the LOV list, then you need to modify the LOV in the universe to union the ALL into the list.

Do a search for all prompt union and you may find what your looking for

Steve Krandel :us: (BOB member since 2002-06-25)

#4

I did union the ‘ALL’ second approach by adding an object called ‘ALL’ to the universe and then putting ‘All’ in the select section.

I get an error ‘The query does not reference a table’.

The ‘All’ object is not part of any table. So what am I doing wrong?

muffntuf :us: (BOB member since 2006-01-04)

#5

Do the ALL with custom SQL

Select ‘ALL’ from Dual

Or include Dual in your universe and force ALL to come from there.

Steve Krandel :us: (BOB member since 2002-06-25)

#6

Dual? What is that?

muffntuf :us: (BOB member since 2006-01-04)

#7

What database are you using? The “dual” table is specific to Oracle.

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

#8

That’s what I thought. We are on SQL 2003, not Oracle.

muffntuf :us: (BOB member since 2006-01-04)

#9

My mistake. Was reading way to many posts.

You can do a Select distinct ‘ALL’ from ANY_SMALL_TABLE

Steve Krandel :us: (BOB member since 2002-06-25)

#10

Steve,

thanks for your reply, but it doesn’t make any sense. There is no ‘All’ in any small table, so when you try to select it, you get an error saying there is no value ‘All’ in the table.

So I have this code:
(@Select(Customer\City) IN @Prompt(‘Select a city…enter ALL for all Cities’,‘A’,‘Customer\City’,MULTI,FREE) )
OR
(‘ALL’ in @Prompt(‘Select the city… or enter ALL for all Cities’,‘A’,‘Customer\City’,MULTI,FREE) )

This generates 3 entry boxes:

Select City

Select a city…enter ALL for all Cities
and

Select the city…or enter All for all cities

It gives me the value button to choose values, but I do not generate any values.

So what is it I am not doing correctly?

muffntuf :us: (BOB member since 2006-01-04)

#11

“All” is a constant. You should be able to select it from any available table. Oracle provides the dual table just for this purpose.

select 'All' from table_01

That should work. If you have 3 rows of data in table_01 then you will get three rows back with the word ‘All’ in them. That’s why Steve suggested the word “Distinct” be added, as it would group the three rows that look alike into a single row.

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

#12

Here is the sql code directly out of the lov:

SELECT DISTINCT
  Customer.City
FROM
  Customer
UNION  
SELECT DISTINCT
'All'
FROM
  Customer
ORDER BY
  1

I am throwing an error Syntax error (missing operator) in query expression ‘( )’

I don’t see where this is being thrown by the lov,

muffntuf :us: (BOB member since 2006-01-04)

#13

For MS SQL Server try:

SELECT 
  Customer.City 
FROM 
  Customer 
UNION  
SELECT 'ALL'

For Oracle try:

SELECT 
  Customer.City 
FROM 
  Customer 
UNION  
SELECT 'ALL' FROM DUAL

For IBM DB2/UDB try:

SELECT 
  Customer.City 
FROM 
  Customer 
UNION  
SELECT 'ALL' FROM SYSIBM.SYSDUMMY1

Andreas :de: (BOB member since 2002-06-20)

#14

I still get the same error.

muffntuf :us: (BOB member since 2006-01-04)

#15

I am in XIr2 and sql server.

Try this. It worked for me.

I added …ALL to the list of values as

SELECT DISTINCT
xyz.dbo.table1.Name
FROM
xyz.dbo.table1
UNION
select ‘…ALL’

and check do not generate slq option.

Then in the condition type condition as

@Select(table1\name) IN @Prompt(‘Select name or select …ALL for all names’,‘A’,‘table1\name’,MULTI,FREE) OR (’…ALL’) IN @Prompt(‘Select name or select …ALL for all names’,‘A’,‘table1\name’,MULTI,FREE).

It gives me single prompt to select …ALL or sigle or multiple names.

Thanks
–H

hourigan (BOB member since 2007-04-06)

#16

But you do not see this come up into WEbi or Crystal in the prompt list. So I am still looking for the solution that allows a lov that has ‘all’ that when chosen, it means all items in lov list should be generated for a result set.

muffntuf :us: (BOB member since 2006-01-04)

#17

Yes, you do. If you follow the instructions for putting ALL in the LOV, you’ll see it.

Steve Krandel :us: (BOB member since 2002-06-25)

#18

here is the code in the sql:
SELECT
Customer.City
FROM
Customer
UNION
SELECT ‘*ALL’
FROM
Customer
ORDER BY
1

This is the where clause:

@Select(Customer\Country) IN @Prompt(‘Select Country’,‘A’,‘Customer\Country’,multi,free) OR ‘*ALL’ IN @Prompt(‘Select Country’,‘A’,‘Customer\Country’,multi,free)

I don’t see the *All come through to Webi or Crystal. I do see it in the universe list when you display the lov list.

So if I don’t see it in the Webi LOV list and I don’t see it in the Crystal lov list, why?

muffntuf :us: (BOB member since 2006-01-04)

#19

Have you checked the box to Export the LOV with the universe?

Have you refreshed the LOV?

Steve Krandel :us: (BOB member since 2002-06-25)

#20

Finally got it to work. So I can choose Multi values or ALL or a single value and everything works.

Now I find though, that it will not sort ascending correctly. Is there a way to force the sort other than applying the sort to the field selected and make it stick?

Thanks!

muffntuf :us: (BOB member since 2006-01-04)