Input Parameter with Key and Text in view help ( F4)

This blog would explain how we can use both Key and Text in  view help ( f4 values) of  HANA Calculation View

***Now this functionality has been added to SPS10, if you are at SPS10 or above, you no longer need this work around ***

Scenario :

Users want to filter data by Customer and see Customer Text in view help along with number . As the table has billions of rows, to improve report performance , we want to use Input Parameter in Projection Filter .  Available types of Input Parameter in SPS9 cannot use Key and Text both directly in View Help or F4 values.

Our Data Model is Calculation View .  Input Parameter behaves differently in Calculation View and Analytic View.

Calculation View :  If the value of Input Parameter is blank , model would return no data.

Analytic View : If the value of Input Parameter is blank , model would return all the data.  ( Provided Input Parameter is not Mandatory )

Solution :

Created an Attribute View on my Customer Table with Calculated Attribute . In test case both Customer Number and Text is from same table
You can join different attribute and text table to create the same based on your scenario .

Calculated Attribute KEY_N_TEXT :  Concatenated Key and Text for Customer

Data From Attribute View :

I created a calculation view with Input Parameter type Column  and use the above concatenated attribute in the reference column for view help.

Now we can restrict the data in Projection Filter with Input Parameter . However, this input parameter would give us value of Key and Text together while we want to restrict only by Key ( that is our column value ) .  So, we can take first 6 character of the Calculated Attribute to get the Key.

But if user does not want to choose any customer (  to run for all customer), this model would not result any data as Input Parameter in Calculation View must be provided with data.  To get around that, I used Match function.

So, over all filter expression would be like this :

"CUST" = leftstr (('$$CUST_WITH_TEXT$$'),6)   OR match ( "CUST", leftstr (('$$CUST_WITH_TEXT$$') ,1) ) 

When a customer key and Text is selected, only first 6 character would be passed as filter condition .

I also set default value of the Input Variable as *All Customer* .  So, if user wants to see data for all the customer, Match expression take the first character ( which is * ) and returns all the data .

Test 1 : User choose one customer From view help                            

Out Put: 

Test 2 : User does not choose any Customer

Attribute View for View Help can also be created on your transaction data table to give you list of only those customer, for which you have values in transaction Data.

Source: scn.sap.com