cancel
Showing results for 
Search instead for 
Did you mean: 

Use of User Input Variables?

Former Member
0 Kudos

As our HANA has been upgraded to SPS03 (SP21), I didn't hesitate to try user input variables. So I have created a variable V_CM in my Analytical View for, say, CALMONTH attribute, of the same data type as CALMONTH (varchar 6). Then I created a Calculated Measure (e.g. "CM1") with the formula logic to return 1 for CALMONTH variable equal to, say, '201112', and 0 otherwise.

What I noticed is that running a SQL query in the SQL Editor for that Analytical View didn't render a variable value entry popup, and CM1 was always 0. The same result I've got when running Analysis for Excel on top of that View.

I only got that variable value entry popup when running Data Preview function on my Analytical View in Studio -- but that has no practical use. When running Data Preview, I noticed that the generated SQL statement contained stuff like " ('PLACEHOLDER' = ('$$V_CM$$', '201102'))" -- is it how variables should be used in a SQL Editor to simulate the variable entry? How about Analysis for Excel and other user frontend tools?

Or is this functionality in the very early stage and should not be used yet? Any comment?

Thanks!

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

The current version of HANA does provide variable functionality however none of the reporting tools recogonizes HANA variables currently. Recoginition of HANA variable is definitely in the development pipeline and would be available soon however not have info on exact time-line.

My understanding based on discussion is BW on HANA has to play key role why variables are available in the current version of HANA.

Answers (1)

Answers (1)

tomas-krojzl
Active Contributor
0 Kudos

Hello,

I noticed that the generated SQL statement contained stuff like " ('PLACEHOLDER' = ('$$V_CM$$', '201102'))" -- is it how variables should be used in a SQL Editor to simulate the variable entry?

Yes.. you can find more information in SAP HANA Database - SQL Script Guide

page 16 - chapter CREATE PROCEDURE

https://service.sap.com/~sapidb/011000358700000604932011

Normally, procedures can only be executed via the call statement. By using WITH RESULT VIEW it is possible to query the result of a procedure as part of a SQL statement. Please notice that no variable references are supported in the WITH PARAMETERS clause and that all constants are passed as string constants (i.e. enclosed in single quotes). Also, parameter names are lower case independent from the original capitalization in the procedure signature.

Example:

SELECT * FROM ProcView WITH PARAMETERS ('placeholder' = ('$$id$$', '5'))

Procedures are sharing same "roots" as Calculation views...

Tomas

Former Member
0 Kudos

Hello, Tomas.
This thread is quite old, but still I hope you'll see my question!

I've been googling for two day but I could find no information about how to pass more then one parameter to procedure with result view!..

Can you, please, tell me, how to perform this?

Yours,

Oleg O. Rotkin

aadityanigam
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hello Oleg

on SP7 the following works

SELECT * from ProcView(PLACEHOLDER."$$anint$$"=> 5,PLACEHOLDER."$$another$$"=> 7 );

OR

SELECT * from ProcView('PLACEHOLDER'=('$$anint$$', '5'),'PLACEHOLDER'=('$$another$$', '7'));


Warm regards

aadi

Former Member
0 Kudos

Hello Aadi,

Thank you so much, it does work!

Yours,

Oleg Rotkin