BusinessObjects Board

Prompt in free-hand SQL

building reports desktopintelligence
#1

Hi,
I have a report with multiple data providers. Three of them are a free hand SQL. I have a working prompts in all of the data providers and I have to have the same prompts in free hand SQLs.
I pasted Prompt from DP SQL to the free_hand SQL and it did not work
In DP I have DDATE =trunc(to_date(@Prompt(‘Enter ending date as YYYYMMDD:’,‘A’,‘DMDate\Report Date in DM’,),‘YYYYMMDD’)) )
I changed it to DDATE = trunc(to_date(@Prompt(‘1)Enter ending date as YYYYMMDD:’,‘A’,‘DDate\Report Date in
DMW’,mono,free),‘YYYYMMDD’)) and it did not work either.
I am getting error “syntax of @prompt, @variable or @script is incorrect”
How do I need to change the Prompt to make it work?
Thank you for the help in advance

darina (BOB member since 2002-12-03)

#2

Did you try a search? I searched on ‘prompt free hand’ and found this.

Cindy Clayton :us: (BOB member since 2002-06-11)

#3

Cindy, thank you for responding.
I am using search always. I read everything about this topic, and did not find what I need. I am not using LOV, My users need to type the date.
If you know the syntax, please send it to me
Thanks

darina (BOB member since 2002-12-03)

#4

Here is one of ours…

a.cnc_dte = @Variable('Enter Cancellation Date (MM/DD/YYYY):')

Cindy Clayton :us: (BOB member since 2002-06-11)

#5

I have to use
trunc(to_date(@Prompt(‘Enter ending date as YYYYMMDD:’,‘A’,‘DMDate\Report Date in DM’,),‘YYYYMMDD’)) )

Could you, please correct mine
Thanks

darina (BOB member since 2002-12-03)

#6 
trunc(to_date(@Variable('Enter ending date as YYYYMMDD:'),'YYYYMMDD'))

You can’t use @Prompt in Freehand SQL to reference a universe LOV. By definition Freehand SQL and universe-based are mutually exclusive.

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

#7

Dwayne,
I don’t need to use LOV, users want to type the date.
Can I use @Prompt in this case?
Thanks

darina (BOB member since 2002-12-03)

#8

If you’re not able to use an LOV, or any of the other benefits of @Prompt, I’m not sure why you’d want to use it, but yes it is possible. The following are equivalent.

trunc(to_date(@Variable('Enter ending date as YYYYMMDD:'),'YYYYMMDD'))
trunc(to_date(@Prompt('Enter ending date as YYYYMMDD:',,,,),'YYYYMMDD'))

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

#9

Thank you very much for your help. It worked using @variable

darina (BOB member since 2002-12-03)