BusinessObjects Board

Prompts

building semantic layer
#1

Hi,

I am using BO 6.5 full client and SQL Server 2000

I want to add the word ALL to my list of values. If a user selects ALL from the LOV then the report should display with ALL values.

Thanks in advance

ksmurthys (BOB member since 2004-12-22)

#2

Look at this FAQ Designer entry: Now that my LOV lets me use ALL for all values, how do I get the ALL in the list?

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

#3

Hi Andreas,

How to do it in SQL Server 2000.

I think we don’t have dual table in SQL Server. I am sure about that.

ksmurthys (BOB member since 2004-12-22)

#4

You’re right, there isn’t a DUAL table in SQL Server, but that part is optional anyway - select sysdate from dual for ORACLE is the same as simply select getdate() for SQL Server. So Dwayne’s instructions for SQL Server would be:
Overriding the LOV SQL

  1. Press the SQL button in the query panel of the LOV
  2. Add the phrase UNION SELECT ‘ALL’.
  3. Click the “Do not generate SQL before running” checkbox.

Universe Object

  1. Create a universe object defined as a constant ‘ALL’ (don’t worry that it does not parse correctly)
  2. Press the combined query button in the query panel of the LOV (defaults to UNION)
  3. Include the new universe object.

Regards,
Mark

#5

Hi Mark,

Thanks for your suggestion

When I include the statement UNION SELECT ‘ALL’ I am getting an exception like

“All queries in an sql statement containing an UNION operator must have an equal number of expressions in their target lists. State:42000”

I am including this UNION statement at the end of my SQL statement. I have totally 4 prompts in my SQL.

ksmurthys (BOB member since 2004-12-22)

#6

Please, post the complete SQL code throwing the error


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

#7

code

ksmurthys (BOB member since 2004-12-22)

#8

This hardly can be the SQL for your LOV, what you posted looks like the SQL of a data provider used in a report.

By the way whenever you use UNION or UNION ALL you must have the same number of arguments of the same data type in the SELECT clause. In your code the first part selects three columns

SELECT 
  avg(dbo.Flights_Fact.TAXI_IN), avg(dbo.Flights_Fact.TAXI_OUT), 
  dbo.FLIGHT_DIM.FlightNumber)
...

whereas your second part of the UNION brings back only one column ‘ALL’

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

#9

Hi,

Yes the SQL posted is for the data provider in the report. I am new to BO where can I find the SQL for the LOV

ksmurthys (BOB member since 2004-12-22)

#10

Hi,
You can find the LOV for SQL in the properties tab of the object.In that tab choose the edit option form the "Associate a List of Values"frame, to get the lov sql

regards
Pradeep

pradeep.bm :india: (BOB member since 2004-06-09)

#11

ALL,

Thanks for your suggestions. It worked.

ksmurthys (BOB member since 2004-12-22)