SAP HANA Variables and Parameters

[Former Member]

Hi all.

We currently at a client busy doing a customer age analysis report.

We need to filter all the data on an input key date i.e. posting date <= input posting date.

Now how do we use a Variable (using SAP Hana Studio) based on a posting date column and apply the operand <=?

In SAP HANA Studio, we cannot seem to find the place to reference and use the Variable when defining a Restricted Measure!

Anybody have a solution for this?

It's pretty urgent, as the customer wants to purchase Hana, however this feature needs to be able to do this.

Your help will be very much appreciated!

Regards,

Thatoyame

Email: thatoyame@gmail.com

[Former Member · Accepted solution]

Hi Thatoyame,

An input parameter is basically meant for processing a calculation (not limited to) based on users input.

A variable on the other hand is used to restrict the number of records which you see in the output of a view.

Having said that, if your requirement is to get an input from the user and check if that is >= Posting date then follow these steps:

1. Create an input parameter, say IP_PSTNG_DATE

2.In projection, go to filter and create an expression like PostingDate<='$$IP_PSTNG_DATE$$'

This should help you in restricting the number of records.

For restricted measure, since it can not be implemented using a variable, you can create a Calculated measure where you can write an IF ELSE logic to restrict the value:

IF(PostingDate<='$$IP_PSTNG_DATE$$' , "<Keyfigure>" ,0)

Hope this helps.

Regards,

Piyush

[Former Member · Accepted solution]

Hi Piyush.

Thanks very much for your assistance!

That is what I exactly did, and it helped!

However, I have one more challenge...

Now, I am using BO Web Intelligence for my presentation layer... How do I use the input parameter from SAP HANA and use it in BO? How can it be inherited from BO to HANA?

Thanks.

Kind Regards,

Thatoyame

[former_member184768 · Accepted solution]

Hi Thatoyame,

Please refer to the following document. It explains how to use HANA input parameters in IDT.

http://scn.sap.com/docs/DOC-27676

Regards,

Ravi

[Former Member · Accepted solution]

Hi Thatoyame,

You need to create a derived table in Data Foundation Layer of Universe in IDT. The query should have the placeholder syntax for the input parameter.

Follow these steps:

1. Create a parameter in data foundation of IDT. This will be the prompt visible to the end user while executing the report.

2.Create a derived table in Data foundation in IDT

3. Use the following syntax for the SQL required in derived table:

     SELECT <col1>,<col2>... FROM <viewname> ('PLACEHOLDER'=('$$IP_PSTNG_DATE$$',<prompt>))

In the above syntax, PLACEHOLDER is a keyword,

'$$IP_PSTNG_DATE$$' is the input parameter created by you in your view and <prompt> should be replaced by the actual prompt created by you in step 1.

Now create your business layer using this derived table. This should bind the Input Parameter with the front end prompt.

For more details you can refer to the link provided by Ravi in his post or you can search for the document named: "Using SAP HANA Variables and Parameters in SAP BusinessObjects BI4.0". This is a good read for the usage of Input Parameter and Variables. Somehow i am unable to open the link provided by Ravi.

Regards,

Piyush

[Former Member · Accepted solution]

Hi Piyush & Ravindra.

Thanks for that, I actually used the same document you specified!

However, I didn't want to use a derived table as there was no need...

