BusinessObjects Board

All in the prompt

Hi,
I had one requirement which need to display the regions values in the prompt,with the ALL and make that as default value.

I am using this syntax to generate the region with the ALL

@Select(Controlineofbusiness\Colinesofbus) IN @Prompt(‘Control Line OF Business(Type ALL to show All Line of Business)’,‘A’,‘Controlineofbusiness\Colinesofbus’,multi,free)
or ‘ALL’ IN @Prompt(‘Control Line OF Business(Type ALL to show All Line of Business)’,‘A’,‘Controlineofbusiness\Colinesofbus’,multi,free).

but here i am able to Type the ALL and it dispalay the all values, but my requirement is i need to display the value ALL as the values of the region , so that i can set that as default value.

Any ideas to achive this.

Thanks
Geetha


geethanjali (BOB member since 2010-06-07)

Search the forums.

You would have found a sticky topic:

Thanks for the reply my requirement is different,

I amble to get the value ALL if i typed, but the user will not use the BO reports directly in BO, it will be gone throu interface with the other application, there user will not have place to enter the value as ALL, they will have just the drop down the values so i need to add the ALL in the list of values and if they select ALL then the report should run for all regions.

Thanks
Geetha


geethanjali (BOB member since 2010-06-07)

Please read the linked post again, it’s exactly what you described. As long as you are using a universe to build your query, and calling the LOV associated with the object, then the solution as given should work.

If you’re building the LOV on your own, without using a universe object, then you’ll need to take care of getting “ALL” onto the list yourself.


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

Thank for the reply,

i tried this option

Overriding the LOV SQL

  1. Press the SQL button in the query panel of the LOV
  2. Add the phrase UNION SELECT ‘ALL’ FROM DUAL or similar dummy table for your particular database. For SQL Server (if you don’t care if the object parses or not) add SELECT xx From xx UNION SELECT ‘ALL’ (You don’t need a FROM table)
  3. Click the “Do not generate SQL before running” checkbox.

i am able to generate the ALL in the list, but ifi select the value ALL, it says there is no data found and the query returns null.

but if i select any other regions then it display the regions name.

please help me out.

Thanks
Geetha


geethanjali (BOB member since 2010-06-07)

Check the prompt definition then. The logic you showed above looks like the standard code, and should work. Try getting it to work in Web Intelligence by itself first, and make sure it’s not your external code that’s missing something.


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

Geetha,

I think you are using Colinesofbus object instead of Prompt in your report.

Thanks,
Zaif


zaif235 :us: (BOB member since 2010-06-15)

Thanks for the help and support, now i am able to display the ALL in the LOV and generate the all values in the reports.

Thanks
Geetha


geethanjali (BOB member since 2010-06-07)

Hi,

I faced a similar issue and followed what was said in the earlier topics.

My issue here is I have three levels say country-> state-> city.

I have created LOV’s and also added UNION ‘ALL’ from DUAL to each object at universe level.

But in the report when I select all from any of the higher level prompts It just returns ALL as a value in list from the lower levels and not all the other values. How do I get all the other values along with ‘ALL’ in the lower level, If my selection is ‘ALL’ in the higher level…

Hope my question is clear

Can anyone help me out?


BOB newbie (BOB member since 2011-02-15)

I showed how to make a cascading prompt for Country -> State -> City in a presentation several years ago, called “Tails of a Universe Ninja”. You can download both parts from my blog here:


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

Hello Dave,

I have checked your presentation and It seems to be a good match for what I am trying for. I have gone thru all the steps but finally when I was trying for the report its saying query doesnt reference a table. can u tell me what are the objects and the conditions to be used in the report to be created.

And I have one more question Is it possible that If we sellect ‘ALL’ in the higher level of prompt It returns (‘ALL’ and other list of values) of the lower level object. If it is can I know how?

like if I select ALL in country level It should return ‘ALL’ + ALL the states of ALL the countries. Is it possible?


BOB newbie (BOB member since 2011-02-15)

As far as I remember, everything is laid out in the presentation.

That’s the way it works. However, you have to create the proper concatenations. Remember that for Webi, the cascading LOV must be followed all the way to the end. You cannot simply select “All” at the top level and stop.


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

I am trying to get it done using the DeskI.

When at the higher level if I select ‘ALL’ it just returns ‘ALL’ as my list of values at the lower level ,not ‘ALL+ other list of values’. I don’t know where I am making a mistake. Can you help me out with that.


BOB newbie (BOB member since 2011-02-15)

Deski works the same way, you have to select the lowest level.

Look at the second level LOV definition. It has to be a union, and it has to include the proper condition so that every element returns if the first level selection is set to ALL.


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

Hi,

My report requires that i need a cascading prompt with Sales Manager and his regions.
This is what i did

  1. in the designer/tools/lov/create a cascading lsit of values. then generate lovs.

  2. in the regions lov, i followed the steps:

  3. Press the SQL button in the query panel of the LOV

  1. Add the phrase SELECT ‘ALL’ FROM DUAL UNION SELECT DISTINCT TABLE.COL1 FROM TABLE
    WHERE ( TABLE.COL1 IN @VARIABLE('ENTER VALUE FOR COL1))
  2. Click the “Do not generate SQL before running” check box.

When i do this and then in webi create my report - everything works fine

i am able to generate the ALL in the list, but ifi select the value ALL, it says there is no data found and the query returns null.

please help me out.


johndreamer (BOB member since 2011-07-11)

@johndreamer, did you have a look at the presentation I linked earlier? The syntax you’re using is not correct for the condition. You should not be using @variable to provide a prompt either.

As a final note, you entered the same post twice, so the second one has been removed. Thanks for understanding.


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

Thanks for the Help Dave. I shall check it out once again.


BOB newbie (BOB member since 2011-02-15)

Use SELECT ‘ALL’ FROM DUAL and not SELECT ‘ALL’ FROM DUAL. Since you are comparing ‘ALL’ in the pre-defined condition, the LOV too must have ALL and not ALL


Mohan :us: (BOB member since 2002-08-15)

How can I set “all values” as a default value for a prompt if I can neither edit the universe, nor edit the SQL queries in the report?

The background is: I have a LOV datum that contains a lot of values - if I pass them in the URL to open the report, that exceeds the URL max length. So I wish to implement a work-around in the way that if no value is selected by user, the (optional) prompt shall use ALL VALUES as default value. Thus, the report would obtain all values without the necessity of having these passed by URL.

Note: it is not sufficient to simply mark all list entries and select them as default value, since new values may enter the DB later, and these shall always be automatically included, without editing the report!


triangulum :switzerland: (BOB member since 2011-10-21)