Skip to Content
0

Using @prompt in a free hand sql report

Sep 22, 2017 at 01:47 PM

427

avatar image
Former Member

I have a freehand sql report, that I have with oracle code. For simplicity and privacy, lets say the sql is the following:

select t.id, t.name, t.qty from test_table t  where t.id = '1234'

However, I want to pass a parameter to this free hand sql report using a prompt so that a value is passed from one report to this free hand sql report via the url. I have done that in the past by creating a prompt in the report. However, seeing that this report is a free hand sql report the interface that is used to add a prompt when the data provider is a universe does not exist for free hand sql report. After some research I attempted to build in the @prompt in the sql itself so I end up with:

select t.id, t.name, t.qty from test_table t  where t.id = @prompt('Enter ID', t.id)

This threw the following error "An error has occurred from the server. Incorrect prompt definition". I tried several variations of the prompt syntax, but they all gave the same error.

My question is can I build a prompt in a free hand sql report so that I can pass parameters to the report as I can with a report using a Universe? If so, what is the syntax that goes within the sql code itself. Thanks in advance.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
AMIT KUMAR
Sep 22, 2017 at 02:06 PM
0

try this.

= @Prompt('Enter ID','A',,Mono,Free,Persistent,,User:0)

Show 5 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Amit is my new best friend. This works well. I thought I tried that before though but I must have done something wrong. One final question (I hope :-) ) is there a way to make the prompt optional? Or to default to All values? Thanks

1

use this for optional prompt.

= @Prompt('Enter ID','A',,Mono,Free,Persistent,,User:0,Optional)

0
Former Member

Thanks again for your reply. I am getting an error with the optional keyword saying that the prompt definition is invalid.

0

sorry i forgot..optional prompt is not supported with free hand sql.

You can check sap note.

2433209 - Optional prompt using Free Hand SQL

Workaround:

  • In order to create a prompt that functions like an optional prompt, create a clause that has the ability to evaluate True based on a valid parameter value or a submitted keyword that has been configured for the prompt. Therefore create an OR clause.
  • For example: @prompt('Enter values for Year:(* to bypass the filter)','A',,Multi,Free,Persistent,,user:0,,'currentDate() - 7') OR '*' = @Variable('Enter values for Year:(* to bypass filter)')
  • If a valid value for Year is provided, then optional prompt clause will return those values for that year. If a “*” character (without the quote characters) is provided, the prompt clause will evaluate “TRUE” for all records as it has been bypassed
0
Former Member

Thanks so much for all your help.

1