XSODATA with Multi-Input Parameter and Graphical Calculation View

I have always liked the xsodata framework because of the out of the box feasibility it offers. But whenever I tried to use it for multi-select drop down options I always ran into issues. This blog post is meant to serve as an end-to-end example of how to accomplish this.

My requirements for using xsodata:

— Have filters that get passed down to the base query via input parameters in graphical views.

— Decrease the metadata output by xsodata

— Make sure that that the input parameter filter had a default ‘ALL’ value for the drop down

— Use the IN operator instead of MATCH in the filtering expression in order to improve performance

Some challenges that I faced:

1) As of now (March 2017) multi-input parameter in xsodata still gives an error as mentioned in this discussion (https://archive.sap.com/discussions/thread/3851602) .

But thanks to Maria Trinidad MARTINEZ GEA  reply (https://archive.sap.com/discussions/thread/3905752) I found out that deselecting the Multi-entries option in a calculation view made the error go away.

The only downside is that using Data Preview of calculation view becomes nearly impossible because you can’t replicate the single-quote input format as single-quotes are escaped. Part of the workaround for this issue is putting the column name of what you are filtering by as a default value. This completely negates the IN filter condition so the data preview can work. The SQL Script call to the Calculation View works the same way as multi-input parameters so that always remains an option.

2) I also wanted to get rid of the uri in the metadata because it sometimes doubled the response of GET request.  Some responses said it was impossible or required extra parsing (https://archive.sap.com/discussions/thread/3602942 ).  Luckily while exploring the various options of passing input parameters I stumbled upon the option of using an alternative to the keys generate local which was to generate the key via input parameters. Then as long as the column which was designated as the key was not selected the metadata was considerably shorter.

-Usefull site: (http://help-legacy.sap.com/saphelp_hanaplatform/helpdata/en/2e/c97095dcbd420794670912e3bc9cd6/content.htm?frameset=/en/aa/c50b77d6c64cbbaa979f70420336bd/frameset.htm&current_toc=/en/8f/796f93e6c3419c9b8029197aa61874/plain.htm&node_id=99)

And with that here’s a step by step example you can follow to try it out yourself.

Sample Table & Data
DROP TABLE SAMPLE_SALES;

CREATE COLUMN TABLE SAMPLE_SALES
(
COUNTRY_CODE VARCHAR(3),
CITY VARCHAR(40),
PRICE DECIMAL(26,8),
QUANTITY Integer
);

DELETE FROM SAMPLE_SALES;

INSERT INTO SAMPLE_SALES VALUES ( ‘US’, ‘LOS ANGELES’, 10.0, 3 );

INSERT INTO SAMPLE_SALES VALUES ( ‘US’, ‘CHICAGO’, 15.0, 3 );

INSERT INTO SAMPLE_SALES VALUES ( ‘US’, ‘NEW YORK’, 20.0, 4 );

INSERT INTO SAMPLE_SALES VALUES ( ‘CAN’, ‘TORONTO’, 10.0, 2 );

Graphical Calculation View

1. Use a Projection Node to feed into the Aggregation Node

2.  Create Input Parameters for each column and one more called key for later

3. Set Input Parameter Filter Condition

I tried using $$IN_CITY$$ = ‘ALL’ here at first but that did not work.

4. Create 2 calculated columns. A dummy key with a default integer value and a TOTAL_SALES column.

5. Make sure aggregation type is set to SUM

RUNNING CV Alone

You can run this the sql console as long as you are careful to put the exact number of single quotes. Data Preview only works if you use the defaults, because we didn’t select multiple entries and if you try to type single quotes they just get escaped with a back slash.

SELECT
“CITY”,
“COUNTRY_CODE”,
sum(“TOTAL_SALES”) AS “TOTAL_SALES”
FROM “_SYS_BIC”.”poc_exampes.XSODATA_MULTI_INPUT/SAMPLE_SALES_TOTAL” (‘PLACEHOLDER’ = (‘$$IN_COUNTRY_CODE$$’,
”’ALL”’),
‘PLACEHOLDER’ = (‘$$IN_CITY$$’,
”’NEW YORK”,”OTTAWA”’ ))
GROUP BY “CITY”,
“COUNTRY_CODE”
;
SELECT
“CITY”,
“COUNTRY_CODE”,
“PRICE”,
“QUANTITY”,
sum(“TOTAL_SALES”) AS “TOTAL_SALES”
FROM “_SYS_BIC”.”poc_exampes.XSODATA_MULTI_INPUT/SAMPLE_SALES_TOTAL”
GROUP BY “CITY”,
“COUNTRY_CODE”,
“PRICE”,
“QUANTITY”
;

SELECT
“CITY”,
“COUNTRY_CODE”,
“PRICE”,
“QUANTITY”,
sum(“TOTAL_SALES”) AS “TOTAL_SALES”
FROM “_SYS_BIC”.”poc_exampes.XSODATA_MULTI_INPUT/SAMPLE_SALES_TOTAL” (‘PLACEHOLDER’ = (‘$$IN_COUNTRY_CODE$$’,
‘”COUNTRY_CODE”‘),
‘PLACEHOLDER’ = (‘$$IN_CITY$$’,
‘”CITY”‘))
GROUP BY “CITY”,
“COUNTRY_CODE”,
“PRICE”,
“QUANTITY”

XSODATA 

Option 1  — With Parameter as Key

Definition:

service namespace “”  {

“poc_exampes.XSODATA_MULTI_INPUT::SAMPLE_SALES_TOTAL”

as “SALES” with (  “CITY”,
“COUNTRY_CODE”,
“PRICE”,
“QUANTITY”,
“KEY”,
“TOTAL_SALES”
)
keys (“KEY”)

aggregates  always (MAX of “TOTAL_SALES”)
parameters via key and entity “SalesInput” results property “Execute”
;

}
settings
{
support null;
}

Sample Call:

http://:/poc_exampes/XSODATA_MULTI_INPUT/SAMPLE_SALES.xsodata/SalesInput(IN_COUNTRY_CODE=”’ALL”’,IN_CITY=”’TORONTO”,”NEW%20YORK”’)/Execute?$format=json&$select=COUNTRY_CODE,TOTAL_SALES

* if you don’t select the key then the response has less metadata

http://<host>:<port>/poc_exampes/XSODATA_MULTI_INPUT/SAMPLE_SALES.xsodata/SalesInput(IN_COUNTRY_CODE=”’ALL”’,IN_CITY=”’TORONTO”,”NEW%20YORK”’)/Execute?$format=json&$select=COUNTRY_CODE,TOTAL_SALES

{"d":{"results":[{"__metadata": {"type":".SALESType"},"COUNTRY_CODE":"CAN","TOTAL_SALES":20},{"__metadata": {"type":".SALESType"},"COUNTRY_CODE":"US","TOTAL_SALES":80}]}}

Else, if you skip the $select  it has the long one

{"d":{"results":[{"__metadata": {"type":".SALESType","uri":"http://<host>:<port>/poc_exampes/XSODATA_MULTI_INPUT/SAMPLE_SALES.xsodata/SALES(IN_COUNTRY_CODE='''ALL''',IN_CITY='''TORONTO''%2C''NEW%20YORK''',KEY=1m)"},"IN_COUNTRY_CODE":"'ALL'","IN_CITY":"'TORONTO','NEW YORK'","CITY":"TORONTO","COUNTRY_CODE":"CAN","PRICE":"10","QUANTITY":2,"KEY":"1","TOTAL_SALES":20},{"__metadata": {"type":".SALESType","uri":"http://<host>:<port/poc_exampes/XSODATA_MULTI_INPUT/SAMPLE_SALES.xsodata/SALES(IN_COUNTRY_CODE='''ALL''',IN_CITY='''TORONTO''%2C''NEW%20YORK''',KEY=1m)"},"IN_COUNTRY_CODE":"'ALL'","IN_CITY":"'TORONTO','NEW YORK'","CITY":"NEW YORK","COUNTRY_CODE":"US","PRICE":"20","QUANTITY":4,"KEY":"1","TOTAL_SALES":80}]}}

Option 2 — With Generated Local Key

service namespace “”  {

“poc_exampes.XSODATA_MULTI_INPUT::SAMPLE_SALES_TOTAL”

as “SALES” with (  “CITY”,
“COUNTRY_CODE”,
“PRICE”,
“QUANTITY”,
“KEY”,
“TOTAL_SALES”
)
keys generate local “localID”

aggregates  always (MAX of “TOTAL_SALES”)
parameters via entity “SalesInput” results property “Execute”
;

}
settings
{
support null;
}

Sample Call

<host>:<port>/poc_exampes/XSODATA_MULTI_INPUT/SAMPLE_SALES.xsodata/SalesInput(IN_COUNTRY_CODE=”’ALL”’,IN_CITY=”’TORONTO”,”NEW%20YORK”’)/Execute?$format=json

{"d":{"results":[{"__metadata": {"type":".SALESType","uri":"http://<host>:<port>/poc_exampes/XSODATA_MULTI_INPUT/SAMPLE_SALES.xsodata/SALES('14797937296582431')"},"localID":"14797937296582431","CITY":"NEW YORK","COUNTRY_CODE":"US","PRICE":"20","QUANTITY":4,"KEY":"1","TOTAL_SALES":80},{"__metadata": {"type":".SALESType","uri":"http://<host>:<port>/poc_exampes/XSODATA_MULTI_INPUT/SAMPLE_SALES.xsodata/SALES('14797937296582432')"},"localID":"14797937296582432","CITY":"TORONTO","COUNTRY_CODE":"CAN","PRICE":"10","QUANTITY":2,"KEY":"1","TOTAL_SALES":20}]}}