BusinessObjects Board

Creating a prompt where user can select "all"

Most likely you are trying to union Data type CHAR/STRING with Data type NUMBER or DATE.

SELECT ' ALL' FROM DUAL 
UNION 
SELECT DISTINCT Tablename.NumberColumns FROM Tablename

This example would not work.

You have to convert the NumberColumn to a CHAR/STRING first.


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

Thanx, the second query was a number and I converted to char and it works fine and even ALL is comming fine in LOV.

Thanks
VA


vanneb (BOB member since 2002-11-14)

Hey Guys I did used this ALL as u guys suggested, and it is comming in the LOV too but when I try to run a report using the object and select ALL it gives an error saying “invalid number : -1722”

Any idea how to solve this.

Thanks
VA


vanneb (BOB member since 2002-11-14)

Please, post the BO generated SQL giving you this error.

Look at the columns which are being joined and see if their DATA types are compatible (looks like you are trying to join a Data type Number column to a Data type Char/String column).


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

SELECT ’ ALL’ FROM DUAL
UNION
SELECT DISTINCT
to_char(CUSTOMER_HIERARCHY.CUST_CODE)
FROM
DW.CUSTOMER_HIERARCHY CUSTOMER_HIERARCHY
WHERE
RCV_RPPS_MEMBER_HIERARCHY.DW_ICA_CODE = @variable(‘Select DEALER:’)

Your right I converted Number to char as used teh above code.

Do you see any problem with this code.

Thanks
VA


vanneb (BOB member since 2002-11-14)

When you Refresh this LOV in BO Designer it works fine I assume?

What about the BO Document you built that is giving you the Oracle error?
Can you post that SQL?

How are you creating the prompt? Please, look the the BOB - Designer FAQ to see how to build a predefined condition which prompts the end-user and allows the value “All” as input".


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

Hey Andreas thanx for the replies, all I am doing is using the object( In whose LOV I included ‘ALL’, It does has nested LOV which prompts for Dealer when I select it).

This is the code that BO Doc is generating:

SELECT
CUSTOMER_HIERARCHY.CUST_CODE
FROM
DW.CUSTOMER_HIERARCHY CUSTOMER_HIERARCHY
WHERE
CUSTOMER_HIERARCHY.CUST_CODE = @variable(‘Select Customer:’)

Thanx
VA


vanneb (BOB member since 2002-11-14)

Your condition doesn’t include the logic to handle the word “All”. If you’re mixing numeric data with character data, not only does the LOV have to match, your eventual source data (for your condition) has to match as well.

Is customer code a numeric? If so, then it will not match “All”. You’ll need to convert it to character. And if you do that, you may lose an index (if there is one).

Dave


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

:hb: :hb: :hb:

OK…I’ve searched here. I’ve searched the KB and KX. I’ve found a million possible causes for my problem, but nothing definitive.

I’ve created my custom prompt so that “ALL” (in one form or another!) is displayed at the top of my LOV. I set it as multi and constrained. It works beautifully in full client. It works in ZABO. It is a synchronous connection. I’ve set the LOV to be exported with the universe.

In Webi, there is no LOV…and I can enter anything I want! This worked correctly before I added the “ALL”. The one suggestion in the KB says that this can be caused by the @prompt LOV being based on the same objects as where the WHERE clause is located and that this creates a loop. I can’t imagine that’s the cause because then it shouldn’t work in full client either, right?

Here the code for my object. The object is in a Class called Prompts. This objects is called Prompt Location.

Here is my select…

@Select(Registered Users\Facility)

Here is my where…

(@Select(Registered Users\Facility) IN @Prompt('Select Location or ALL','a','Registered Users\Facility',multi,constrain) ) OR ('.ALL' in @Prompt('Select Location or ALL','a','Registered Users\Facility',multi,constrain) )

Here is the sql generated by the query in full client…

SELECT
  ( LOCATION.FACILITY )
FROM
  LOCATION
WHERE
( (( LOCATION.FACILITY ) IN @Prompt('Select Location or ALL','a','Registered Users\Facility',multi,constrain) ) OR ('.ALL' in @Prompt('Select Location or ALL','a','Registered Users\Facility',multi,constrain) )  )

I’m so confused!!!


Eileen King :us: (BOB member since 2002-07-10)

Are you using WEBi SDK?


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

No…it is a full client report that I am sending to the corporate repository and trying to refresh in Webi. Nothing fancy!!!


Eileen King :us: (BOB member since 2002-07-10)

Ok… but are you using WEBi SDK? Does the URL look like /wi/…or like wiasp/ or wijsp…?
Also, if you can just try to remove all those parenthesis floating in the SQL code.


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

Just wi…

I can’t edit the sql code in webi…that’s the sql code from full client.


Eileen King :us: (BOB member since 2002-07-10)

Are you saying even though the @prompt provided a LOV you never saw a LOV in WEBi (even before you changed the code to include All)?
Did you export your LOV with the universe?

Modify the BO document in BO Full Client, modify the SQL, check “Do not generate SQL before running”, send the modified BO document to your BO inbox. Then use WEBi to retrieve and open this document.


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

According to my user, the LOV was there before I added the “ALL”.

I did export the universe and clicked the export LOV with the universe. I also clicked export the LOV on the objects that the select and where clause are coming from. I’ve tried to make them editable by the user based on some of the things I’ve read.

The whole complication seems to come from adding the

SELECT '.ALL' FROM DUAL 
UNION 
SELECT DISTINCT
  LOCATION.FACILITY
FROM
  LOCATION

Eileen King :us: (BOB member since 2002-07-10)

Change you LOV SQL code to:

SELECT 'ALL' FROM DUAL 
UNION 
SELECT LOCATION.FACILITY 
FROM LOCATION 

Does it work then? After you exported the universe etc.?


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

I did everything you suggest. Nothing worked.

I tried creating a Webi document with the exact same object. No problem.

:reallymad:


Eileen King :us: (BOB member since 2002-07-10)

What about deleting the local universe copy and associated LOV files from the WEBi server so WEBi is forced to import a new/fresh copy of the universe together with the LOVs from the BO Repository?

Then take a look at the LOV file on the WEBi server and see if it is correct.

Finally, create a new BO Full Client document using this “problem” predefined condition for your 'All" prompt, send it to your BO inbox and view it through WEBi?

I am running out of ideas…


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

I took another object (with nothing special in the lov)…created a full client object, put it in the report and in the prompt…sent it to Webi…refreshed…I have the LOV. I just don’t get it!!!

And it’s got to have the right LOV on the Webi server or I wouldn’t be able to create a Webi document and get the right LOV…right? :wah: :cuss: :crazy_face:


Eileen King :us: (BOB member since 2002-07-10)

For what it’s worth, I didn’t think that Webi supported UNION as part of a query. Maybe it’s that problem that’s causing the issue.

Here’s a thought, if you can do this… go into the database and create a view that does your union. That way your LOV is a simple query. Base your LOV object on this view (instead of the base table) and export it. See if that works in Webi. :crazy_face:

Dave


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