“Empty” value in optional prompt in Bex query

Posted on August 21, 2012 by . Filed under BW / Bex / BICS, Connection, Excel, Xcelsius | 6 Comments

I finally found the solution for the issue with the optional prompts and the empty value string when connecting through BICS to a Bex query.

Unfortunately BW does not view the Excel formula =”" as an empty string as thus your query returns no data. Formerly with the Live Office connections you needed to pass “” as the empty value string. This does not work either.

I logged this error at SAP and they told me to create the optional prompts in the Bex query itself and then use the prompt selector. However I do not want to use this selector as I use the interactivity from the components (charts) itself to fill in the prompt. This huge box I do not want to use at all. If I wanted to bypass the prompt selector component then I needed to create 2 BW queries, one with a prompt and one without one. Come on, not really an option either!

After some trial and error I found the solution. If you want to use a formula to get the prompt values you could instead of the =”" a formula like =9/0. This returns a #DIV/0 error and when you have the general Excel setting to suppress errors like #DIV/0 then it will turn into an empty cell value and the prompt value is ignored by BW.

Note: The general Excel setting can be found here:
File – Preferences – Excel options – Ignore Excel Formula Errors

Less Info ↑
Femke Kooij
Femke's website
Follow Femke on LinkedIn
Femke Kooij is a dashboard and report designer, developer and educator specializing in SAP | BusinessObjects Dashboards (better known as Xcelsius) and Crystal Reports. She has also a lot of experience with other SAP | BusinessObjects toolings like, Web Intelligence (WebI), Information Design Tool (Universe Designer), BusinessObjects Data Services (BODS) and the overall server platform BusinessObjects Enterprise (Launchpad, CMC). She spends a lot of her time thinking about and sharing her knowledge of Xcelsius to others through her own blog. Lately she is exploring other visualization tools like Qlikview and Tableau. On the later she is also posting some experiences. Currently she is in the employment of Cognizant Technology Solutions. Her blog only contains her personal views, thoughts and opinions. It is not endorsed by Cognizant Technology Solutions nor does it constitute any official communication of Cognizant Technology Solutions.

This entry was posted in BW / Bex / BICS, Connection, Excel, Xcelsius and tagged , , , , , . Bookmark the permalink.

6 Comments to “Empty” value in optional prompt in Bex query

  1. zahid says:
    July 4, 2013 at 13:25

    I usually use EMPTY cell to pass ALL values to Bex. This one might be useful also for some other cases.

    • manjesh says:
      August 20, 2013 at 17:21

      Hi Zahid,
      Can you please explain how you passed empty cell for all value? I am getting error while sending empty cell. I am writing – IF(A2=”All”,”",A2) but it passes 0 value .
      Thanks

  2. Carlos Barron says:
    June 26, 2013 at 20:06

    try to use or ‘

  3. Delphine Pourrain says:
    October 4, 2012 at 16:15

    I tried your solution with a ” IN LIST” but it doesn’t work with an optional prompt.
    I put 2 prompts :
    Eg: col1 IN LIST ( Prompt1 ) OR col1 LIKE (Prompt2)
    When I want ALL : Prompt1 = ” and Prompt2 = ‘%’
    When I want Nothing : Prompt1 = ” and Prompt2 = ”
    When I want a list : Prompt1 = (‘A’, ‘B’) and Prompt2 = ”

    But if you have another solution for optional prompt with a List….. I will take it.

    Thanks.

    • Kalyan Verma says:
      July 25, 2013 at 05:30

      Delphine,

      Did you figure out how to pass values to an INLIST prompt? I tried to separate the values using ; and , but neither worked. Any ideas here?

      • Femke Kooij says:
        July 26, 2013 at 17:20

        Hi when using an inlist prompt with Bex queries (via BICS) you can’t concatenate the values. If you link the selected values from your inlist prompt to multiple cells the webservices automatically passes this as seperate OR statements. Just note that you have the fill in the blanks (or the 9/0 option) for cells that do not have values if you have them filled with formulas.