Passing multiple values to input parameter from IDT to HANA

[upamanyu_mukherjee]

Hi Guys,

I am new to Business Objects , I am trying to pass input parameters from Webi to HANA.

I have created the input parameter in IDT(4.2 SP2) and have also created a derived table in data foundation.

I have selected the "Allow multiple values" check box in data foundation , however , once I select this check box I get the below error while validating the derived table query in data foundation.

[SAP AG][LIBODBCHDB32 DLL][HDBODBC32] Syntax error or access violation;257 sql syntax error: incorrect syntax near "(": line 2 col 70 (at pos 216)

The same query seems to work if I un-check the "Allow multiple values" check box.

I am using the query syntax -

select a,b,sum(c) c from view

('PLACEHOLDER'=('$$IP_PLANT$$',@Prompt(Plant)))

group by a,b

Am I missing something? What would be the correct way of allowing users to pass multiple values for a prompt and consume the same in Webi and then pass it down to HANA thru IDT.

Any insight would be much appreciated.

Best Regards,

Upamanyu Mukherjee

[rindia]

Hi Upamanyu,

The sample syntax:

SELECT A,B, SUM(C)

FROM

"_SYS_BIC"."ABC/CV_NAME"

('PLACEHOLDER' = ('$$IP_PLANT$$', '@Prompt('Select Plants','K',

first

solution

',multi,constrained,persistent,{'*'

})'))

GROUP BY

A,B;

The derived table contains a prompt based on the list of values named “first solution”.

The prompt data type is “K”:

it means that the values selected by the user are not enclosed by single quotes.

The prompt allow the user to select multiple values but only in the list (parameter constrained).

This is a restriction of “K” data type to avoid SQL injection.

A default value (*) is added in the prompt definition:

this allows the validation of the derived table because

prompt needs to be solved beforehand.

The prompt expression is enclosed by single Quotes to ensure that HANA will

validate the derived table SQL expression.

Regards

Raj Kumar Salla

[rindia]

Also I recommend to go through this document:

This will really helps you.

[upamanyu_mukherjee]

Hi Raj,

I tried using the syntax mentioned by you and this time the derived table definition gets validated and I can preview its data by passing a single value.But when I try to pass multiple values from the LOV I get the below error:

[SAP AG][LIBODBCHDB32 DLL][HDBODBC32] General error;2048 column store error: search table error:  [34023] Instantiation of calculation model failed;exception 306002: An internal error occurred

I am using HANA SP12

Any idea?

Regards,

Upamanyu Mukherjee

[rindia]

Hi Upamanyu,

In Calculation view, Is the option Multiple Entries checked in Input parameter?

Please paste your Input parameter screenshot.

Regards

Raj Kumar Salla

[upamanyu_mukherjee]

Hi Raj,

I have attached the screenshots below:

1)HANA Input Parameter Definition:

2) IDT Parameter Definition:

3) Derived Table Query:

SELECT "_SYS_BIC"."Pkg.Views/CV_STO"."R_REGIO","_SYS_BIC"."Pkg.Views/CV_STO"."WERKS", SUM("_SYS_BIC"."Pkg.Views/CV_STO"."MENGE")

FROM

"_SYS_BIC"."Pkg.Views/CV_STO"

('PLACEHOLDER' = ('$$IP_PLANT$$', '@Prompt('Plant','K',Plant_LOV,multi,constrained,persistent,{'*'})'))

GROUP BY

"_SYS_BIC"."Pkg.Views/CV_STO"."R_REGIO","_SYS_BIC"."Pkg.Views/CV_STO"."WERKS"

Here Plant is my prompt message and Plant_LOV is the LOV that I have associated with thid prompt.

This seems to work as long as I pass a single value , but throws the above mentioned error when I pass multiple values.

4)I checked the log and the query that gets generated is:

Query failed.

SELECT

  Table__2."R_REGIO",

  Table__2."WERKS",

  Table__2."SUM(MENGE)"

FROM

  (

  SELECT "_SYS_BIC"."Pkg.Views/CV_STO"."R_REGIO","_SYS_BIC"."Pkg.Views/CV_STO"."WERKS", SUM("_SYS_BIC"."Pkg.Views/CV_STO"."MENGE")

FROM

"_SYS_BIC"."Pkg.Views/CV_STO"

('PLACEHOLDER' = ('$$IP_PLANT$$', '(1000,1100)'))  ***from show log

GROUP BY

"_SYS_BIC"."Pkg.Views/CV_STO"."R_REGIO","_SYS_BIC"."Pkg.Views/CV_STO"."WERKS"

  )  Table__2

Note: As per modeler guide HANA is expecting ('PLACEHOLDER' = ('$$IP_PLANT$$', '''1000'',''1100''')) , so it seems that the syntax is not matching.

Let me know if I am doing something wrong.

Warm Regards,

Upamanyu

[rindia]

Hi Upamanyu,

Could you please check the single quote around LOV.

Also please refer to attached link of previous reply- page 9.

[upamanyu_mukherjee]

Hi Raj,

I have tried enclosing the LOV in single quotes('LOV') , but I keep getting the same error-

FROM

"_SYS_BIC"."Pkg.Views/CV_STO"

('PLACEHOLDER' = ('$$IP_PLANT$$', '@Prompt('Plant','K','Plant_LOV',multi,constrained,persistent,{'*'})'))

The below query(LOV') gives syntax error-

FROM

"_SYS_BIC"."Pkg.Views/CV_STO"

('PLACEHOLDER' = ('$$IP_PLANT$$', '@Prompt('Plant','K',Plant_LOV',multi,constrained,persistent,{'*'})'))

As of now it seems directly creating Webi report on top of HANA is a solution , as there I can successfully pass multiple values.

But am really keen on finding out a way to make this work in IDT.

Regards,

Upamanyu