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 “
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