Multi line Input Paramters with Scripted View

Creating multi-value or multi entry input parameter in HANA Graphical calculation view is soothing and works perfectly, however doing same in Hana Scripted view doesn’t work correctly (Checked on HANA 01 SPS 12, not sure if SAP has fixed this in later versions).

Let’s see how to do this in Scripted View.

creating sample table ZSALES_TEST with Year, Country and Amount columns

CREATE COLUMN TABLE “MOSAHIL”.”ZSALES_TEST” (“YEAR” VARCHAR(4),
“COUNTRY” VARCHAR(25),
“AMOUNT” INTEGER CS_INT) UNLOAD PRIORITY 5 AUTO MERGE;

Inserting sample data to table:

insert into “MOSAHIL”.”ZSALES_TEST” values(‘2015′,’India’,5000);
insert into “MOSAHIL”.”ZSALES_TEST” values(‘2016′,’India’,8000);
insert into “MOSAHIL”.”ZSALES_TEST” values(‘2017′,’India’,15000);
insert into “MOSAHIL”.”ZSALES_TEST” values(‘2018′,’India’,25000);
insert into “MOSAHIL”.”ZSALES_TEST” values(‘2015′,’UAE’,5000);
insert into “MOSAHIL”.”ZSALES_TEST” values(‘2016′,’UAE’,15000);
insert into “MOSAHIL”.”ZSALES_TEST” values(‘2017′,’UAE’,35000);
insert into “MOSAHIL”.”ZSALES_TEST” values(‘2018′,’UAE’,55000);
insert into “MOSAHIL”.”ZSALES_TEST” values(‘2015′,’US’,15000);
insert into “MOSAHIL”.”ZSALES_TEST” values(‘2016′,’US’,105000);
insert into “MOSAHIL”.”ZSALES_TEST” values(‘2017′,’US’,20000);
insert into “MOSAHIL”.”ZSALES_TEST” values(‘2018′,’US’,515000);

table data:

YEAR	COUNTRY	AMOUNT
2015	India	5000
2016	India	8000
2017	India	15000
2018	India	25000
2015	UAE	5000
2016	UAE	15000
2017	UAE	35000
2018	UAE	55000
2015	US	15000
2016	US	105000
2017	US	20000
2018	US	515000

Create a new Scripted calculation view in HANA to fetch values from above table with multi value input parameter.

Below multi value input parameter(IP_YEAR) is created in a scripted view

declare two variables VAR_YEAR  to store input parameter value and COUNT_YEAR to store no. of items entered in input parameter.

Use below code:

/********* Begin Procedure Script ************/

BEGIN

DECLARE VAR_YEAR NVARCHAR (100);

DECLARE COUNT_YEAR INTEGER;

VAR_YEAR = :IP_YEAR;

–Input parameter returns string as ”’2017”,”2018”’ this should be replaced with 2017,2018

VAR_YEAR = REPLACE(REPLACE(VAR_YEAR,  ”’,”’, ‘,’),””,”);

–Counting no. of items entered, adding +3 just be sure

SELECT (LENGTH(VAR_YEAR)-LENGTH(REPLACE(VAR_YEAR, ‘,’,”))+3) INTO COUNT_YEAR FROM DUMMY;

–below code returns input paramter values in a table format

T_YEAR= SELECT “VAR_YEAR” AS YEAR, LENGTH(VAR_YEAR) as RANK FROM

(

SELECT

SUBSTR_REGEXPR(‘(?<=^|,)([^,]*)(?=,|$)’ IN :VAR_YEAR OCCURRENCE “SERIES”.”ELEMENT_NUMBER” GROUP 1) “VAR_YEAR”

FROM DUMMY,

SERIES_GENERATE_INTEGER(1, 1, :COUNT_YEAR) “SERIES”

)

WHERE VAR_YEAR IS NOT NULL;

var_out = SELECT “YEAR”, COUNTRY, AMOUNT FROM “MOSAHIL”.”ZSALES_TEST” where YEAR” IN (select YEAR from :T_YEAR)

ORDER BY “YEAR”;

END

/********* End Procedure Script ************/

Data Preview: (entered two lines for input parameter)

Output:

Cons:

1. You need to increase input parameter data type length in order to work, I have given 400 else it will throw string too long error.

2. If input parameter value is derived from a table column or view, then this will not work, because data type value for input parameter will also be derived from table column data type