CE syntax with Placeholders and parameters

[Former Member]

Hello Experts,

a) How can we convert a SQL syntax like:

SELECT .......FROM CV1

(

            PLACEHOLDER."$$ZIP_CV_DAT_GYTD_FROM$$" => :V_GYTDfrom )

WITH PARAMETERS ( 'PLACEHOLDER'=('$$ZV_CV_ATT_VENDOR$$', 'XXX') );

b) how to pass a Variable prompt from BO to HANA within the above syntax? with what do we need to replace XXX

Thanks & regards,

Jomy

[former_member184768]

Hi Jomy,

The following document will answer your second question.

I did not understand your first question.

Regards,

Ravi

[Former Member]

Hi Ravi

I already went through this document and somehow not able to find answer to my question.

Let me try to explain my scenario:

We've a scenario, wherein we've normal Fiscal year Period and Vendor Specific Fiscal year period i.e.

If Fiscal year Period = 03/2013 and Vendor V1 closing Month = 6(This information is only stored in Vendor master) i.e. June then

  Vendor Fiscal Year period = 9/2013

and we have different Time frames like YTD, LYTD, Current Month, MTD, Current Day, Same Day Last year, Last Month

and Vendor based i.e. V_YTD, V_LYTD and so on.

So we've a graphical Calculation view with all these different projections.

On top of which we've Script based Calculation View, where we get Date Input parameter and Vendor Input Parameter from BO to calculate values for all input parameters which is there at different projections in the underlying Graphical calculation view

as shown below:

select ACTSS into mnth1 from "<SCHEMA_NAME>"."<TABLE_NAME>"

where LIFNR = ;

mnth2 := 12 - mnth1;

V_VYTDfrom := add_months(to_date(V_YTDfrom,'YYYY-MM-DD'),mnth2);

V_VLYTDfrom := add_years(to_date(V_VYTDfrom,'YYYY-MM-DD'),-1);

V_VLYto := add_months(to_date(V_VLYTDfrom,'YYYY-MM-DD'),12);

SELECT *

     MATNR,KUNNR,WERKS,MFRNR,DATE_SQL,ZCA_IDENTIFIER,VKBUR,ZCM_AN_NET_SALES

FROM "<GRAPHICAL_CALC_VIEW>"  

            (

            PLACEHOLDER."$$ZIP_CV_DAT_YTD_FROM$$" => :V_YTDfrom,

            PLACEHOLDER."$$ZIP_CV_DAT_DATETO$$" => :V_DATEto,

            PLACEHOLDER."$$ZIP_CV_DAT_LYTD_FROM$$" => :V_LYTDfrom,

            PLACEHOLDER."$$ZIP_CV_DAT_LYTD_TO$$" => :V_LYTDto,

PLACEHOLDER."$$ZIP_CV_DAT_VYTD_FROM$$" => :V_VYTDfrom,

            PLACEHOLDER."$$ZIP_CV_DAT_VLYTD_FROM$$" => :V_VLYTDfrom );

and hence needs to pass this to corresponding projections.

I do have two questions:

a) Is there any way other than using Script based calculation view to do with filter expressions. If yes then how ?

b) Suppose if multiple vendors are passed then in that case we need to use Variables for Vendor and Input Parameters. Then in that case how can script based calculation read that Vendor and to do a select on Vendor master to get the closing month.

Thanks & regards,

Jomy

[former_member184768]

Hi Jomy,

I presented few options on Period reporting models in HANA

In your case, I think you can create input parameters for the Scripted Calc view. These input parameters can be populated by the prompts in BO and these input parameters can then further be used to derive the variables in the scripted Calc view. The variables then can in turn be passed to the Projection input parameters.

Regards,

Ravi

[Former Member]

Hi Ravi,

Thanks for your response!!!

If I'm corrrect, you are backing our approach to have to script based  calculation view because of special vendor logic, otherwise we would have just done with Graphical calculation view with different projection parameters and those parameters can be passed either from BO or needs to be derived in BO based on on just 1 -2 input paramater.

In any case, how can we handle a scenario wherein multiple vendors are passed and we need to read the passed vendors to perform the below operation in script based calculation view:

select ACTSS into mnth1 from "<SCHEMA_NAME>"."<TABLE_NAME>"

where LIFNR = ;

Thanks & regards,

Jomy

[former_member184768]

