HANA Input parameter issue - Need help

[raghavendra_p]

I got struck with HANA input parameters issue, Any ideas/Suggestions are greatly appreciated.

Issue :

---------

I have a basic calculation view of data category as cube. It has projection, aggregation (default) and semantics. In projection, I am restricting table VBAK, need five fields Doc num VBELN, creation date ERDAT, division SPART, sales org VKORG and NETWR.

Want to create four input parameters for four fields VBELN, ERDAT, SPART and VKORG. User should be able to enter multiple entries for VBELN/VKORG/SPART and date range for ERDAT.

Note :

1,  When input parameter left as blank, system should fetch all values and i need all four parameters (Incase of one parameter, i know, it works with match function).

2. If user enters two parameters and left two as blank, data should fetch as per parameters entered considering blank parameters as 'ALL VALUES' (Just like BEx variables)

Any inputs/suggestions from your end is greatly appreciated.

[rindia]

Hi Raghavendra,

Create Variables instead of Input parameters for VBELN, ERDAT, SPART and VKORG.

This will fulfill your purpose.

Regards

Raj Kumar Salla

[raghavendra_p]

Hello Raj,

Thank you for your response.

I actually want to filter data right at the time of fetching from data base rather filtering at result set before displaying in report, thats why i am choosing input parameters .

[rindia]

Hi Raghavendra,

The filter is applied right at the time of fetching data from base table.

How I can say about this. Well then you should know about the Planviz ( blog written by my guru Lars Breddemann) .

The total count of base table is 15,332.

The query generated from data preview of created calculation view:

Copy the above query and do the plan visualization:

On the Overview tab, click on the number (Number of tables used )

which shows the number of records processed for each table which is 1,364 in my case

while the total number of records in base table are 15,332.

Also you can click on the Executed Plan tab for the execution diagram.

By this we can say that filter is applying at the initial stage rather than on result set

Regards

Raj Kumar Salla

[raghavendra_p]

Hello Raj,

Thank you for your comments.

Though it is not my actual question, But to comment on your logic, I am sharing my observation ( Input parameters filtering data at bottom most nodes.) on input parameters and Variable.

I have created two simple CVs with projection, join and aggregation node. One with Input parameter and other with variables.

SQL for same :

CV with Input parameter	CV with Variable

Further debugging views:

Input parameter filter is applying right at projection node:

Where as, In variable, 'Where' condition applying at aggregation node :

Variable - projection : No where condition, bringing all data from VBAK

Variable aggregation :

Applying filter here

[lbreddemann]

Your interpretation of what you see in the debug view windows is not correct.

The debug view doesn't anticipate all possible selection criteria but generates a default query that allows for the selected node to execute.

Raj has demonstrated that very well and PlanViz is the right tool to check for actual execution.

If the filter consideration is the only reason for the use of input parameters, then I'd recommend to reconsider as applying the conditions in the WHERE condition does the same thing.

[lbreddemann]

I'm not quite sure, why you want to provide all the filtering through parameters.

Why not simply leave it to the WHERE clause?

Which front end are the users using?

[raghavendra_p]

Hi Lars,

I don't understand your statement "Why not simply leave it to the WHERE clause?"

You mean, why don't i create variables and system will filter result set based on variables using where clause?

I actually want to filter data right at the time of fetching from data base rather at result set so using input parameters.

Secondly, we have nearly 10 variables, want to make four of them as input parameters to improve performance. that is where it lead to above requirement.

My reporting environment is analysis for office.

[former_member182302]

Have a look on this blog and also at the comments, it not always true that Input parameters give you better performance compared to variables.

Regards,

Krishna Tangudu

[former_member182302]

Have a look on the below thread which will answer on how to use MATCH :

Regards,

Krishna Tangudu

[raghavendra_p]

Hi Krishna,

Thank you for your time/response.

'Match' function is working fine with single input parameters, incase of multiple input parameters, if i use 'match' on all and if I pass value to one parameter and left another one blank, still system bringing all values.... ( Ideally, system should fetch data based on parameters entered only,.. just like BEx variables or any other variables)

ex - i have two parameters year and customer. If i pass customer value and left year as blank, system should fetch data for that customer for all years.