Error while data preview in sys_bic schema

[Former Member]

Hi,

I have made a calculation graphical view in a schema and I have used input parameter to filter the data on the basis of date.

it got succeeded and activated and when I do data preview it ask for a date prompt, Now when I am trying to preview its data through SYS_BIC schema it is not asking for prompt and giving an error(screenshot attached). Can anyone tell me what is the reason behind this error or how it

can be resolved.

Regards,

Karishma

[former_member184768]

Hi Karishma,

Couple of points:

• Can you please go to the Graphical Calc view, do the data preview and then go to View log. Copy the SELECT statement in the generated log and please post it here. It should have all the required parameters which are passed to the Calc view (could be related to filter based on the date or parameters for the currency conversion).
• Please refer to the document http://scn.sap.com/docs/DOC-27676 for using derived tables and Input parameters.

The SELECT statement from the Log will provide more details and avoid the confusion on which input parameters are exactly expected by the Calc view.

Regards,

Ravi

[rindia]

Hi Karishma,

First try with giving default value for your input parameter while creating it. Then do validation and activation. Then refresh your _SYS_BIC and try.

If it is working fine then you can play with keeping default value or not as per your requirement.

If not working then could you send the screenshot of your edit input parameter? Also let us know what is the version of your Hana studio?

Regards

Raj

[Former Member]

Hi Raj,

I have tried with default value, when I preview data with default value in SYS_BIC it works fine.

I guess reporting person have to write some sql in universe to pass  input parameter to front end using derived table.

Can you give me an example how to write that sql or what needs to do further.

Actually we are using input parameter for the first time and I have made it right at back end, we just don't know how to pass it to the  front end through SYS_BIC. Is it possible to create list of values and parameters in data foundation layer in the place of business layer.

I am attaching screen shot of error as well as of the parameter I have set.

Regards,

Karishma

[sorin_radulescu]

To pass the parameter from Universe you have to use the sintax

select ...from your calculation view ('placeholder'=('$$nameofinputparameter$$', 'value'))

Observation: name of input parameter must be lower case.

In this way you can run the query from sql editor or you can use the sintax into Universe to pass the parameters.

I hope this will help you.

Kind Regards

Sorin Radulescu

[Former Member]

Hi Sorin,

Thanks for the query, it is really helpful, I have a question this 'value' stands for the column name

or any thing else. my parameter lies between start_date and end_date so what I will put here in

value???

Thanks

Karishma

[sorin_radulescu]

So how many input parameters do you have? one or two?

Value means what is the value which will replace the Input Parameter (it is like default value)

When you will use the query into universe instead of value you will put the variable from Universe.

If you will have multiple input parameters the sintax is similar.

select ...from your calculation view ('placeholder'=('$$nameofinputparameter1$$', 'value1'), 'placeholder'=('$$nameofinputparameter2$$', 'value2'))

I hope this will help you.

Regards

Sorin

[Former Member]

Hi Sorin,

We have just one input parameter having name IP_DATE, it is working fine in HANA n asking about

prompt, Now coming to universe level

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'=('$$VARCURRENCY$$','USD'))

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

[sorin_radulescu]

Ok.

So basically you have to create a derivate table where you will use the prompt variable from Universe and the select which I mentioned

Example:

Let's suppose you created a prompt for Currency into your Universe then Derivate table must be:

SELECT *

FROM "_SYS_BIC"."mypackage/SALES" ('PLACEHOLDER'=('$$VARCURRENCY$$',@Prompt(Currency)))

I didn't understand your question related to IP_DATE because I didn't see any parameter regarding date.

If you don't associate any value for Input Parameters or Variable but you would like to select from calculation view also without passing values for these variables then you have to provide default values for variable or Input parameters created. (You have a field when you create Input Paramters or Variable called Default Value).

I hope this will help you.

Regards

Sorin