Hi Jomy,

Well, do personally do not recommend script based calc view because of the following:

• performance is not optimal for script based calc views
• Maintenance is very high and time consuming

But if you do not have the feasibility of generating the required filters in the front end tool, then you may have to use script based calc view for the additional business logic to derive the filter values.

I did not completely understand the concept for the multiple vendors, but if you are suggesting that multiple values should be passed from the front end tool and should be then passed to the SELECT statement in the calc view then those values cannot be passed using a single input parameter. You need in ( ) operator with the list of values and I think that will generate multiple records for mnth1 variable.

As I said, I am not very sure if my understanding is correct, but in that case you may not be able to use this approach.

Can you please elaborate with some sample data.

Regards,

Ravi

[Former Member]

Hi Ravi,

What i meant is suppose, you have Vendor Prompt in your BO tool where user select multiple vendors.

What we need to do in Script based Calculation View is read the value of any of the selected vendor and then execute that select statement to find the Vendor Close month as given below:

select ACTSS into mnth1 from "<SCHEMA_NAME>"."<TABLE_NAME>"

where LIFNR = ;

The problem is how can we read any of the chosen vendor in Script based calculation View.

Thanks & regards,

Jomy

[former_member184768]

Hi Jomy,

I think this issue is now related to selecting one value from a list of values provided in BO Prompt. You can pass only 1 value to the input parameter to the scripted view. So even if the users select multiple values, your BO prompt should pass only 1 value.

But again, I am not sure if that will be the correct implementation. If the ACTSS value differs for different vendors, then selecting any single value will be incorrect for other vendors selected.

Coming back to selecting one values from multi-value prompt in BO, I think you should raise that question to the Business Object forum.

Regards,

Ravi

[Former Member]

Hello Ravi,

Yes, we can pass only 1 value to Input parameter. I read somewhere that we can pass multiple values but not sure where.

We know that in SQL script based Calculation View, if we use CE functions instead of SQL, then col pruning will happen. Additionally CE functions are preferable over SQL.

Can we pass Input parameter in CE functions just like we do in SQL, so that I can try with that option also as we are experiencing performance issue, even with 5-6 dimensions and 10-12 measures.

Thanks & regards,

Jomy

[former_member184768]

Hi Jomy,

Multiple parameters can be utilized with in function in projections. It can accept multiple values, but then you will get multiple values for the ACTSS column. You may have to change the logic to either select the first value for the month or find an alternative depending on the business logic.

You can try to use IN function in the CE_PROJECTION function for the filter.

Regards,

Ravi

[Former Member]

Hello Ravi,

It seems there's some confusion. I was having two questions:

a) How can i change the following SQL script CALC View to have only CE functions, as you mentioned that CE functions are better in terms of performance:

select ACTSS into mnth1 from "<SCHEMA_NAME>"."<TABLE_NAME>"

where LIFNR = ;

mnth2 := 12 - mnth1;

V_VYTDfrom := add_months(to_date(V_YTDfrom,'YYYY-MM-DD'),mnth2);

V_VLYTDfrom := add_years(to_date(V_VYTDfrom,'YYYY-MM-DD'),-1);

V_VLYto := add_months(to_date(V_VLYTDfrom,'YYYY-MM-DD'),12);

SELECT *

     MATNR,KUNNR,WERKS,MFRNR,DATE_SQL,ZCA_IDENTIFIER,VKBUR,ZCM_AN_NET_SALES

FROM "<GRAPHICAL_CALC_VIEW>" 

            (

            PLACEHOLDER."$$ZIP_CV_DAT_YTD_FROM$$" => :V_YTDfrom,

            PLACEHOLDER."$$ZIP_CV_DAT_DATETO$$" => :V_DATEto,

            PLACEHOLDER."$$ZIP_CV_DAT_LYTD_FROM$$" => :V_LYTDfrom,

            PLACEHOLDER."$$ZIP_CV_DAT_LYTD_TO$$" => :V_LYTDto,

PLACEHOLDER."$$ZIP_CV_DAT_VYTD_FROM$$" => :V_VYTDfrom,

            PLACEHOLDER."$$ZIP_CV_DAT_VLYTD_FROM$$" => :V_VLYTDfrom );

b) If we pass multiple values for a parameter, can we use any one of them in our script . If yes, then how ?

Waiting for your reply and Thanks again!!!

Jomy