Question Re: Multiple Input Parameters in Calculation Views - Blank Parameters in Technology Q&A

Multiple Input Parameters in Calculation Views - Blank Parameters

b_dunk — Mon, 14 Mar 2016 22:07:59 GMT

Having read the following blog: http://scn.sap.com/community/hana-in-memory/blog/2013/12/30/using-multiple-values-in-input-parameter-for-filtering-in-graphical-calculation-view it's not clear to me the suggested solution works in all cases.

I'm currently working on SPS13 and I'm able to reproduce the exact behavior "Mic xyz" suggests in the comments of the same blog, "Creating a multiple parameter with multiple entries is working fine EXCEPT when the parameter is left blank." The error message when leaving the value blank is as follows:

I've tried the in() and match() logic suggested in the blog but neither the multiple parameter or blank will execute. In both scenarios the match() logic throws a type mismatch error.

Any advice on how this should work? As "Mic xyz" suggested the expectation is by leaving the parameter blank should resolve to a "select all" as done in ECC.

Re: Multiple Input Parameters in Calculation Views - Blank Parameters

SergioG_TX — Tue, 15 Mar 2016 01:21:02 GMT

Brian,

SP13 isn't out yet.. I am not sure what you meant by SP13... the latest HANA release is SP11... anyways, here is a blog where I used something similar... I had multiple input variables and each variable accepted multiple values... keep in mind that each value must be comma separated so that the in clause can work

please look at the screenshot of the stored proc where I am calling a view and passing those values to the input parameters, hope this helps you

Re: Multiple Input Parameters in Calculation Views - Blank Parameters

b_dunk — Tue, 15 Mar 2016 13:51:59 GMT

Sergio,

Appreciate your response. My mistake regarding SP level. I'm currently using SP10 not the non-existent "13" I mentioned in my post!

In the Graphical Calculation View, I've had success passing Input Parameters with multiple values. The issue I'm seeing is when a non-mandatory Input Parameter is left blank. The assumption is leaving a non-mandatory input value will not impact a filter expression, in other words, a "select all" would be the outcome for that specific param. This is not the case and thus the discussion topic. Is this the correct behavior? Here are more details:

Re: Multiple Input Parameters in Calculation Views - Blank Parameters

SergioG_TX — Tue, 15 Mar 2016 15:07:11 GMT

Brian,

if you only have one input parameter which if not provided, then you should skip, then I would suggest to take it out of the view and perform an if statement and only when the value is provided, filter out the output of the view. There will be a small performance impact due to the introduction of an if statement will not allow you to do the parallel execution, however, this is one way to get it accomplished.

alternatively, you may move the if statement into a separate stored proc call and hope you get better performance so that the if statement is isolated from your main stored procedure

Re: Multiple Input Parameters in Calculation Views - Blank Parameters

b_dunk — Tue, 15 Mar 2016 18:32:38 GMT

In the real business case, there are multiple input parameters some are mandatory some are not. I understand there are multiple ways to work around but seems unnecessary. The design of this functionality seems off. Appreciate your comments!

Re: Multiple Input Parameters in Calculation Views - Blank Parameters

SergioG_TX — Tue, 15 Mar 2016 19:03:59 GMT

I completely understand.... as a matter of fact I had that requirement of having a bunch of optional input parameters which made the processing of the proc slower... I ended up having to pass those input parameters to an XSJS service and then from there, I would read from a table and apply only the params that were indeed provided.

in my approach I followed this blog - I know this is a separate technology but for my scenario it worked perfectly and the performance was much faster than to doing this from a stored procedure