How Can I Find Detailed Instructions on Setting up Prompts (@prompt())on a report using FreeHandSQL?

[carla_rogers]

This post contains three closely-related questions.

QUESTION 1:  Can you possibly make any suggestions on where I might be able to find relatively complete information on use of @prompt() with freehandsql to make and manage prompts in WEBI reports running on SAP Business Objects Platform?

Background:  Not long ago, I was informed that adding and controlling prompts on reports using Freehandsql in Business Objects WEBI requires  manually coding in the SQL with "@prompt()" function calls.

I tried searching community.sap.com for detailed specifications and instructions for "@prompt()" function calls, but unfortunately, I did not find comprehensive information.

Question 2:  Is question 1 written making correct use of the relevant terminology?  For example, is it correct to write:  "adding and controlling prompts on reports using Freehandsql in Business Objects WEBI requires  manually coding in the SQL with "@prompt()" function calls."

Question 3:  How could I quickly find the exact information I am looking for without imposiing on anyone's time with questions in this forum?

Any information you can provide will be appreciated immensely.

Thank you.

In the webi editor, tjhis is where we plug parameters in, where we link to thje stored procedure that will produce the dataset for the webi

Here is what our SPs look like:

[nscheaffer]

I answered this question. Then I edited my answer to format some code. And now my answer is gone. No draft, nothing.

[nscheaffer]

I posted the second version of my answer. Maybe this one is better. 😉

[nscheaffer]

And now my first answer is back. Strange.

[nscheaffer]

We do not use WebI in this way so I am not sure. The only thing I can think of to have you try is to remove the double-quotes from around your @Prompt() function.

[nscheaffer]

Answer 1

I understand you are working in Web Intelligence, but WebI Prompt documentation is more about how to interact with the Prompt Properties dialog. I think the Information Design Tool Prompt documentation is more comprehensive in laying out all the options. Be sure select the version to match the version you are using.

When creating a prompt in a free-hand SQL query, I usually create one in a universe based query on an object of the same data type.

Then adjust the settings in Prompt Properties dialog (restrictions highlighted)...

With the following restrictions...

• You cannot "Prompt with List of Values" in a free-hand SQL query.
• You cannot have an "Optional prompt" in a free-hand SQL query specified within the Prompt() function. There is a workaround I will mention later.

Then click on the "View query script" icon (last one to the right) and copy the entire @Prompt() function to paste into your free-hand SQL query.

As I mentioned above, you cannot designate a prompt as optional in a free-hand SQL query but you can achieve the same effect with a workaround.

I picked up this technique from Alan Mayer a while back. Basically, you put your @Prompt() in pairs of OR statements so that when you pick "ALL" that eliminates the comparison of the corresponding database column since 'ALL' = 'ALL' will always be true. The last parameter User:X sets the parameter order.

WHERE
(CITY = @Prompt('City', 'A', {'ALL'}, Mono, Free,Not_Persistent,{'ALL'}, User:0) OR
'ALL' = @Prompt('City', 'A', {'ALL'}, Mono, Free,Not_Persistent,{'ALL'}, User:0))
AND
(DATE = @Prompt('Date', 'D', {'ALL'}, Mono, Free,Not_Persistent,{'ALL'}, User:1) OR
'ALL' = @Prompt('Date', 'D', {'ALL'}, Mono, Free,Not_Persistent,{'ALL'}, User:1))
AND
(STORE = @Prompt('Store', 'A', {'ALL'}, Mono, Free,Not_Persistent,{'ALL'}, User:2)
OR 'ALL' = @Prompt('Store', 'A', {'ALL'}, Mono, Free,Not_Persistent,{'ALL'}, User:2))

Answer 2

In my opinion, your wording is fine. I believe I understand what you are asking.

Answer 3

You could look for answers in the SAP Analytics section of the Help Portal. There are also some tutorials here. However, at times can it be difficult to know what to search for. In that case, that is the purpose this forum. We can help you clarify your question. I have found ample and able assistance here over the years and I am glad to give back. It keeps me sharp and allows me to support BOBJ users more effectively within my organization.

I hope this gets you on the right track.

Noel

[carla_rogers]

Noel, it will take me a few minutes to try working through the instructions and comments you provided. I did not want to wait until then to tell you, your response is astonishingly excellent. You seem to have covered all the details needed, with exceptional awareness and focus on the perspective I happen to have. This response demonstrates a special gift for writing support information. I will let you know how it goes when I try applying what you have written.

[carla_rogers]

I have no idea what I did to produce the pink box of extraneous letters.

[carla_rogers]

I posted a notice of the problems with the forum post editor at this link: https://community.sap.com/t5/questions-about-sap-websites/software-in-this-forum-needs-to-be-easier-...

[carla_rogers]

I have added to original question. Next step if for me to apply what you suggested