This document is prepared on HANA SPS 12 based on this thread.

Use case: The user is asked to enter a date. If nothing is specified then it should be default to first day of the current month else user specified value should be used to filter the data, using Graphical calculation view.

If you are thinking to do by using Input parameter with “Derived from Procedure/Scalar function”

then you are almost there with just 2 hurdles to cross.

For this demonstration I’m using the below table structure for which the field

DOJ is of type DATE on which the input parameter will be used.

1.TableStructure.jpg

Sample Data in the table:

2.SampleData.jpg

Create a procedure which returns the date (first day of current month).


CREATE PROCEDURE RAJ.FIRST_DAY (OUT FIRST_DAY DATE)

LANGUAGE SQLSCRIPT

AS

BEGIN

—- Write the logic based on business requirement.

—- This logic gives you the first day of the current month as date

    SELECT ADD_DAYS(ADD_MONTHS(LAST_DAY(CURRENT_DATE),-1),1)

/*   Alternate logic

SELECT LEFT(TO_CHAR(CURRENT_DATE),4) || ‘-‘ ||
SUBSTR(TO_CHAR(CURRENT_DATE),6,2)|| ‘-‘ ||’01’
      INTO FIRST_DAY

FROM DUMMY;

 

*/

END

;

 

 

Call the procedure to see the output:

CALL RAJ.FIRST_DAY(?);

 

3.ProcOutput.jpg

 

In graphical calculation view, Create Input parameter which is based on above procedure.

Now you will come across thefirst hurdle

Error message is – Procedure must have scalar parameter of type String, which is because of the product limitation.

4.InputParameterDateNotAllowed.jpg

 

The input parameter is based on Date and there is a product limitation to use string type only.

Fortunately the dates are given in single quotes in a SQL query, hence this should not stop us to go ahead.

5.DatesinQuotes.jpg

Let us go back to Procedure and change the

out parameter type from DATE to VARCHAR(10) and

convert the date output to string using TO_CHAR.

DROP PROCEDURE RAJ.FIRST_DAY;

CREATE PROCEDURE RAJ.FIRST_DAY (OUT FIRST_DAY VARCHAR(10))

LANGUAGE SQLSCRIPT

AS

BEGIN

    SELECT TO_CHAR(ADD_DAYS(ADD_MONTHS(LAST_DAY(CURRENT_DATE),-1),1))

    INTO FIRST_DAY FROM DUMMY;

END

;

 

Now in the input parameter, give the procedure name. This time it should accept

without error message. Hurdle number 1 crossed

 

6.ProcTypeString.jpg

 

Apply the input parameter on your required date field in Graphical calculation view (in my case the field is DOJ).

 

7.ApplyIP.jpg

7A.GCV.jpg

 

Validate and Activate the view.

 

8.IPValueNotPassedFromProc.jpg

 

On Data Preview you see Input Parameter output value is not passed. Here comes the hurdle number 2

Somehow the output of procedure is not getting populated correctly. Could not catch the actual reason for this.

Generally when Attribute/Analytic/Calculation view is activated, a column view of the same is placed in _SYS_BIC from which the system access.

So I changed the schema name in procedure to _SYS_BIC.

(
Now I figured out that, we need to give execute privilege on your schema(RAJ) in which procedure is created to _SYS_REPO

GRANT EXECUTE ON SCHEMA RAJ TO _SYS_REPO WITH GRANT OPTION;

By doing this, it is no more required to create procedure in _SYS_BIC.

The below work around is no more required.
)

The only change this time will do is change the schema name to _SYS_BIC.

DROP PROCEDURE _SYS_BIC.FIRST_DAY;

CREATE PROCEDURE _SYS_BIC.FIRST_DAY (OUT FIRST_DAY VARCHAR(10))

LANGUAGE SQLSCRIPT

AS

BEGIN

SELECT TO_CHAR(ADD_DAYS(ADD_MONTHS(LAST_DAY(CURRENT_DATE),-1),1)) INTO FIRST_DAY FROM DUMMY;

END

;

Check the output of your procedure by CALL _SYS_BIC.FIRST_DAY(?);

 

 

Now modify the Input parameter in calculation view to point to schema _SYS_BIC.

 

9.IPwithSysBic.jpg

 

Activate the view and do the data preview.

This time you will see the output of the procedure being populated. Hurdle number 2 crossed

 

10.ValueFromProc.jpg

 

Output of view based on input parameter value returned from procedure:

 

11.OutputWithDefaultValue.jpg

 

Getting the required result. Now again do the data preview and give the date we want,

12.GivenInput.jpg

Data is fetching as expected.

Thank you for reading

 

To report this post you need to login first.

19 Comments

You must be Logged on to comment or reply to a post.

  1. Anindya Bose

    Hi Raj

    Liked the way you wrote this piece.  I think this would even work for lower SPS as I am using lot of IP type Derived from Procedure in SPS 10 .

    Regards

    Anindya

    (0) 
    1. Raj Kumar Salla Post author

      Thanks Anindya 🙂

      On top of every document, I made the habit of specifying SPS level so that others who try might see some differences in options due to different SPS levels.

      The proposed solution would work for lower levels too.

      Regards

      Raj

      (0) 
  2. Sudarshan Survepalli

    Thanks for the detailed blog Raj.

    Have a question, we just migrated to SP12 and having a peculiar issue with data preview on Calc views with input parameters. From the HANA studio, the preview screen isn’t popping up.

    HANA Versions is: 1.00.122.01.1470046197

    Studio is: 2.3.8

    Are you facing any similar issues? Please can you share the version of studio or HANA that works.

    Thanks

    Sudarshan

    (0) 
    1. Raj Kumar Salla Post author

      Hi Lim,

      As you said your SPS level is 9 and that’s why you cant see this option.

      Minimum SPS 10, I guess, is required to see this option.

      Regards

      Raj

      (0) 
  3. Alejandro Serrano

    Hi Raj,

    I am trying to use Hana Stored Procedures in different ways. In my case I have a table in BW. I want to access to this table, pre-process it and apply a AFL predictive function (Single Exponential Smoothing). Then, I want to store the results and access them from BW. After some research, I managed to find 5 different ways to approach this problem:

    1. Using SQLScript from SAP HANA Studio
    2. Using Application Function Modeller (AFM) from SAP HANA Studio
    3. Using SAP Predictive Analytics (SAP PAA)
    4. Use BW report (ABAP) to trigger a SQL PROCEDURE
    5. Using Predictive Algorithms native in BW

    In points #1 to #4 I get stack when calling the procedures. Please, have a look to https://answers.sap.com/questions/148420/five-approaches-to-execute-a-predictive-afl-functi.html.

    Any help, new idea or approach will be very useful. Thanks and regards!

    (0) 

Leave a Reply