POSTED : December 15, 2016
BY : Meri Grieve

HANA View Prompts in IDT Derived Table

(by Meri Grieve with Ignacio Lopez and Alejandro Wojtowicz)

Recently, I was on a project that used HANA views as the data source for SAP BusinessObjects 4.0 universes (unx). The HANA views had mandatory input parameters (i.e. prompts) as well as variables which I needed to provide default values for or bypass with a derived table in the Information Design Tool (IDT). Collaborating with the great HANA developers I work with (Nacho and Alejandro), we worked through the proper syntax of the derived table using placeholders.

Initially, we could not change the HANA views to remove all the prompts because the views were used for other reporting that required those input parameters and variables. Yet, we needed to provide a universe without mandatory prompts so that Webi developers had control over the prompts they added to their reports.

We wanted to share this technique, so here it is:

These are the variables and mandatory input parameters in the HANA view as displayed in HANA Studio.

The defaults built into the HANA view are lost in the universe, so placeholders provide us a way to put those back into the SQL.

 

 

For this blog, I’m using the fictitious name of ORDER_QUERY for the HANA view name and using just a few of the fields. But you’ll get the idea. Focus on the FROM clause where parentheses ( ), placeholders, single quotes, and dollar signs ($) are used. Here’s the SQL script for the derived table in the Data Foundation of the universe.

[Note:  We did end up removing one input parameter (P_ExchangeRateDate) from the HANA view, so you’ll see in the WHERE clause that I restricted the ExchangeRateDate to the current date per the requirement.]

 

SELECT “SAPClient”

, “OrderID”

, “DisplayCurrency”

, “ExchangeRateType”

, “SalesOrganization”

, “ResponsibleEmployee”

, “ExchangeRateDate”

, SUM(“Amount”) AS “Amount”

FROM “_SYS_BIC”.”ORDER_QUERY”

(              ‘PLACEHOLDER’ = (‘$$P_SAPClient$$’, ‘100’)

, ‘PLACEHOLDER’ = (‘$$P_DisplayCurrency$$’, ‘USD’)

, ‘PLACEHOLDER’ = (‘$$P_ExchangeRateType$$’,’M’)

, ‘PLACEHOLDER’ = (‘$$V_SalesOrganization$$’, ”)

, ‘PLACEHOLDER’ = (‘$$V_ResponsibleEmployee$$’, ”)

)

WHERE “ExchangeRateDate” = (CURRENT_DATE)

GROUP BY “SAPClient”

, “OrderID”

, “DisplayCurrency”

, “ExchangeRateType”

, “SalesOrganization”

, “ResponsibleEmployee”

, “ExchangeRateDate”

______________

 

Syntax:

(

‘PLACEHOLDER’ = (‘$$X_XXXX$$’, ‘default’)

, ‘PLACEHOLDER’ = (‘$$Y_YYYY$$’, ‘default’)

)

 

Syntax pointers:

  1. Parentheses are used to open and close your placeholder block of statements.
  2. Placeholder keywords use single quotes and all caps.
  3. The syntax of the right side of each placeholder statement uses:
    • Parentheses to open and close the right side of statement
    • Single quotes around the name of input parameter or variable
    • Next use two dollar signs inside the single quotes before and after the input parameter or variable name
    • Then a comma
    • And finally, provide the default values for each placeholder. We bypassed the last two variables by using two single quotes. These aren’t mandatory so we didn’t need to pass a value.

And that’s it.

If you have a similar requirement in SAP BusinessObjects 4.0, give this a try and let me know how it works!