on 06-10-2013 4:20 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.