So what I did on the IDT within the Data Foundation layer, was to pass a parameter and appended it with a “WITH PARAMETERS (placeholder…  ”  at the end of the SQL sentence.

I did so by using the END_SQL property of the data foundation. This property accepts prompts as well.

So finally my END_SQL statement will looked like this :

WITH PARAMETERS (placeholder = ('$$keydate$$', @Prompt(myKeydate)))

That is a way of passing parameters from BO to SAP HANA. Works like a charm!

Nevertheless, thanks for ALL your helps guys, much appreciated!!

Regards,

Thatoyame

[Former Member · Accepted solution]

HI Piyush,

I'm struggling with an issue. I followed the steps to create an parameter in the data foundation, define input parameters in a calculation view, create a derived table with placeholders and referenced the value of the placeholder to the input parameter. So far so good. When I query from within the IDT it works fine and the parameters are shown, values are passed and a column is computed at runtime in the view. Unfortunately I get an error when I want to build a query from Web Intelligence. When I replace the references to the parameters from the data foundation to a fixed value, I can query from within Web Intelligence.

Can you or anyone else help me out here?

With kind regards,

Martijn

[Former Member · Accepted solution]

Hi Martijin,

Can you please share the SQL generated by WebI for this report? Also, can you please share the error message?

It would give some idea about the issue.

Regards,

Piyush

[former_member212706 · Accepted solution]

Hi Piyush,

I tried following the same way as you had mentioned for binding an input parameter with the universe prompt. i get an error staing

SAP DBTech JDBC: [257] (at 99): sql syntax error: incorrect syntax near "(": line 3 col 82 (at pos 99)

and my sql script for the derived table is

SELECT *

FROM

"_SYS_BIC"."ecssap-utilities/CLV_AVG_DAILY" ('PLACEHOLDER' = ('$$ip_profile$$', @Prompt(Profile) ))

[Former Member · Accepted solution]

Hi Rajesh.

Did you add the END_SQL property within the business layer or data foundation? If not, you need to do that! You will find this in either the  So the business layer or data foundation within the Query Script parameters section....

So in the END_SQL you should set something like:

WITH PARAMETERS (placeholder = ('$$ip_profile$$', @Prompt(Profile))).. Make sure you have created a prompt called "Profile" in either the Business Layer or Data Foundation...

Also make sure that, where you define your prompt (Prompt Options) in either the business layer or data foundation, you have the "Allow multiple values" tick/check box UNTICKED. I used to get the same error as you when I had that field ticked.

Let me know how it goes.

Regards,

Thatoyame

[Former Member · Accepted solution]

Hi Martijn.

You don't necessarily have to use derived tables to pass parameters from BOBJ to HANA...

You can add END_SQL property within the business layer or data foundation?  You will find this in either the  So the business layer or data foundation within the Query Script parameters section....

So for e.g. in the END_SQL you should set something like:

WITH PARAMETERS (placeholder = ('$$ip_profile$$', @Prompt(Profile))).. Make sure you have created a prompt called "Profile" in either the Business Layer or Data Foundation...

Also make sure that, where you define your prompt (Prompt Options) in either the business layer or data foundation, you have the "Allow multiple values" tick/check box UNTICKED. I used to get the same error as you when I had that field ticked.

Regards,

Thatoyame

[Former Member · Accepted solution]

Hi Piyush,

I managed to resolve the issue in the meantime doing some research. As I noticed others have been struggling as well I will share how I resolved my specific issue:

As I mentioned from with the HANA Studio and the IDT I was able to execute a query, filled in the 2 prompts and get a result back. I wanted to make sure that the Calculation View was working properly so I tried to use it in Analysis for MS Excel. There I found out that altough the prompts displayed, entering a value showed a nested exception.

This nested exception was related to the fact that I choose attribute as Type and selected a attribute which was part of the Calculation View when creating the input parameters in the HANA Studio. Having changed the Type to blank resolved the issue in Analysis for MS Excel.

The second step was to build the LOV, which was tight to the Parameters in the Data Foundation, based on the original tables instead of on an attribute which was part of the (derived table of)Calculation View.

I also unmarked the checkbox that allows multiple values to be selected.

Hope this helps others!

With kind regards,

Martijn van Foeken

[Former Member · Accepted solution]

Unmarking the check box for "Allow Multiple Values" is a must. As the moment you check that box, the operator applied for comparison is "IN" and not "=". The moment "IN" operator is applied, you get the entered value inside a pair of braces (). And in majority of the cases you get this error, which Rajesh has mentioned in his post.

Glad to hear that your issue is reolved now.

Regards,

Piyush

[Former Member · Accepted solution]

Rajesh, the error which you are getting is definitely because of the "Allow Multiple Value" being checked for the "Profile" prompt. Because if you read the error message, it points to the 82nd character which is @Prompt. When you select the "Allow Multiple Values" box, it puts a pair of braces around the entered values which is not allowed in the PLACEHOLDER syntax.

Regards,

Piyush

[Former Member]

Hi All,

I am trying to pass a numeric prompt to users using placeholder through Derived table.

The syntax I am using is:-

select * from <view_name> ('PLACEHOLDER'=('$$name of the input parameter$$',<prompt or value which you want to pass>)).

In the above syntax for the prompt I have made a parameter which is number.When I try to pass that parameter in place of prompt definition in the above syntax it gives me error,but when i pass that parameter in single quotes the expression gets validated successfully.But I want to pass it as a numeric prompt and after defining the parameter as numeric how can it get validated when i pass it in single quotes.

Any help on this will be appreciated.

Thanks in advance.

[Former Member]

GROUP BY "NAME"

WE CAN ACHIEVE BUT MULTIPLE VARIABLE IS NOT POSSIBLE , KINDLY LET ME KNOWN ANY ONE KNOWN

ADVANCED THANKS

VINAY

[Former Member]

Hi,
I am working on SAP BO4.0.I have a requirement of creating a month filter on a date which will be between two ranges.The first range is the user response and the second range will be 6 months added to that user response.For eg:-If a user enters 1 as the response for month prompt then the date will be filtered between 1 and (1+6)=7.The database used is SAP HANA.
Please let me know if further clarifications are required.
Any help on this will be appreciated.
Thanks in advance.

[former_member182302]

Hi Riddhi,

Have a look on this blog

This should give you some thoughts

Regards

Krishna Tangudu

[Former Member]

Hello Folks,

I'm  having hard time finding the solution for what if I have two input parameters from HANA calc view? how to handle them in the universe(IDT)?

I did lot of research on the web and all the examples are related to one parameter. No luck.

I have following input parameters defined in HANA calc view:

Division

SalesOrg:

I tried to create derived table and using syntax :

SELECT *

FROM "_SYS_BIC"."new/InvoiceTestQuery" ('PLACEHOLDER'=('$$Division$$',@Prompt(Division)))

It is giving me error saying that SAP DBTech JDBC: [2048]: column store error: search table error: [34092] search on calculation model requires parameters;Required variable $$SalesOrg$$ is not set.

Basically it is looking for SalesOrg parameter. 2nd parameter.

How to handle second parameter in the derived table?. Any help would be appreciated.

Thanks

Remata

[Former Member]

Hi Piyush,

I have also made the Input Parameter in the same way you mentioned. it asks for a prompt while data preview, but if I want see the same Hana view through SYS_BIC

it does not ask for a prompt and throws an error saying please enter a value for parameter or set any default value. I have already made it mandatory still it gives error.

Is it the case that we do not get prompts in column views in SYS_BIC schema ??

Please suggest I am really stuck.

Regards,

Karishma

[Former Member]

Hi,

Can you please share the SQL which you have written to view the data from SYS_BIC. Remeber you need to use the PLACEHOLDER syntax in your query, if you want to pass a value to Input Parameter through SQL.

Regards,

Piyush

[Former Member]

Hi Piyush,

I guess the SQL is to be written at universe level, not at back end in sap hana,

I have made the parameter with the name $$"IP_DATE"$$

Can you please tell me how to write its SQL with placeholder.

it is working fine at hana end, our concern is to pass it at BO level.

the parameter is to choose a date which is between start date and end date, these condition is defined in filter.

Please have a look at the screen shots to have a clear view of the condition.

Regards,

Karishma

[Former Member]

Hi Karishma,

Here is the syntax.

SELECT <col1>,<col2>... FROM <viewname> ('PLACEHOLDER'=('$$Input_Date$$',<prompt or value which you want to pass>))

Please read through this thread to understand how you can bind and input parameter to universe. I and Ravindra have given reference to few documents which will give you the exact way to implement it.

Regards,

Piyush

[Former Member]

Hi Piyush,

I have read that article and used the same approach. According to it,

HANA engine expects a query syntax in this format:

SELECT […]

FROM <viewname> ('PLACEHOLDER' = ('$$<parameter name>$$',<parameter value>)

WHERE <attribute name>=<filter value>

Example :

SELECT *

FROM "_SYS_BIC"."mypackage/SALES" ('PLACEHOLDER'=('$$IP_DATE$$','12-01-2013'))

we do not need any filter value, just want the prompt IP_DATE

But, I am unable to create derived table using this function too with error below:
Error:
SAP DBTech JDBC: [2048]: column store error: search table error:  [34092] search on calculation model requires parameters;Required variable $$Input_Date$$ is not set.


So, is it possible to create dervied table on view with input parameter function at universe level???

I am not getting that if I have made the query using placeholder, still it is giving the same error

Why?

Regards

Karishma

[Former Member]

Hi,

Please check the name of the input parameters in the HANA model and in the query used in universe. In HANA model it is $$Input_Date$$ whereas in the query it is $$IP_DATE$$. Please use the same name as in the HANA model.

Regards,

Piyush

[Former Member]

I'm pretty sure IP_DATE should be ip_date in the PLACEHOLDER syntax.  (Even though it's defined as IP_DATE).

Case sensitivity can sometimes be a bit of a hangup =/

[Former Member]

Hi Guys,

I am facing similar issues, but in my case I have 2 parameters on Calc View. Now when i include the Date Parameter Prompt it works, but as soon I add StoreID Prompt my SQL fails. I have unmarked the check box for "Allow Multiple Values" as well.

When I add Store ID prompt to the SQL, I get the following error:

Store ID Prompt is numeric type and Date Prompt is date type

Please help!

Regards

Sushant Jain

[Former Member]

Hi Sushant,

There are 2 problems which i notice here:

1. You are not using the Store ID Prompt in the Placeholder syntax. You have hardcoded the value '1001'. In this case if all your syntax is correct, the user will not be prompted for the Store ID as its prompt is not referred.

2. What is the data type of the Store ID input parameter in HANA? if it is numeric then the problem is quite basic. You are passing a value '1001' where as it is expecting 1001. i.e. the moment you put a value into a pair of single quotes, the SQL engine treats it as a string. So essentially you are trying to compare a numeric value with a string value. I suggest you remove the single quotes, if the input parameter of Store ID is having numeric data type in HANA.

Regards,

Piyush

[Former Member]

Hi piyush,

I am trying to pass parameters to calculation view from IDT

i have created a derived table and used the following syntax

SELECT *

FROM "_SYS_BXXXX" ( 'PLACEHOLDER' = ('$$P_GR_QTY_TOL$$', @Prompt(Total)), 'PLACEHOLDER' = ('$$P_TOL_IN_CAL_DAYS$$', @Prompt(Total2)))

But i m getting

Even i tried with the end sql ...when i pull a object it is prompting But finally getting syntax error

Error:

[] <com.businessobjects.dsl.commons.exception.NestedException: Failed to execute: SELECT

  Table__1."Material"

FROM

  "_SYS_BIC"."XXXXX"  Table__1

('PLACEHOLDER' = ('$$P_GR_QTY_TOL$$', '50'), 'PLACEHOLDER' = ('$$P_TOL_IN_CAL_DAYS$$', '80'))>

Cause of Error

SAP DBTech JDBC: [257] (at 97): sql syntax error: incorrect syntax near "(": line 5 col 1 (at pos 97)

[Former Member]

H dhana,

Is it possible that the columns/ input parameter defined in underlying view is numeric/decimal/integer? If so then do not put the vales 50 and 80 in single quotes. Keeping them in quotes makes them string. Also , if the input parameters are numeric then check the prompt created in idt and make sure you are using numeric prompts and not string.

Also check if allow multiple selection is disabled or not in the prompt.

Regards,

Piyush

[RodrigoCaparroz]

Hi dhana babu,

Were you able to solve this issue?

Best,

Rodrigo.

[former_member184768]

Hi Thatoyame,

Did you try using Projection with Input parameters. In restricted Measures, I could not see the variable usage, but the similar functionality can be achieved with Projections as a work around.

I am not sure if it fits your use case, but worth trying.

Regards,

Ravi

[Former Member]

Hi Ravi.

Thanks for your prompt response.

Could you perhaps explain how to do your proposed work-around as I am not sure?

Thanks.

Regards,

Thatoyame

[former_member184768]

Hi Thatoyame,

As I mentioned, I am not completely aware of your business case, but based on the information you provided, I think you can create one projection for each age bucket and have all the projections in UNION.

This will have to be done in the Calc view.

Regards,

Ravi

[Former Member]

Hi Ravi.

I don't need a projection for each age bucket, I already have that done.

My issue is that I want to filter all the data on an input key date i.e. posting date <= input posting date.

Now how do I use a Variable (using SAP Hana Studio) based on a posting date column and apply the operand <=?

In SAP HANA Studio, I cannot seem to find the place to reference or use the Variable when defining a Restricted Measure! That's issue I am facing.

Thanks,

Regards,

Thatoyame

[former_member184768]

Hi Thatoyame,

I understand that currently we cannot use a variable in the restricted measure. Hence I suggested the work around.

But you also mentioned that you wish to filter ALL the data based on a column. This is different from using Restricted measure as the restricted measure filter is applicable to that column only. The projection provides you similar feature to define the restriction on data for a perticular column.

I am sure you must be aware of the classical "Projection scenario" of "Actual" vs. "Planned" data. In BW, this can be implemented with Restricted KFs, but in HANA it can also be implemented with Projections.

I was trying to suggest the same.

Regards,

Ravi

[Former Member]

Hi Ravi.

Yes I am aware of the classical "Projection Scenario" & that it can be done in BW...

Now if I create a Projection, where do I define a condition where I can define an input key date i.e. posting date <= to an input posting date. Also how can I prompt this and reference or use a Variable when defining a Restricted Measure?

You get me?

Regards,

Thatoyame

[former_member184768]

Hi Thatoyame,

When you create the projection, you can provide the restriction in "Filters" -> Expression. In the Expression you can use Input Variable which will prompt the user a value to be entered.

Restricted measure cannot use variables so it cannot to be done in Restricted Measures.

Regards,

Ravi

[Former Member]

Hi Ravi.

Thanks for that, as it helps a bit...

However, I am still struggling with that restricted measure part?

What work-around is there for that?

Also, what is the real use of a Variable & an Input Parameter?

I know an Input Parameter is similar to a prompt, however, I am not sure what a variable's use? Can you provide an example?

Thanks.

Regards,

Thatoyame

[former_member184768]

Hi Thatoyame,

As mentioned earlier, I do not have any more information on the restricted measure part.

Coming to the difference between Input parameter and variable, even it had been bit confusing to me. To my knowledge, the Input parameters are used for the design time objects and variables are used for the "where clause condition" for data filtering.

Please refer to the following thread in which the same issue is discussed.

http://scn.sap.com/thread/3232859

Regards,

Ravi

[Former Member]

Hi Ravi.

Thanks for all your help!

Most of the work-arounds you gave me, did help me alot...

Thanks.

Regards,

Thatoyame

[sudha_rao2]

This message was moderated.