@Prompt Functions: The Next Step – Optional Prompts

Posted on by 134 Comments

For most of us the @Prompt function syntax can be a bit confusing at first. Sure we catch on quickly, however, we often need to review the business objects @Prompt syntax available online or refer to a previously created @Prompt. After we get comfortable with the @Prompt and the available options such as custom list of values, constrained/free, mono/multi, and default/purge values (link to article), many of us are ready to take our @Prompts to the next level.

Prompt Conditions – Fancy Lines in a SQL WHERE Clause

Of course, if we are discussing @Prompt then we are discussing work done in Business Objects’ Designer. I suppose you could hard-code one in the SQL of a WebI report, but if you are doing that then I don’t think you are ready for the next level. Anyway, back to Designer, @Prompts are placed in condition objects (the little filters). Conditions become part of the WHERE clause of a report’s SQL. Therefore, much of what you can do with the WHERE clause in SQL can be replicated in Designer’s condition objects. This means that you can include AND and OR logic, for starters. This is where the magic starts, because “OR” logic is the drive behind optional prompt conditions. Anyway yo get the technical discussion started here is an example of basic business objects @prompt syntax:

Products.product_num IN @Prompt('Enter Product Number(s)','A',,MULTI,FREE)

Multiple @Prompt Applications Using @Variable

