cancel
Showing results for 
Search instead for 
Did you mean: 

Prompt in WebI causes Syntax error

Former Member
0 Kudos

Hi Colleagues,

we´re using BI Platform 4 and I have a report based on a Universe which is based on a HANA calculation view.

Whithin the report I use prompt query filters based on parameters and value lists which are defined in the data foundation. The report was running fine until I changed it.

I added two fields in the HANA view and refreshed the universe. Also I added one more value list and parameter in the universe which is also used as prompt input in the report. I did all the changes step by step, checking the refresh after every step and it worked. Once I saved the report, closed it and reopened I get the error :

Database error: [SAP AG][LIBODBCHDB DLL][HDB] Syntax error or access

violation;257 sql syntax error: incorrect syntax near "@": line 24 col

29 (at pos 647). (IES 10901)

SQL Script from Query Panel:

SELECT

  Table__1."BLD_SYSTEM_ID",

  Table__1."BLD_SYSTEM_ROLE",

  Table__1."BLD_BUSINESS_SYSTEM_TYPE",

  Table__1."SID_CLIENT",

  Table__1."CLIENT_ID",

  Table__1."TENANT_NUMBER",

  Table__1."CUSTOMER_ID",

  Table__1."CUSTOMER_NAME",

  Table__1."BUSINESS_TENANT_TYPE",

  Table__1."DESCRIPTION",

  Table__1."WORKCENTERVIEW",

  Table__1."TITLE",

  Table__1."DATE",

  Table__1."HOUR_SLOT",

  Table__1."MIN_SLOT",

  sum(Table__1."E2E_STEPS_COUNT"),

  sum(Table__1."E2E_FRONTEND_TIME"),

  sum(Table__1."E2E_NETWORK_TIME"),

  sum(Table__1."E2E_BACKEND_TIME")

FROM

  "_SYS_BIC"."dcsa/BYD_TENANT_WL_OVERVIEW"  Table__1

WHERE

  Table__1."DATE"  BETWEEN  @prompt('Evaluation Period Start','D',,Mono,Free,Not_Persistent,,User:0)  AND  @prompt('Evaluation Period End','D',,Mono,Free,Not_Persistent,,User:1)

GROUP BY

  Table__1."BLD_SYSTEM_ID",

  Table__1."BLD_SYSTEM_ROLE",

  Table__1."BLD_BUSINESS_SYSTEM_TYPE",

  Table__1."SID_CLIENT",

  Table__1."CLIENT_ID",

  Table__1."TENANT_NUMBER",

  Table__1."CUSTOMER_ID",

  Table__1."CUSTOMER_NAME",

  Table__1."BUSINESS_TENANT_TYPE",

  Table__1."DESCRIPTION",

  Table__1."WORKCENTERVIEW",

  Table__1."TITLE",

  Table__1."DATE",

  Table__1."HOUR_SLOT",

  Table__1."MIN_SLOT"

There are two more prompts which are not in the Script, these are optional. I noticed that, sometimes I get to the point of selecting values for the prompts, sometimes the error occurs before the selection screen comes up. Also, sometimes I can open and run the report 2 or 3 times and the error happens at the fourth.

Until now I tried the following:

1. I removed all prompts and added them one by one again, checking the refresh. Sometimes it works until I save the report and reopen it and the error occurs on reopen. Sometimes the error occurs already after adding the second prompt.

2. I rebuilt the universe and report from scratch using WebI prompts instead of the universe parameters. This seemed to work. But after saving and reopening - same error.

3. I checked the refresh of the data preview in the query panel as well as the same query built in the universe - this always works fine.

Any hint is very appreciated.

Thanks and best regards,

Katja

Accepted Solutions (0)

Answers (2)

Answers (2)

jyothirmayee_s
Active Contributor
0 Kudos

Hi,

I remeber on replying this question yesterday..

Please execute the view before using it for reporting. There might be a syntax issue with the above statement.

Thanks,

Jothi

Former Member
0 Kudos

Hi Jothi,

I did execute the view many times. Of course I cannot execute the above statement with the @prompt syntax in it, this is where business objects places the date from the user response.

I executed it within a query from the universe and it worked.

Thanks for your help.

Best regards,

Katja

jyothirmayee_s
Active Contributor
0 Kudos

Hi,

I see that there are multiple commas after "Prompt Type" (D,,).. can you try to remove the one of the comma and test please?.

@prompt('Evaluation Period Start','D',,Mono,Free,Not_Persistent,,User:0)  AND

@prompt('Evaluation Period End','D',,Mono,Free,Not_Persistent,,User:1)

 

Type the below parameters for @Prompt(1,2,3,4,5)

 

1 = 'Prompt Text Message'

2 = 'Prompt Type'  (i.e. A,N,D,U)  where A= Character, N= Number, D= Date, U= Unit.

3 = 'Class Name/ Object Name'

4 = 'Multi/ Mono'  ; Multi means Multiple ( Example = 2004,2005,2011)/ Mono means Single ( Example = 2012)

5 = Free/Constrain (Type value or select value in LOV/ Select Value)

  

Example for creating a @prompt filter is below

@Prompt('enter year','A','time period/year', Multi', Free)

Thanks,

Jothi

Former Member
0 Kudos

Hi Jothi,

I tried to remove the second comma in the custom script of the report panel. But on validation I get an API error and cannot save the changed script.

I guess the two commas need to be there because I tried a prompt without a value list, which should be ok. If the second comma is deleted, the next parameter is taken as the value list which does not work.

Thanks and best regards,

Katja

former_member188911
Active Contributor
0 Kudos

Hi Katja,

did you open a case with support?

it seems there is a problem with the @prompt statement

what patch level are you on (for both HANA and BI Platform) ?

thanks


SC

Former Member
0 Kudos

Hi Simone,

yes, I opened an incident, but did not yet get a reply.

Our HANA version is 1.00.50.373820, SP5

BI Platform has version 4.0 FP3 (14.03.613)

We did not change any installation recently, prompts are working in other reports.

Thanks for your help.

Best regards,

Katja