Skip to Content
avatar image
Former Member

Custom query not accepting @prompt as keyword

I want to modify report dynamic query generated by Webi Report. I want same query with some modification so I used checkbox of custom query and added modified query. In original query generated by report have datetime as input prompt. query generated have below prompt

DT_INTRADAY_POSITIONS_TIME.DATETIME_FORMAT IN @Prompt('Enter Start Date','D',,Mono,Free,Persistent,,User:0,Optional) AND @Prompt('Enter End Date','D',,Mono,Free,Persistent,,User:1,Optional)

While validating this custom query it gives error as

"Custom SQL cannot contain optional prompts (IES 10812)".

How to resolve this issue.

Thanks in advance..

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Dec 16, 2016 at 02:13 PM

    Hi Vipin,

    Try creating a pre-defined Business Filter, its supported starting BO 4.0 some SP. Checkout the below blog.

    https://blogs.sap.com/2013/05/24/optional-prompt-in-bo40/

    Thanks,

    Mahboob Mohammed

    ,

    Hi Vipin,

    There are few mistakes in the prompt, please use BETWEEN instead of IN, that way you can specify as below:

    PositionDate between Start Date and End Date

    And, I don't know if Optional prompt creation is supported yet, when you create a prompt using the @prompt syntax, you'll have to create pre-defined Business Filter. There are 2 types of pre-defined filters now, Native and Business filter. In Native, you write the where clause just like what you have here, and in Business you'll have to use the GUI, as we do currently, when we create a prompt in Query Panel Webi, and in that you've the option to make it an Optional prompt. I just tried creating it and it works, I have BO 4.1 SP5.

    Thanks,
    Mahboob Mohammed

    Add comment
    10|10000 characters needed characters exceeded