Let me take one step back and discuss the BO syntax briefly. @Prompts are translated into SQL by BO with the values submitted to them. Sometimes this translation is frustrating (dates date types for example). The interesting thing is that this translation can occur for each instance of the @Prompt in the report’s SQL. So you could place it in various parts of your report’s WHERE clause and each would be executed and evaluated. HOWEVER, the prompt values are all consolidated under a single @Prompt text string. This because the unique identifier of the @Prompt is the text displayed to the end user, you know the first string following the “@Prompt(“. Another way to reuse a prompt in the SQL of a report is to use the @Variable function with the exact same prompt text string. This allows you to use the input from the user without replicating all of the rest of the syntax in the @Prompt. Using @Variable is preferable where possible because it will avoid having to make changes to an @Prompt in too many locations (when changes are inevitably needed).

Creating a True Condition

SQL WHERE clauses function much like most logical programming; they evaluate to “True” or “False”. I don’t really want to get into a tutorial on logic, but it is important that you understand a bit about it. In short, this is what you need to know:

true and true = true
true and false = false
true or false = true
false or false = false

In order to create an optional prompt you will need to create a clause that has the ability to evaluate true based on a valid parameter value or a submitted keyword that you have configured for the prompt. Therefore you want an OR clause. Here is the key to the whole thing and it looks like this:


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

The keyword that is pre-configured in this prompt is: * (the asterisk character). So do you see it? If I enter a valid value for Employee.employee_id then my optional prompt clause will return those employees. If I enter a “*” character (minus the quote characters) the prompt clause will evaluate “TRUE” for all records and this will make as though the enter Prompt clause were not even present. It has been bypassed.

Standard Practices with Business Objects Optional Prompts

I have seen various keywords used over the years. The “*” (asterisk) is the most common. The word “All” is used by some, but when using letters you have to be prepared to handle or prevent mixed case responses. I have also seen the keyword added to a List of Values and the Business Objects prompt set to constrained to prevent upper/lower-case issues. This would for MONO or MULTI prompts, but please know that putting multiple values together with the keyword will still result in a complete TRUE clause/statement (or bypassing of it). Often the keyword is included in the prompt text or at least in any auxiliary training documentation. Remember, the keyword doesn’t help anyone who is not aware of it or how to use it. Finally, the keyword should not be similar, close to, or the same as any possible valid value for the prompt condition (nothing close to Employee.employee_id in this case); this would REALLY confuse the end-users of the BO prompt.

Optional Prompts in Business Objects XI 3

BO finally caught up with its competitors with the release of BO XI 3.0. In this release it began to include the ability to make report-level prompts optional through a check box. The unfortunate part is that this requires the user to create his or her prompts in the report and not follow the best practice of creating them in the universe. I haven’t yet seen any way to make a universe-level @Prompt optional based on configuration initiated at the report level. Perhaps this is by design; it allows the universe author to perfectly control both the composition and the optional nature of the prompt.

If you haven’t yet seen how to make a report level prompt option, please take a look at the image below. To get to this point you need (1) open the report or create a new one, (2) click on “Edit Query”, (3) locate a report-level prompt or create one, (4) click on the prompt properties button, it looks like a question mark in a blue circle over-lapping a small window with an “X”, (5) click on the “Optional prompt” check box, (6) click “OK”, (7) switch to the “Edit Report”, (8) test the prompt and save the report.
Business Objects XI 3.1 Web Intelligence Optional Prompt
Modify Report > Edit Query > Prompt Properties > Optional Prompt

Enjoyed this post? Share it!

 

134 thoughts on “@Prompt Functions: The Next Step – Optional Prompts”

  2. Hi Calixtus,

    Thanks for pointing this typo (I hope it was just a typographical error) out. I have just corrected the article. I really appreciate you taking the time to point this out.

    Thanks, Julian

  3. Hi Julian,
    We have requirement to show a description in the lov and when selected, we need a id of the row for the where clause. Could you please help as how to achieve through BO IX Release 3 universe.
    Thanks,
    Pat.

  4. Hi Pat,

    I haven’t done this in a while. So the old way to do with was to set the object’s LOV (in the universe) to be two objects in the results panel: Row ID, Description

    Then when you create a prompt that depends on this LOV you would get both values in each record displayed to the user, but only the first value of the record(s) selected would be passed to the SQL statement.

    This worked well in BO 6.5 Full Client, and it probably works in Desktop Intelligence. Nevertheless, I am not sure how it fares in Web Intelligence in BO XI 3.1. I will test this out and write a separate article about it when I get the chance.

    Please share your progress based on this short/untested answer to your question.

  5. Hi Julian,

    i ve written the query in lov using union to get ‘All’ part in prompt. Now i am getting the prompt values including ‘All’ .Now if i will go to my prompt i can see my data like this.
    All
    Austin
    Boston
    California
    etc

    i worte one filter condition as follows

    @Select(Store\City) = @Prompt(‘Enter a value ‘,’A’,’Store\City’,Multi,FREE)
    OR
    ‘All’ = @Prompt(‘Enter a value ‘,’A’,’Store\City’,Multi,FREE)

    using this in report level i am able to see the data for ‘All’ and also for individual cities. i Also want to see the data for multiple selection of cities. user must hve the privilage to select more than one city to view the data.

    How can i implement this?

  6. Hi Manu,

    Can you try this out?


    @Select(Store\City) IN @Prompt('Select or enter a Store/City','A','Store\City',MULTI,FREE)
    OR
    'All' IN @Variable('Select or enter a Store/City')

    I changed the prompt text to seem a little more descriptive for the end user and I added a @Variable. Please try it out and let us know hwo it works for you.

  7. Hi Julian,
    It is working with the this formula.

    @Select(Store\City) in @Prompt(‘Enter a value’,’A’,’Store\City’,Multi,FREE)
    OR
    ‘All’ in @Prompt(‘Enter a value’,’A’,’Store\City’,Multi,FREE)

    Thank you very much for your support

  8. Manu,
    Can you let me know how your report will respond if any user selects ‘All’,’Austin’ from the list, as the report accepts MULTI entry of LOVs

  9. Hi Abdul,

    I can answer this one… Since the operator is “IN” as it needs to be for a MULTI prompt. Then if the user select anything and the word “All” then the result would be the same as having just selected all. The entire prompt would be bypassed as a filter in the query.

    Right Manu?

  10. 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?

  11. Hi Manu, thanks for your question.

    If you set year to be a character object type then you must explicitly convert it to a character in the SQL. In Oracle this would be to replace “tablename.year” with “TO_CHAR(tablename.year,'#')“. Once you do this, your “All” will work, BUT you may see SQL query performance decrease because such functions can cause the database query optimizer to miss an index.

    Another method would be to stop using “All” as your bypass keyword and start using a number. For example, you could use “0” (zero) to indicate all years. Or perhaps zero would be confusing as some users may input “O” (the letter “O”). So consider “9999” perhaps.

    Please try it out and let us know how it goes.

  12. Hi Julian,

    it is working with TO_CHAR but as you said its giving some performance issue and i can’t replace ALL with ‘0’ because client want it as ‘all’.
    any other work around to improve the performance?.
    also when i am detecting the cardinality and say ‘ok’ it is getting hanged. any idea why it is happenning?

    Thanks for your response
    manu

  13. Hi Julian,

    When iam tring to see the Table Values of a particular table i am getting an error like “Exception:CS, Unable to rin the sql execute”.
    Can you tell me why it is happenning like this? and when i am trying to recreate the universe it is getting hanged.

    Please help me to resolve this issues

    Thanks
    Manu

  14. Hi Manu,

    1) I can’t really help you with the performance as this would require lots of knowledge about your database structure and model. You should consult with a DBA or SQL tuning expert on your side.

    2) I never test cardinality in the universe (Designer), I don’t find it to be a useful tool given the cost. It also will be impacted by SQL query performance.

    3) I don’t really use the “table values” functions in Designer often. I have never received this error. As far as I know the function just does a simple SQL statement like “SELECT * FROM tablename WHERE ROWNUM < 50". If you are getting errors, ensure you have a good connection to the database and that your user has rights to query the data in the table. If all of this is good then I might try creating a new universe from scratch using the same connection and see if I still get the error on that table. If you do not, then this might point to universe corruption. Best Regards, Julian

  15. Hi Julian,
    Thanks for your response.
    With regards to the error i think its because of database connection authority given by the user. i will check it out and let you know once it is gone.

    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

  16. Hi Manu, regarding a disappearing slice and dice… perhaps its a rights thing. This is pretty far off topic though. I’d like to keep these comments useful to readers of the original article.

  17. Hi Julian, Thanks for the useful tips. I have created the condition as above and parsed ok! but when i run the report it gives error as null(error INF). Please help

  19. Fara, I really don’t know what to do with that error. I always suspect corruption in such cases. Therefore I try to delete and recreate, or even to recreate in a different universe to see if the issue goes away through either test.

    Manu, Custome LOVs, good question. LOV = List of Values. Custom means that you have overridden the default LOV that an object gets by editing its query. You do this while editing the object on the second tab, as I recall.

  20. Hi Julian,

    At last, its now working after I did saparate LOV and my query/prompt is refering to the saparate LOVs. Thanks a lot ..

    But now I have one more requirement from users, which is to allow users to type partial names and automatically all names which having that partial will be dispalyed. Can it be done? has anyone try this? I am not sure if we need to handle this at SQL level or at universe level

    Thanks

  21. Good news on the separate LOVs, bad news on that requirement. I am not even sure that macros could handle that one. I don’t think it is possible without building your own SDK portal to the BO application and that would be A LOT of work and on-going maintenance.

  22. Hi Julian,
    using your info here I was able to create the following that works. So thank you.

    RPT_CASE.COUNTRY_ID IN(SELECT LM_COUNTRIES.COUNTRY_ID FROM LM_COUNTRIES WHERE LM_COUNTRIES.COUNTRY IN @Prompt(‘Select Country of Incidence or Enter ALL’,’A’,’General – General Information\Country’,multi,free)) OR (‘ALL’ IN @Prompt(‘Select Country of Incidence or Enter ALL’,’A’,’General – General Information\Country’,multi,free))

    But another question: not sure its totally related here but… for these optional prompts used in boxi r2.

    How does one get the UserResponse or ReportFilter to bring back what was entered into the prompt query. I can get it to bring back the contents of the field that was influenced by the prompt. In this case “country of incidence” but not the actual search criteria, as prompts dont appear in the “Data” view when in Edit Report view. I tried using an object instead of a true condition but my prompt sql wouldnt work. Any ideas? Or should I ask in the forum?

  23. Hi Sandra,
    Does UserResponse("Select Country of Incidence or Enter ALL") work? It should give you what ever value was submitted at refresh.

  24. Hi Julian,

    I have BO report that is accessed through Clarity Tool. When user selects Product and SubProduct that will be passed to the BO prompt “Product Prompt” and “SubProduct prompt” to get the results into Clarity but if a user selects Product but not SubProduct then Clarity will pass as null value to the SubProduct prompt. Requirment is if null values are passed to the SubProduct prompt it should get all SubProducts to the report.

    In simple words:
    if SubProduct prompt value is null then get all SubProducts else get only selected Subproducts. How do I need to code prompt if prompt value is null then get all results?

    Thanks

  25. Hi John, I think that this can be resolved at the database level handling the NULL in the SQL. In Oracle you could use the NVL() function around your @Prompt, but it would have to be a MONO Prompt.

  26. Thanks Julian for your quick reply. I think you did not get my question. Let me explain in detail again.
    Let’s say if NULL is passed to the SubProduct prompt then it should get all SubProducts (i.e. ALL = ALL) else it should get only specific SubProduct that have passed to the prompt. I am using SQL Server 2005 and BOXI R2.

    I am trying to write code this way but it doesn’t work.

    Select * from Product_Table
    where Product_Table.SubProduct = Case ISNULL(@Prompt(‘Select or Enter SubProduct’, ‘A’,’Product\SubProduct’, mono, free))
    When ” Then Product_Table.SubProduct
    Else
    @Prompt(‘Select or Enter SubProduct’, ‘A’,’Product\SubProduct’, mono, free)
    End

  27. Hi John, I think I did understand you correctly and I was thinking exactly what you are doing; I just think in Oracle:

    WHERE Product_Table.SubProduct = NVL( @Prompt(‘Select or Enter SubProduct’, ‘A’,'Product\SubProduct’, mono, free), Product_Table.SubProduct )

    I was hoping this would work. I don’t know much about clarity, but could you handle things there? Perhaps if you selects nothing for a prompt Clarity passes a keyword to the report, such as “ALL”, that you use to bypass, making the statement evaluate to True.

  28. hey guys thanks for this great tip it might just be what i am looking for.

    here is a question for you all.

    lets say i have a date prompt. if the user selects a date it will pull those fields. but lets say the user leaves the prompt blank. can i have it default to yesterdays date by using sysdate-1?

    I was thinking something like this:

    DateTable.encDate = @Prompt(‘Enter a Date)’,’D’,”,MONO,FREE)
    OR
    sysdate-1 = @Variable(‘Enter a Date)’)

    would this work?

  29. HI Julian,

    I have question regarding the prompts.
    My client wants following requrement.
    in the prompts user want to select only n list of values(say 10) out of m list of values.(say 100).If he selects more than 10,error message should come.How to do it?Please let me know if u have any ideas?

  30. I like your thinking, but prompts require some kind of response from the user unless you are using the native optional prompts in BO XI 3. If you want a prompt to default to a certain dynamic date then even XI 3 optional prompts won’t help you. Try something like this:

    • change the Prompt to an alphanumeric (“A”) datatype and then use a keyword as discussed in the article
    • DateTable.encDate = DECODE(@Prompt('Enter a Date(YYYY-MM-DD)','A',”,MONO,FREE), 'YESTERDAY','YESTERDAY',TO_DATE(@Variable('Enter a Date(YYYY-MM-DD)'),'YYYY-MM-DD')
      OR
      ( 'YESTERDAY' = @Variable('Enter a Date(YYYY-MM-DD)') AND DateTable.encDate = SYSDATE-1 )

    WARNINGS: I have not tested the syntax, but it should be pretty close. Depending on your table structure and data volume in that DateTable, you could take a big performance hit.

  31. Interesting requirement. I always think from an Oracle standpoint and I can’t think of anyway to capture this exception before executing the SQL. I mean there is no Oracle or BO functionality to limit the number of selections that I can think of right now. You might be able to do a number of things after the query executes though.

    After execution at the report level you could determine how many distinct values the user selected (best option is to parse the UserResponse() function on the prompt. Then you could put a big error message or use alerters to warn the user that they crossed the line. For now, that is what comes to mind. I’ll keep thinking though and welcome other’s ideas.

  32. Hi Julian,

    I have a requirement from the user that is automation of Scheduling. The user want to achieve this by putting all the Date prompts into an Object table. So when we add any of the prompts into the report and refresh it will not prompt us but will take the value directly from the object. Is it possible??

  33. Yes and No. It depends. OK, enough wishiwashiness. If you use the BO XI 3.X optional prompt, or no prompt at all, then the answer is in the query not Business Objects.

    You would join your query to that table with the desired date value and that is it. This is really generic stuff that people have used for a long time. If you can dynamically set that date value, or values in that table then you are effectively giving the query a prompt value, old-school style. Nothing wrong with that.

    The SQL would look something like:
    SELECT
    ...
    FROM
    ...
    New_Table
    WHERE
    ...
    Existing_Table.date_value = New_Table.date_value

    Of course, you would add the New_Table and “date_value” as class and object in the universe (don’t hard code the SQL, it will bite you later), but the answer is really in the query and not the @Prompt.

  34. Ye that sounds eawsy when there is one particular date avalue assigned but here my problem is the Business Date value returns Multiple values and not one single value. So how to go about IT? I don’t find a way

  35. my problem is the Business Date value returns Multiple values and not one single value.

    I suppose you are not providing enough information here then. The logic of the SQL I posted above does not care if the “New_Table.date_value” is just one record or 1,000 records. The SQL and database take care of that.

  36. Hi Julian,

    I have requirement where couple of filters has to be optional in a child report. where 2 Parent reports are linked to it through the hyperlink. In the First parent report it has all the prompts reuired to be passed to the child report this works fine. The second parent report has only couple of promts present in the child report. I need to make those promts which are not there in the second parent report as optional in the child report. Please help.

  37. I think the principles discussed here still apply very well. The child report’s prompts which are optional will need to be converted as described here. Then the parent report which does not have values for all prompts will need to submit the bypass keywords (hard-coded) that you setup for those optional prompts.

  38. A quick question?
    i just upgraded from XIR2 to XI3.1 having a problem when using an object that has @prompt function it works fine in Deski but when i try to make a query with an object that has @prompt function both webi and webirich client getting stuck.

    i have seen the same issue with XIR2 but over there i used to downgrade my java player here in 3.1 it is not considering downgraded java player as default at run time 3.1 install updated java player.

    i hope some body can help me with that

  39. Hi Bill, this is a new one for me. Could you post the troublesome conditions details and maybe we could help by trying to replicate and analyze?

  40. Hi Julian,

    I have read the normal BO forums to find a solution to pass a default value in the @prompt syntax for deski reports. I dont seem to find a solution to pass a default value to a prompt.

    could you kindly shed some light on this subject.

    Thanks

  42. Hi every body,

    I have a problem with @Prompt, so when I use only one value, the execution of the report is doing with success, but where I try to have a results with 2 values, It seems that’s returns a syntax error.
    after investigation, I’ve seen that when BO try to generate a SQL request, this last is generated with two useless parenthesis,
    this a part of a SQL request
    ( T_TCK_VOIX.SERVED_PARTY_MSISDN IN (@Prompt(‘Numero de l”appelant :’,’A’,,multi,free)) )

    when I delete the two parenthesis, It works correctly, but this is not the definitive solution.

    any body has any idea how to resolve , I will be grateful for.
    thanks.

  43. In such situations I try to get my hands on the exact SQL that executed with the report. This can either be done through the auditing record or even in the report itself (a function returns the SQL). This might give you a better idea of the problem. The syntax you show up above look pretty clean, well except that quote (“) character in (l”appelant) concerns me a little.

  44. Hi Julian
    thank you for your reply
    Is it correct to have the parenthesis just befor @prompt and another dor closing. ?
    because whene I edit the sql request with the correct function, I delete this Two parenthesis, after saving, the report is executing successfully.

    on the other hand (l”appelant) seems to be correct, because whene I execute the request in TOad, It works worrectly :-).

  45. Hi Youssef, Excellent point that I missed this morning! Yes, when creating @Prompts that are preceeded by an “IN” operator you should not surround the “@Prompt(*,MULTI,*)” function with parenthesis as Business Objects will substitute the entire function with the selected value(s) enclosed in parenthesis (this is triggered by the “MULTI” keyword). I now understand your syntax error; it makes perfect sense.

    BTW, you can put a SQL function on the “@Prompt()” function if it is not a “MULTI” one. For example, I have used the “UPPER( @Prompt() )” function successfully many times. Just FYI.

  46. Hi Julian

    My Probleme Is like That : users don’t Know how to edit SQL, for Doing the necssary Modifications.
    my question Is: how can resolve this automaticaly; IT means; is there any options do I change in univers or BO Options for resolving probleme; without users change manually the requests.

    I remain available for any information.

  47. Hi Youssef, Yes, I always recommend that you avoid editing the SQL of a report whenever possible. The proper correction is for you to edit the condition in the universe and remove the parenthesis around “@Prompt” function. Save the universe, export, now open the report again and confirm the change worked.

  48. Hi M. Julian

    It works correctly. thanks a lot.
    thanks :-).

    Have a good day and weekend.

  49. I am not sure what is the use of:-

    MONO/MULTI,FREE/ CONSTRAINED in @prompt?

    Can anyone please explain that?

    Thanks

  50. Hi,
    I had to create a date prompt where if user doesn’t enter anything in the prompt,the prompt should take it as null and run the query with date is null.I went through this whole article and tried to apply the logic suggested here.
    I created filter at Universe level where I set the default value in the prompt as blank.Below is the syntax:
    @Select(Cust Create Dt\Cust Create Dt_optional) IN
    @prompt(‘Enter Cust Create Dt:’,’D’,’Cust Create Dt\Cust Create Dt’,Mono,Free,Persistent,{”},)

    With this,I can see default value as blank while running the query at webi but the database level query is not running giving error as”The query cannot run because prompt contains invalid dates”

    I looked through BOB,there are topics on optional prompts but I could not find solution over this particular issue,Please let me know if anyone has any work around over the issue.

    Thanks in Advance.

Leave a comment

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