Creating Procedure Based HANA Input Parameters To Derive Dynamic MTD, QTD, YTD Metrics

05 Jan Creating Procedure Based HANA Input Parameters To Derive Dynamic MTD, QTD, YTD Metrics

MikePink2 Mike Pink

I am often approached with a business requirement to provide the ability for an end user to enter a Fiscal Year/Period which then drives a set of key figures based on that Fiscal Year/Period.  For example provide MTD, QTD, YTD, Last year YTD, 3 Month Rolling, 6 Month Rolling, along with many other variations.  HANA SP10 provides the ability to meet this requirement with input enabled procedure based input parameters.

SAP HANA SP10 provides the ability to create Input Parameters with parameter type “Derived from Procedure/Scalar Functions” with input parameters.  Prior to HANA SP10 the Input Parameters of type “Derived from Procedure/Scalar Functions” did not provide the ability to pass values as input which limited the use of the input variables based on procedures or functions.  The ability to pass values to the procedure based input parameters provides an array of options to meet business requirements.

Step-by-Step Process to provide dynamic QTD Key Figures in a HANA Calculation view:

  1. Create HANA Procedure which provides QTD beginning Fiscal Year/Period
  • Login into HANA Web Editor within the SAP HANA Web-based Development Workbench
  • Navigate to the package you would like to create the procedure within and right click and select “New–>HDB Procedure”

Figure 1: Create HDB Procedure

hana procedure

  • Enter the Procedure name and Schema the procedure will reside in.

Figure 2: Procedure Name and Schema

Hana Proc pic2

  • Enter the Procedure logic. Figure 3 shows a test procedure which determines the beginning period of the QTR based on the year/period entered in the input parameter.  Note the procedure is not production ready, it should be setup with logic to catch incorrect input values and no input values.

Figure 3:  PR_QTR_BEG_PERIOD Procedure

Hana proc pic3

  • Save Procedure.
  • Test Procedure by right clicking within the procedure body and selecting “Invoke Procedure”
  • Enter Input Year/Period and Execute.

Figure 4: Test Procedure

Hana Proc pic4

  1. Create Generic Input Parameter with User Input with view you desire the QTD restricted Key figure.
  • Open the view within HANA Studio you would like to update with the QTD Metric.
  • Create a generic input Parameter within the view which accepts a Year/Period value. “ZFISCPER” is a generic test column in the test view formatted as “YYYYMMM”.

Figure 5. Generic Input Parameter

Hana Proc pic5

  1. Create Procedure based Input parameter
  • Create new Input Parameter with Parameter Type “Derived From Procedure/Scalar Function”

Figure 6:  Procedure Based Input Parameter

Hana proc pic6

  • Click “Manage Mappings”, this is key action which maps the value of the generic input parameter value to the input parameter to the Input parameter of the procedure based input parameter.

Figure 7: Map input parameter

Hana proc pic7

  1. Create Restricted Key Figure based with filters based on Input Parameters
  • Within the HANA view open the node under Semantics which will have the option to create “Restricted Columns” available.
  • Create a new restricted key figure and enter the desired Name and Label and the Key Figure column which the Restricted Key figure should be based on.
  • Under “Restrictions” select the column which will be filtered, in this case “ZFISCPER”.
  • Select the “Between” Operator.
  • Select the Procedure based input parameter as the “From Value” and the generic input parameter as the “To Value”

Figure 8: QTD Restricted key figure

Hana Proc pic8

  1. Review results of QTD Restricted Key Figure
  •  Execute Data Preview to test the Key figure.
  • Enter Year/Period “2016005” when prompted.
  • Pull required objects into Label and Values to view data.

Figure 9: Data Preview Results.

Hana Proc pic9

 

Mike Pink is a SAP Business Intelligence Architect. Over the years, he has helped numerous companies build robust and efficient reporting solutions.

No Comments

Post A Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Become A Partner

If you have a strong SAP background and a
“Whatever it takes” attitude , submit your resume.