Skip to Content

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   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}]}}

 

13 Comments
You must be Logged on to comment or reply to a post.
  •  Putting the default value as the column name that is used in the IN condition unfortunately excluded nulls from the result if you skipped the input parameter. Putting the ”’ALL”’ work as a default input parameter but the language has to be SQL not Column Engine. For this option I had to go back to Studio because it wasn’t available in the Web Workbench.

    • Hi Denica Baeva

      Thanks for the blog, it helped me a lot in times of trouble.

      BTW, you have kept “ALL” in expression editor. For which column, you have mentioned like this ?  I assume this is for multiple values, but is it still work for “Is Mandatory” – checked or unchecked ?

       

      Thanks

      HANA Developer

  • Good blog Denica. Thanks for sharing it.

     

    By the way, am right now pondering over an security issue with the construct of the “sample call” you shared – where the filter values are passed openly (unencrypted)! what if the user is was allowed access to TORONTO and then they debug and chage it to LOS ANGELES from the UI code.. is there a simple way to prevent this kind of data tampering?

     

    Regards

    Sudarshan

    • This is kind of late but if the users have two separate HANA Accounts the quick and dirty way to do it is have a function that returns select current_user from dummy. Have a table that stores the HANA user to city mapping. Join the two to get the current user’s regions and then use those regions as part of an inner join filter in your graphical CV. So then even if they enter LOS ANGELES they wouldn’t get any data.

  • Hi Denica,

    Great Blog and thanks for the Input.

    What ist the reason you use Input parameters for simple filtering problems?
    You state that one of your reason is that you want to ‘ Have filters that get passed down to the base query via input parameters in graphical views.’

    I face a smiliar problem now and tried to find out if the normal odata Filter statement even has this problem.

    Ongoing from this Blogpost https://archive.sap.com/discussions/thread/3773813 i anaylzed the sql statement that was generated by odata by adding “&profile=html” to my odata query.
    I got the generated sql statement and anlyzed it with the PlanViz Tool in Eclipse and saw that the SQL Engine pushes down the Filters to the Tables on it’s own. I even use nested Calculation View and still the where statement is pushed down to the bottom of the execution plan.

    Are my queries not complex enough or are there special queries where the Input Parameter makes a difference for Filtering?

    Greetings

    Oliver Schmidt

  • Hi Denica,

    I need your guidelines on this approach

    I followed the exact steps mentioned in this blog.  But when trying to display the data through calculation view I’m getting either 0 records (For Option 2 & 3 of “Running CV Alone”) or an error message (for Option 1 of “Running CV Alone”).  I’m currently using HANA 1.0 SPS12

    Also could you please help me in understand the point 3 on the prerequisites for Odata services.

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

    Thanks in advance.

     

    Regards

    Ganapathi

  • This was a very interesting blog and I sincerely want to thank for your efforts.

    I have couple of questions.

    1. Is it better to put $filters in the URI or should we have Input parameters for the better performance of Odata Service and Calculation View? In my project, the service could be looking for billions and billions of records, but since i dont have access to large amount of data, i am unable to do the comparison.
    2. I am fetching data from multiple sources and I have calculation views for some of these sources. If i try to consume these already existing Calculation Views from a parent calculation view, Input Parameters on the child view doesn’t work..Is there anything i might be missing out if i want to pull data from child view with input parameters via parent view using xsodata service?

     

    • Hi Rohit,

      1. Having input parameter to the service help performance for huge volume than filtering at the end point.
      2. Check if you have managed mapping between parent and child views. Also make sure the input parameter in child view is filtered against the column in a expression for graphical view. If scripted view, then the input parameter can be passed to where condition like :input_parameter.

      Thanks

      Siva