@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 JustStartedUsingBO, this really is a SQL logic question more than a BO one. I can’t think of how you can tell the SQL to sometimes accept a single value and sometimes accept multiple values. Using “IN” is usually how you do this. I think your only option is to build in some kind of “CASE” logic, but I am not sure how you would do that.

  3. I have Included ‘ALL’ in my lov’s successfully.
    but when i use the object(whose LOV has ‘ALL’) in filter condition .
    i get multiple prompts
    can anyone help me

  4. Hi Shri, I suspect that the text in your @prompt’s and @variable’s is not perfectly identical. It must be, or else you will receive a prompt for each variation of a string following evert @prompt and @variable.

  5. I am building a prompt on a date (date type) object in the Universe. How can I leverage you idea above of creating a True condition to do this?

    Here is the syntax I’m trying and it does not work:
    ARADM.PS_STDNT_CAR_TERM.WITHDRAW_DATE <= @Prom('date Please','D','',mono,free,not_persistent,{to_date('01/01/01')})
    OR
    '01/01/01' = @variable('date Please')

  6. Hi Uma, if you have XI 3.x consider using WebI to define the prompt as optional. This is the easiest method (the article needs a update to highlight this). However, to do this “old school” I would do this:

    ARADM.PS_STDNT_CAR_TERM.WITHDRAW_DATE <= @Prompt('date Please','D','',mono,free)
    OR
    TO_DATE('01-JAN-2001','DD-MON-YYYY') = @Variable('date Please')

    I cut out some of the optional parameters, but just trying to set this up to bypass the prompt (in Oracle) if the user submits the date “Jan 1, 2001”.

  7. Hello All,

    I am developing a report which require a drop down list in the report level. Do we have an option in Deski (XIR2)

    Appreciate your help.

  8. BO User, your requirements are quite vague. A report prompt with a list of values defined could meet your needs. Also, you could add a drill filter that might also meet your needs.

  9. Hi julian, this really work for me, thanks!.
    But i’m ask something, for example: i want to link my report from other webpage, can i send the value for the @prompt when the webpage call my report?
    thanks a lot!

  10. Hi Natalia, yes, OpenDocument supports the passing of @Prompt values. The parameters to use are “lsS” (single value prompts) and “lsM” (multi-value prompts).

  11. Julian, wow how quick you answer!! but how i use this? thinking something like :
    <aref="url&IsS=”>
    is this true?

    (sory for my english, can express my self with my poor english jajaja)

  12. Hi, i read of OpenDocument() found the aspx and jsp script, i’m with TOMCAT so, how i link to the script?

  13. so.. i’m now calling all right the openDocument script. But doesn’t work at all… so please help me!
    this is the url report that i use like index:
    http://dwhserver.intranet:8080/businessobjects/enterprise115/desktoplaunch/InfoView/CrystalEnterprise_Webi/view.do?objId=640375
    and in the link i want to linked other report in the same document; is use this url:
    =””
    +”ir a Tab3″
    + “    ”

    but it response: “There was an error retrieving data from the server:
    The object was not found.”

    what i do wrong?

    (sory for all the messages)

  14. so.. i’m now calling all right the openDocument script. But doesn’t work at all… so please help me!
    this is the url report that i use like index:
    http://dwhserver.intranet:8080/businessobjects/enterprise115/desktoplaunch/InfoView/CrystalEnterprise_Webi/view.do?objId=640375
    and in the link i want to linked other report in the same document; is use this url:
    ../Program/openDocument.jsp?sType=wid&iDocId=640375&sReportName=tab3&sRefresh=Y&lsSModulo=”+CodificarHTML(“181-0”)
    i want to pass the Modulo number and this prompt calls Modulo.

    it response: “There was an error retrieving data from the server:
    The object was not found.”
    what i do wrong?

    (sory for all the messages)

  15. Hi Natalia,
    I would suggest that you consider using the “CUID” and not the “ReportName” of “Object ID”. This is a best practice since it continues to work even after the report is migrated to another environment using Import Wizard. “Object ID” does not, those IDs are unique to every environment and “ReportName” is a non-unique value even within the same environment. So, once you know the target document’s CUID you could do something like this:
    iDocID=Aa6GrrM79cRAmaOSMGoadKI&sIDType=CUID

    This would solve your object not found issue. You can use Query Builder to find your object’s CUID with this script:
    SELECT *
    FROM ci_InfoObjects
    WHERE si_id = 640375

    Keep the messages coming until we get this figured out.

  17. I am working on SAP BO XI 3.1.
    I have a report which contains two separate queries (data providers), each one having its prompts.
    When user is in view mode and selects to refresh all data providers, then everything works as expected (the prompt dialog box pops up with all prompts defined).
    However when trying to refresh only one data provider, then the prompt dialog box pops up empty!
    When switching to edit mode, you have again the ability to run the queries separately and get the prompt dialog box properly.
    Any ideas why this happens?

    thank you in advance!

  18. Hi Petros, I would make sure that the user is using a supported web browser, not IE 9, if IE 8 the user must use compatibility view. Also make sure that zoom is set to 100%.

  19. Thank you very much Julian, I will try that and provide my feedback, as soon as I go back to the client. I will also try the same scenario and see what happens, on another client who has BO XI on Oracle DB. I suspect that this might be a bug of SAP BO.

  20. Hi Julian ,

    i want to show the user @Prompt for Date(mon-yyyy) , when selecting a date from list the below query should be executed :

    ——————————————–
    MyDate in (
    TO_CHAR(ADD_MONTHS(to_DATE(@Variable(‘date plz’),’Mon-YYYY’),-12),’Mon-YYYY’),
    TO_CHAR(to_DATE(@Variable(‘date plz’),’Mon-YYYY’),’Mon-YYYY’)
    )
    ——————————————–

    else if selecting none then , the below query executes:

    ——————————————–
    MyDate in (
    to_char(Add_Months(sysdate,-1),’Mon-YYYY’),
    TO_CHAR(ADD_MONTHS(Add_Months(sysdate,-1),-12),’Mon-YYYY’))
    ——————————————–

    how i can achieve that ??

  21. I have a report that includes 4 optional fields. I would like the user to populate at least 1 (any of the 4) when executing queries. Does anyone have any idea how I can enforce this?

  22. Hi LeAnn, I don’t think that Business Objects will be ale to enforce this in anyway. So even if you build in some SQL logic your users will have to comply or face an error of your choosing.

  23. How can we link 2 reports in SAP Business Objects 4.0.
    Exaample:I have summary report and detailed report. From Summary report if i click on one data element as Segment, i want to see detailed data for that perticular segment value by passing through a parameter value.

    thanks.

  24. Happy Easter Sari, the method of doing this is through the “OpenDocument” function. Keep in mind that you are not refreshing the data in both reports simultaneously, but rather you are refreshing the summary report first and then at the moment you click on the data element (such as a link called “order detail”) in the summary report, you are requesting the refresh of the linked detail report. This linked report can pass in parameters (and it should if it is calling a detail report) or it can have no parameters. Take a look at an article we published on this function titled OpenDocument – An Introduction to a Powerful Tool.

  25. Is there a limit as to the number of characters that can be displayed in a prompt or report description?

  26. Hi,
    This is the first time that I visit this site. I’m working on BO 4 and I have issues with key date variables that were created in BW, in fact there are four key date variables, but in the universe accept only one. I´m not sure if it could be possible seeting in the universe. Colud you help me, please?
    Best wishes,
    Maricela

  28. Hi Julian,

    Thanks for the article and comments.
    I have an optional prompt in a report based on an id. when we give in a value for the optional prompt it works fine. But, when i do not pass in a value, it gets back all the data for the specified time interval.
    Can you please help me to restrict the query values only when the optional prompt is entered otherwise i dont care even if the query does not run.
    FYI, this report has multiple queries.

    Regards,
    Ashwin M

  29. Hi

    I need some help on a prompt which I want to bypass – the prompt is for language and i just need something which will preven this prompt coming up as i need to schedule the report it is in and the prompt is preventing this . The current SQL code is as follows @Prompt(‘param_language(*)’,’A’,,mono,free,,{‘en’})),

    Any help greatfully appreciated

  30. Hi Simon, I am sure I am missing something, but when scheduling you should be able to set prompt values. You don’t need to bypass them in order to schedule. If you code is setup to handle “*” as a bypass then you will just need to set “*” (without the quotes) as the prompt when you are setting up the schedule. Now, please tell me what I am missing.

  31. HI Am trying to create a filter where by Networklogin = @Variable(‘BO_USER’).

    We have come across a scenario where apostrophe is causing an issue @Variable(‘BO_USer’)

    Eg: Networking long = Conor_E_O’Brien I could replace the ‘ with Replace syntax , but on the other side for the @Variable(‘BO_USer’) it doesn’t allow me to do the same @Variable(‘Conor_E_O’Brien’) and errors out.

    is there a way to replace the ‘ with Double quotes before it goes to database.

  32. Hi Tejaswini, I suggest that you try using two single-quote characters right after each other. Like this @Variable(‘Conor_E_O”Brien’). This works with Oracle, I can’t speak for SQL Server. You need only search for “escape character for single quote in SQL Server”.

  33. I have a task,

    I want o apply a filter which should use @prompt function.
    In this it should first ask for the report name(from a list of report names), then according to the report it shuld apply restrictions on some objects.
    Please help

  34. Hi,
    i have a problem. when i choose dimensions in a class for my query, i want another dimension to be a mandatory filter? for example, when i will look into the financial transactions of bank, and put “the amount” object in the result, i want the branch object be a mandatory filter.is this possible?

Leave a comment

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