Extended @Prompt Syntax: Default Value, Purge Values

Posted on by 139 Comments

During my report development efforts the other day I knew that I needed a business objects universe-level prompt, but I also knew that it was mandatory to “purge” the prompt values. Like a good little Business Objects developer I always purge all of the data from my reports and reports’ prompts before I publish them for User Testing and promotion to production; however, in Business Objects XI R2 I have found that WebI reports have a strangle-hold on prompt values and they don’t want to give them up without a fight.

So I started looking around to see if I was just missing something obvious. What I found on my search was some completely unsupported extended @prompt syntax. The funny thing was that I did not have to install anything to enable it. The syntax worked immediately, just by passing in the additional parameters. Note: this is unsupported officially by BO, but BusinessObjects obviously developed it for some larger client and slipped it into the XI R2 code. Therefore, there is no promise that this will be supported in the future, but there is hope.

Anyway, on to the syntax:
@Prompt('1) Enter Product Family','A','Product Hierarchy\Product Family',multi,free,[Not_Persistent],[<{default_value(s)_or_leave_blank}>],[User:#])

With this syntax you can now add 3 additional/optional parameters after you universe-level prompt’s “free/constrained” option.

  1. Prompt Persistence: Default value is “Persistent”; however, the value “Not_Persistent” is the reason most Business Objects WebI report writers want to use this syntax.  Using “Not_Persistent” removes the last prompt used with the report; it purges the value.  This can be very important in many settings because it can be the difference between an end-user accidentally running the report for some unwanted parameter value.
  2. Default Value(s): If left blank no default value used.  If a value or values is desired then the set should be surrounded by { } characters and separated by commas (much like hard-coded LOV values earlier int he @Prompt parameters).  This feature might seem to counter the “Not_Persistent” setting; however, it may be possible that you always want your Web Intelligence report prompt to include the default “*” value even while you are testing or running it for other values.  Therefore, some might find that using this option with the “Not_Persistent” option provides value and extended functionality to their WebI reports.
  3. User:#: This particular value has been ignored by many, but it also provides some interesting value.  It allows the universe business objects developer to define the relative order of the prompt.  Yes, no more not-so-tricky “1.”, “1)”, or “1-” prefixes.  In fact, I believe this option provides better value because it defines order of universe-level prompts and it two prompts have the same “User:#” value then they are sorted alphabetically.

The following are some examples of potentially usage of this extended @Prompt Syntax:

@Prompt('Enter Product Family','A','Product Hierarchy\Product Family',multi,free,Not_Persistent,{'Kiwi','Guava'},User:0)
@Prompt('Enter Product Family','A','Product Hierarchy\Product Family',MULTI,FREE,Not_Persistent,{'*'},User:9)
@Prompt('Enter Product Family','A','Product Hierarchy\Product Family',multi,free,Not_Persistent,,User:1

I have found this syntax to be invaluable in my BusinessObjects Designer and Web Intelligence report development efforts.  As good little programmers we know that universe-level prompts are a “best practice”; however, without this syntax they can retain last-run prompt values and cause confusion and even misleading and blatantly wrong business intelligence.  This syntax finally brings universe-level prompts into the 21 century and makes our jobs all a little easier and maybe even more secure.  😉

Tip: Including “All” or “*” In Your LOV

Try hard coding “All” or “*” in the object’s LOV, in the universe. You could do this by editing the object’s LOV and adding a query with UNION that will only return the word “All”. You may have to make sure that the LOV for the object is returning a character data type for this to work properly.

Enjoyed this post? Share it!

 

139 thoughts on “Extended @Prompt Syntax: Default Value, Purge Values”

  1. I use BO XI 3.1

    Here we can use the option “Prompt Optional”
    With this flag selected the use can ignore the “filter” and BO create a SQL statement without this condition.

    This is the syntax that you can see with the “SQL” botton:

    SWT_IST.IST_TPV_COD In @prompt(‘Variante:’,’A’,’Products\Variant Type Codice’,Multi,Free,Persistent,,User:0,optional)

    Unfortunately it is not possible use this syntax creating an object “condition” during the universe definition.

    Do someone know any trick to solve this problem?

  2. I am trying to use the prompt for a date in BO.
    I have tried it at both the universe level and the Web intelligence level. After clicking the run query button, infoview hangs and I have to close it down. Any idea what the cause may be ?

  4. John, we had the same issue – refresh query on a prompted report just hangs. Save it, navigate to the report folder and run it as if you are the user – if you don’t get the prompt(s), select View, Left Panel to see and select the prompt(s), run query.

    In the report properties you can set Refresh on open, but then you may see a similar situation if you Modify from the report folder. Instead you have to go run the report, selecting the prompts, and then use Document, Edit.

    Hope this helps you, if anyone knows how to fix this please post!

  5. When InfoView hangs after click run and the prompts do not display – the issue is usually the version of Java on the PC. BO is not compatible with new versions of Java. You need to be on Java version 1.5 or lower.

  6. I used to believe that BO’s Java Report Panel couldn’t handle higher versions of Java on the client machine, but then through a mistake on my part I installed the latest Java version on my client machine. Since then I have been using it successfully to develop new reports and modify existing ones. I am currently running “Java 6 Update 11” (a.k.a. 1.6.0_11-b03).

  7. Pingback: Optional Business Objects Prompt - Using BO Designer | Business Objects Tips

  8. Is it possible to use a formula in the default area? I want the prompt to default to the current month when a month isn’t selected, and this can be gotten from (sqlserver) datepart(mm,getdate()) – however that doesn’t seem to work in that spot in the prompt.

    What about referencing another object that does this calculation? Or is there a better way to do this?

  9. I don’t think that you can use any formula\logic here.

    You might try setting up your SQL to substitute the current month when a keyword such as “Current Month” is input. This is done by extending the idea of an “Optional Prompt” to include more logic. I should write an article on this soon, I hope.

  10. Hi,

    Using WebI we can make the prompt optional. “optional” is the last parameter to be passed. But if we use the syntax with universes, it gives and error stating :
    Invalid Definition ” UNV0023: Error parsing default values parameter (7th parameter)

    @ prompt from WebI:
    @prompt(‘Enter Order Status:’,’A’,’Order Details\Order Status’,Mono,Free,Persistent,,User:0,optional)

    If The prompt is answered, the condition is applied, else all status values are retrieved.

  11. What version of BO are you using? As far as I know optional prompts are not supported until BO XI 3. Also please check your version of Designer. Maybe you are using XIR2 Designer with a BO XI 3 CMS.

  13. Hi Namit,

    Have read the article called “Optional Prompts“?

    Look at the example which uses the “*” character and replace it with the string “All”. This enables the functionality of a user submitting such a value. To put it in the LOV then you would have to take that example:

    Employee.employee_id = @Prompt('Enter a value (* to bypass filter)','A','',MONO,FREE)
    OR
    '*' = @Variable('Enter a value (* to bypass filter)')

    and make a small adjustment

    Employee.employee_id = @Prompt('Enter a value ("All" to bypass filter)','A','All',MONO,FREE)
    OR
    'All' = @Variable('Enter a value ("All" to bypass filter)')

  14. Maybe this will work as optional prompt.

    select Product Family from Product
    UNION ALL
    Select ‘ ‘ from dual

    Product.Product_Family in @Prompt(’Enter Product Family’,’A’,’Product Hierarchy\Product Family’,multi,free,Not_Persistent,{‘ ‘},User:0)
    OR
    ‘ ‘ in @Prompt(’Enter Product Family’,’A’,’Product Hierarchy\Product Family’,multi,free,Not_Persistent,{‘ ‘},User:0)

    The default value is blank. So if the user will not enter anything, it will take the blank value for the prompt.

    I haven’t tried this, just thinking whether this will be another option that might work.

  15. I am not quite sure about the syntax Oranje Boy, but even if it doesn’t fly 100% I like the idea of setting it up so that a blank value would be the designated bypass keyword. I need to take a moment and test this because something always scares me about making assumptions about how blanks/nulls are handled by different apps.

  16. I am obviously doing something very wrong here because I cannot get these prompts to work for me. They default the values I want and they also show up in the order I want, but they do not render results in the report when I use these parameters.

    The syntax I’m using for two prompts is as follows:

    @Prompt(‘Please Enter School Division:’, ‘A’,,,{’10’},user:1,),
    @Prompt(‘Please enter School District:’, ‘A’,,,{’20’},user:0,)

    When I run the report with the appropriate result objects, no data is returned. When I remove the parameters at the end (for example the {’20’},user:0,) )the results show up in the report just fine. Any suggestions would be SO appreciated!

  17. BO Sleuth,

    Can you comment on the difference in the SQL generate by each report (with and without this extended syntax)?

    If I understand you correctly you are saying that when you refresh the report with those extended syntax prompts in the report the default values are populated in the prompts (10 and 20) and when you click Submit for the parameters you get “no data returned”? But when you run the report with just “regular” prompts and you manually enter those same default values you do get data?

    Please let us know.

  18. Hi Julian,

    You understood absolutely correctly. However, I do not get a message that says, “No data returned”, I just get an empty report. When I omit that extended syntax and enter the values, the report is populated with the data I need. When the generated SQL is compared, the only difference is the extended syntax in the prompts. Otherwise the code is identical. I’m likely missing something simple but I cannot see what it could be. Also, even though I specify not_persistent in a test I did with the data, the prompts retain the previous value if the user decides to change the default value in the prompt. Any ideas on how to make that part work?

    Thanks SO much!

    Sleuth.

  19. I figured it out. The @Prompt syntax is fine (works with or without the comma at the end), but there was a variable in the report that looked for the former @Prompt text (which was prefixed with letters to force the ordering) and the report variable returned values with the UserResponse function. Because I removed those letter prefixes from my @Prompts, the report variable failed and no data was rendered. When I changed the report variable to remove those letter prefixes, I got report data! Sometimes we can over think something to the point where the basic things just fade into the woodwork.

    Thanks!
    Sleuth

  20. I now understand how to add the value ‘All’ in the list of values of a prompt. Thanks much for that. How to show the value ‘All’ in the lov section in the prompt including the actual values in the object. For example, for Year prompt, I want to show values within the lov as 2009, 2008, 2007,etc., and also value ‘All’. At the same time, I don’t want to hard card code the actual years. I was trying something like below but it doesn’t work.

    @Select(Date\Year) IN @Prompt (‘Enter Year or “All”‘, ‘A’, {‘Date\Year’, ‘All’}, multi, free) OR ‘All’ IN @Prompt (‘Enter Year or “All”‘, ‘A’,{‘Date\Year’, ‘All’}, multi, free)

    where Date\Year is class\object

  21. Raj:
    I would go a different route. I would hard code “All” in the object’s LOV in the universe. You could do this by editing the LOV and adding a query with UNION that will only return the word “All”. You may have to make sure that the LOV for the years is returning a character data type for it to work.

    BO Sleuth: You really are a BO slueth after all. Thanks for keeping us updated on your results.

  22. Like you mentioned I added ‘All’ in the object’s LOV and it worked. Thanks much for your help.

  24. I ve to implement ‘All’ (it should be dafault) along with my other parameter list. So for the prompt City it should show like

    All
    Austin
    Boston
    Chicago
    etc
    If i will select i he to show all the city values.
    Can any one help me for that?

  26. Hi Julian,

    i red the document and i am not clear in the @variable part where OR is used, and still i am not getting what i want. Now i ve ‘All’ and rest of the list when i select a prompt. but when i select ‘All’no data is returned.

  27. The best method would be for you to post what you have and I will see if I can find the problem.

    Also, since this topic has more to do with the other article. Please post your follow-up response on that article’s comments.

  28. Hi Julian,

    I am facing an issse with respect to ‘ALL’ implementation.
    I am implementing ‘ALL’ with ‘YEAR’ which is of Type ‘Number’.
    So when i am running the report with ‘ALL’ i am getting an error as “Error during SQL Execution: Exception DBD,ORA-01722: Invalid Number State:” I tried casting the ‘Year’ to varchar but it is not working. i don’t know how to cast ‘ALL’ to Number format. Can anyone help me for that?

  30. Hi Julian,
    Thanks for your response.

    Will there be any situation where in we will not be able to see Slice and dice part. Currently for my report slice and dice is not opening?

    Thank You
    Manu

  31. Hi,

    i am developing a universe and this universe will be used to create my reports. i finished universe creation and when i am creating reports i am getting multiple queries instead of a single query.

    can any one help me to get a single query?

  32. Hi Manu,

    I don’t want to turn away anyone, but your question seems unrelated to this article. Perhaps you should post your question on a forum (see our Links pages for some good ones).

    I will give you a hint. The joins that you created in your universe, the universe’s contexts, and the objects that you selected for report are most likely the cause. Your universe doesn’t seem to be setup to support the kind of query you want to create in your report.

  34. Hi Julian,

    I have scheduled a report for the 1st day of every month. Apart from that I need to use prompt as well, so that previous reports can be seen. When the schedule runs for 1st day of month it should take previous month data which is happening right now but when user runs it manually there should be prompt for the date.

    Thanks in advance!
    Lico

  35. lico, What you need is a special keyword prompt that applies different logic based on the value submitted. This is very similar to “Optional Prompts“. Basically you need to build some logic into your SQL that looks like this:

    WHERE
    ...
    (
    TO_CHAR(table1.date,'YYYY-MM-DD') >= @Prompt('Enter Start Date or LAST_MONTH',...)
    OR
    ('LAST_MONTH' >= @Variable('Enter Start Date or LAST_MONTH') AND (TO_CHAR(table1.date,'YYYY-MM-DD') >= ADD_MONTHS(TRUNC(SYSDATE,MON),-1))
    )
    ...
    The above is based on Oracle syntax, it is meant as an example of what you could do. Read the other article and post any follow-up questions there.

    By the way, the easier way to do this in the short-term is to create two reports, one with a prompt and one that always runs for the previous month.

  36. Hi,
    I have one issue (Prompts)in Business objects report.

    My requiremnet is :

    The prompt should asks the “ENTER VALUE” or ALL (default).

    ALL — > Means it should not give the list of values in the Report, just it should display only ALL.

    Because totally I am having 3 differnet prompts(columns),i want to select eiether of the 3 prompts.

    Ex : 3 fields

    1.Column1 (prompt : ENTER VALUE ,Default is ALL)
    2.Column2 (prompt : ENTER VALUE ,Default is ALL)
    3. column3(prompt : ENTER VALUE ,Default is ALL)

    It should be work as below :

    1st Scenario:

    Column1 — >200(some value),Column2 — > All ,Column3 —- >All

    2nd Scenario:

    2. Column1 —-> All,Column2 — > 30(some value),Column3 —- >All

    3rd Scenario:

    3.Column1 —-> All,Column2 — > All,Column3 —- >400(some value)

    I tried, but it is giving List of values(ALL) in the report…but it should not give list of values in the report.

    Please let me know if any one having idea how todo this.

    Thanks in advance
    Raj

  37. Hi Raj,

    My first understanding is that you would like to disable a LOV depending on what values a user chooses. If this is correct, then what you are trying to do is not possible in WebI and it may not even be possible in DeskI with the use of macros.

    My second understanding is that you would like to not return any data (you are calling it a “List of Values in the report”) for the column to which a prompt is assigned in the query if the user selects “ALL”. If this is correct then there are only two ways this could work if you are using a single query for your 3 prompts. Either it bypasses the filter or it would only return database records for which the column to which the condition is applied is equal to the string “ALL”.

    If I am misunderstanding your requirement, could you please rephrase it at a higher, more abstract level?

  38. Hi Julian, I have been googling Boxi items and keep getting sent to your site, which is a brilliant site and found the above items on Prompts extremely helpful.
    I have a question? If I wanted to have a prompt that allowed a user to choose 2 items of text: Confidential or Non Confidential. Which ever the user chooses appears on the report. Is this even possible in Boxi R2 Webi?

  39. Hi Julian,

    I am trying to create a date prompt and the default value should be sysdate (today’s date / current date). Can I reference an object in default parameter? I created an object ‘Today Date’ that shows today’s date and used it for default parameter. But it’s not working.

    @prompt(‘Enter Time Frame Begin:’,’D’,’Date Dimension\Calendar Day Date’,MONO,FREE,not_persistent,{‘Day Related Objects\Today Date’})

    Is there a better way to do this?

    Thanks for your help,
    Raj

  40. Hi Raj,
    I really like your idea, but I have never tested such a thing. My gut is telling me that your results have probably answered your question. 🙁

    The only other way I can think of is to build a keyword of “TODAY” into your SQL but then you would have to make the entire prompt character type and so your dates would be “2010-02-03” or something like that and then performance would likely go downhill as the SQL gets complicated… Sorry.

  41. Hi Sandra,
    I think you are asking if it is possible for a user to choose a value for a prompt and then have that value be used in the report as a label without that choice affecting the the query results. Such as a user is generating any report and they can decided if they want that report to receive a “Confidential” or “Non-Confidential” label. I hope I am understanding you correctly, because I like this question as I understand it.

    If the above is correct then the answer is “Yes”. What you would need is to build in logic such that no matter what response is given to the prompt the entire clause always evaluates to true. Therefore you need an “OR” in there, an always true statement, and some parenthesis to bind up the clause. Or you could just forget the “OR” if you can create a prompt that is always true. Like this:


    ( customer.last_name <> @Prompt('Choose Desired Confidentiality Label','C','',MONO,FREE) )

    Now the prompt has nothing to do with the field “customer.last_name” but I am betting that no record in the database has “Confidential” or “Non-Confidential” in this field. The better solution may be to limit the user to a hard-coded list of “labels” by create a custom LOV right there in the Prompt and setting it to “CONSTRAINED”.

    By the way, thanks for the compliments..

  42. That is exactly what I was asking Julian, THANK YOU!!I will try it tonight and keep you posted.

  43. I wanted to know if there was a simpler solution :). Thanks as always for your suggestion.

    Raj

  44. Hi Julian,

    I have defined the below prompt in the universe.

    @prompt(‘Enter Time Frame Begin:’,’D’,’Date Dimension\Calendar Day Date’,MONO,FREE,not_persistent,{‘1/1/2009’})

    When the prompt is used in the report, timestamp ’12:00:00 AM’ is attached at the end – ‘1/1/2009 12:00:00 AM’ for the default value and LOV’s. The LOV reference object ‘Date Dimension\Calendar Day Date’ has only date values without timestamp. How can I get rid of the timestamp in the prompt? And the weird thing is the timestamp doesn’t show up all the time for this prompt.

    Sorry if this is the right place for my question.

    Thanks,
    Prapoorna

  46. I have always wondered about this myself. I can tell you that when you are working in Webi Java Panel you will see different prompt behavior than you will through InfoView (like a user would do). Make sure to test your prompts through InfoView, this is what matters.

    Not much of an answer, I know. If you really want to get rid of timestamps than you will probably have to convert everything to character data type and for many reasons (mostly performance and headache) you don’t want to go there unless it is a must.

  48. Hi meredianblues, I can’t seem to understand what you are referring to with “aggregate navigation”? Please explain your question a little more with some context. Thanks.

  49. Hi Julian,

    I am getting an error when implementing the ‘*’ in a prompt like so, @Select(Order\Order Number) IN @Prompt(‘Enter Order Number(* for All): ‘,’N’,’Order\Order Number’,multi,free) OR (‘*’ IN @Variable(‘Enter Order Number(* for All): ‘)). The problem is the incompatible data type between the LOV (Order\Order Number)which is numeric while ‘*’ is a character. Can this be done in the Universe Designer? And please show me how.

    Thank you for your assistance.
    Kel

  50. Kel, you bring up an excellent point that I failed to highlight. The ‘*’ design depends on a character data type. You could convert your object to character and resolve this issue; this could be done in the prompt only, so that the object is not impacted, but performance may take a hit. In Oracle I would make your prompt like this:

    (
    TO_CHAR(@Select(Order\Order Number),'#') IN @Prompt('Enter Order Number(* for All): ','N','Order\Order Number',multi,free) OR
    ('*' IN @Variable('Enter Order Number(* for All): '))
    )

    I hope that helps.

Leave a comment

Your email address will not be published. Required fields are marked *