cancel
Showing results for 
Search instead for 
Did you mean: 

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

carla_rogers
Explorer
0 Kudos

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

 

carla_rogers_1-1709121565933.png

Here is what our SPs look like:

carla_rogers_2-1709121691946.png

 

 

 

nscheaffer
Active Contributor
0 Kudos
I answered this question. Then I edited my answer to format some code. And now my answer is gone. No draft, nothing.
nscheaffer
Active Contributor
0 Kudos
I posted the second version of my answer. Maybe this one is better. 😉
nscheaffer
Active Contributor
0 Kudos
And now my first answer is back. Strange.
nscheaffer
Active Contributor
0 Kudos
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.

Accepted Solutions (0)

Answers (2)

Answers (2)

nscheaffer
Active Contributor

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.

nscheaffer_0-1708962036534.png

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.

nscheaffer_1-1708962150877.png

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

nscheaffer_5-1708962935340.png

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.

nscheaffer_6-1708963044953.png

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
Explorer
0 Kudos
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
Explorer
0 Kudos
I have no idea what I did to produce the pink box of extraneous letters.
carla_rogers
Explorer
0 Kudos
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
Explorer
0 Kudos
I have added to original question. Next step if for me to apply what you suggested
nscheaffer
Active Contributor
0 Kudos

Answer 1

I understand that you are working in Web Intelligence, but the WebI Prompt documentation is more about how to interact with Prompt Properties dialog. I think the Information Design Tool Prompt documentation provides a much more comprehensive description of all the options.

What I usually do is create prompt in a universe based query on an object of the same data type.

nscheaffer_0-1708957050690.png

Then I go into the Prompt Properties dialog adjust the options to what I want. Note that you cannot "Prompt with List of Values" in a free-hand SQL query. Therefore, you cannot specify "Select only from list" either. Neither can you have an "Optional prompt" in a free-hand SQL query specified with the Prompt () function. More on that below.

nscheaffer_2-1708958393257.png

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

nscheaffer_3-1708958653364.png

There is a workaround to simulate a optional parameter.  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 just fine.

Answer 3

The best place to find the information you need depends on the topic. The Help Portal for Analytics is probably a good place to start. Note that once you click into a particular product, you can choose your version. There are also a number of tutorials and links to other SAP training and learning resources here.

Sometimes the challenge is not knowing what term or phrase to search. So do not feel you are inconveniencing anyone by posting a question. In my mind, that is the purpose of this forum. Interaction here is how I have learned a lot over the years. I find that answering questions like this keeps me sharp and able to explain these concepts more clearly and effectively to users within my own company. If there is anyone that would be inconvenienced by your question probably wouldn't be reading it anyway.

Hope this gets you on the right track.

Noel