Restricted column in SAP HANA
Welcome to the next tutorial in this series where we learn how to work with the concept of Restricted columns in SAP HANA. Just as the name suggests, a restricted column is a user created column that is restricted in it’s output by a condition that we specify.
For example, if we do not want everyone to see all the data in a field but based on a condition we decide whether this data should be displayed or not. This will become much more clear from our use cases below.
Restricted Column – An illustrated Example
Let’s say we want to create a new restricted column called RESTRICTED_NETWR which should show values only when the corresponding value in field AUART is ‘SO’. To achieve this, first right click on the “Restricted Columns” field on the right and select “New” from the context menu as shown below.
1
The below screen pops up demanding technical name and description as always in the “Name” and “Label” column respectively. The column drop down is used to select the numerical field on which this restriction needs to be applied- which in this case is NETWR. In the restrictions section, you can choose to apply a simple restriction using an existing field or if it is a complicated condition, use the Expression editor via the radio button.
In our current scenario, the restriction is simple – AUART = ‘SO’ so we do not need the expression editor.
Fill in these values as shown below. And press Ok.
Save and Activate your view. Now do a data preview to check if this works.
As seen below, the RESTRICTED_NETWR field displays values only where AUART = SO as specified by our restriction.
1
Conditions for restrictions are not always static. This means that there are requirements where conditions are provided to be provided by the user on run-time. Let’s take an example where there would be an input parameter that specifies the value of AUART for which the values of RESTRICTED_NETWR must be displayed.
To achieve this, first let’s create a new Input parameter to capture the AUART value. Right click on “Input Parameters” and click on “New”.
Provide the values as shown below.
Now we have a new input parameter P_AUART ready for capturing values on run-time.
Double click on the RESTRICTED_NETWR field to edit it.
This time, we need to take value of AUART from an input parameter and hence this requires the use of the expression editor.
It asks for your confirmation to move even the existing condition into the expression editor. Press Ok.
We see that the old condition we had is also now converted into an expression code. All we now have to do is to replace ‘SO’ by the input parameter. Delete SO and double click on our P_AUART to add it in.
The expression editor should look like the image below.
Press Ok. Then, save and activate the view. Run the data preview and you would get the below value entry screen. Here I fill out the old input parameters with some values as well as the new P_AUART with value of ‘SO’.
As seen from the output below, our dynamic restriction worked perfectly and RESTRICTED_NETWR only shows values now for the ‘SO’ AUART.
Let’s try again for a different AUART field value. This time we fill the value with ‘TA’ whilst keeping the other values constant.
As seen below, only ‘TA” values now display the restricted column. Hence, our condition was successful.
I hope a restricted column is something easy to create from now on for everyone who read this tutorial.Comment below with your thoughts.
Please show your support for this website by sharing these tutorials on social media by using the share buttons below. Stay tuned for the next ones.
Happy learning!
Thank you It is very helpful to us
Thanks
Chiranjib
Glad to know it helped. Thanks for reading!
Starting from a KPI,can you tell me the exact approach to come to this KPI?How do I know which tables/views need to be used?
I am sorry but I did not quite understand the question. Can you please elaborate?
Hi Shyam,
I have recently started Hana Modeling. If i had to join two table RSEG, BSIK with columns such as:
client, document number, fiscal year, purchasing document etc in RSEG
&
Company code, vendor, transaction type, clearing etc in BSIK. How to join both these tables?
In the basic examples like customer, product, orders, i was able to join them through IDs. but i am confused with these real time tables and their columns and fields.
Please help.
Hi Chandra,
When we design these views and encounter real-time tables, you need some functional expertise to understand relationships and it’s Ok to not know the correct relationship. In such cases, you should reach out to the project’s functional consultants who would tell you how the tables are related. 2 tables are not always related based on the obvious link. There may be filters and fields with dis-similar names to be joined. I’ve never joined RSEG and BSIK but even if you don’t have functional help, you can always google for terms like “RSEG, BSIK(or BSEG)” link.
For example, I got a result https://archive.sap.com/discussions/thread/846782 in which someone has established the relationship already. But when you pick something off the internet, it’s always important to verify it with your functional team. Your business should help define relationships between involved entities.
Hope this clears it up.
Hi Shyam,
Thanks for the response.
So, i had tried some tables joining with language specific column like SPRAS using text join. But, if there Is no common field or column how can we join 2 tables? LEFT OUTER JOIN? OR Somehow to get the report.
2) how to get the purchase orders from the tables? (for examples tables: RBKP, RSEG INVOICE TABLES)
1. The linking fields always may not have the same names. Also, 2 tables may not always link directly. They may need one or more tables in between to link them.
2. EBELN is usually the field with PO numbers.
All of these questions are functional rather than technical. You’d find better answers from the project’s functional consultant.
In Filters also we can restrict the data right ? then what is difference between restricted column and filters ?
Please let me know ?
it’s clear but i have one doubt,can we do nested restricted columns in calculation view
Why would you do that firstly? Please explain an example of such a requirement.
Nice one
Thanks! Please share a tutorial to help us grow.
Hi Shyam,
I have created a restricted column for the measure Gross Amount. I used an attribute Partner ID to restrict. When I see the data preview I can see both “Gross Amount” column and new column “Restricted Gross Amount”. Now in Gross amount column still it displays value for all the records and in “Restricted columns it displays value only for the partner I chose and other partners have ‘?’ as value. My question is still Gross amount is visible in Data Preview of my Calculation View? Then how we are restricting/hiding the values here? Thanks in advance
Namaste. shyamuthaman ji. Great Contribution to the HANA community. Hats-off
Excellent!!! Thanks for sharing such kind of wonderful website for learing SAP HANA…
Hi ,
Well explained in a simpler way . Thanks for sharing such wonderful Blog .
In the above output screen still we could see records with “?” along with restricted ones . Can’t this be avoided and display only the restricted ones . Would this have any impact on performance ?
How to use Existing Restricted columns and calculated columns inside New restricted column at the same node?
I am not able to see my input parameters(of type column )while creating restricted column where as when input parameter is of type direct then input parameters are visible?Any way to see input parameters of type column in Restricted column creation.