Skip to Content
Author's profile photo Rich Heilman

Using Dynamic Filters in SAP HANA

With SAP HANA 1.0 SPS6(Rev 60), we can now leverage the concept of dynamic filters.   There have been several requests for this type of functionality, since SAP does not recommend the use of dynamic SQL(EXEC statement) when developing SQLScript procedures.  We now have a new statement in SQLScript called APPLY_FILTER.  This statement accepts two parameters.  The first parameter is the dataset in which you want to apply the filter.  This dataset can be a database table, database view, HANA attribute or calculation view, or even an intermediate table variable.  The second parameter is of course the filter condition itself. This would be very similar syntax that you would use in the WHERE clause of a SELECT statement.   In the following example, I have a SQLScript procedure which simply reads data from the “Products” table and applies a filter which is passed as an input parameter to the procedure.  The result set then shows the filtered dataset.

CREATE PROCEDURE get_products_by_filter(

            IN im_filter_string VARCHAR(5000),

            out ex_products “SAP_HANA_EPM_DEMO”.“sap.hana.democontent.epm.data::products” )

  LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER

  READS SQL DATA AS

BEGIN

ex_products =

APPLY_FILTER(“SAP_HANA_EPM_DEMO”.“sap.hana.democontent.epm.data::products”,

                   :im_filter_string) ;

END;

/wp-content/uploads/2013/07/ppic1_238497.png

Assigned Tags

      35 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Kumar Mayuresh
      Kumar Mayuresh

      Hi Rich

      Thanks for putting up an example and explaining dynamic filters on SPS 06.

      Regards

      Kumar.

      Author's profile photo Nagaraj G
      Nagaraj G

      Hi Rich,

      Thanks for your information.

      Can you please explain what is

      SAP_HANA_EPM_DEMO"."sap.hana.democontent.epm.data::products"

      Regards,

      Nagaraj

      Author's profile photo Rich Heilman
      Rich Heilman
      Blog Post Author

      Sure that is the name of a database table.  "SAP_HANA_EPM_DEMO" is the schema. "sap.hana.democontent.epm.data" is the package in which the table is stored in the repository.  "products" is the name of the table.  This table was created using a .hdbtable file in the repository.

      Cheers,

      Rich Heilman

      Author's profile photo Nagaraj G
      Nagaraj G

      Thanks Rich

      Author's profile photo Former Member
      Former Member

      Hi Rich

        Thanks a lot for sharing on this new feature.

         Can we use the APPLY_FILTER feature as part of Scripted Calculation views also.Or it is only limited to Procedures.

      Regards

      Santosh

      Author's profile photo Rich Heilman
      Rich Heilman
      Blog Post Author

      I would think that this syntax would work in a calc view as well, but I have not tried it.

      Cheers,

      Rich Heilman

      Author's profile photo Rama Shankar
      Rama Shankar

      Thanks Rich!

      Author's profile photo Gairik Acharya
      Gairik Acharya

      Thanks Rich. This will help us to pass select-options values and to use them in the sqlscript as a filter .

      A quick question : We are using ERP on HANA ( ECC6.06 , SP00 ) . Is it possible to upgrade our HANADB to SPS06 without upgrading the ERP to SPS02/03 ?

      Author's profile photo Kamal Mehta
      Kamal Mehta

      Thanks Rich.

      Regards

      Kamal

      Author's profile photo Former Member
      Former Member

      Hi Rich,

      thanks for your sharing, but i have one confused question on that new feature.

      as you know, in version previous SP06, hana donot recommend to use dynamic SQL statement for the consideration of performence. if you use "APPLY_FILTER" statement, we actually use dynamic SQL statement. what is the performence difference between previous dynamic sql expression (i mean "EXEC ..." statement) and this new feature.

      thanks

      Author's profile photo Rich Heilman
      Rich Heilman
      Blog Post Author

      APPLY_FILTER statement does not use dynamic SQL and no association with EXEC statement, so there should be no related performance issues when using APPLY_FILTER.

      Cheers,

      Rich Heilman

      Author's profile photo Jonathan Haun
      Jonathan Haun

      I have what seams like a simple queston. If I wanted to add a dynamic filter to a projection in a calculation view that was int(leftstr("BUDAT",4)) <= int(leftstr( now(), 4)) why would I get a type conversion error when activating the calculation view. BUDAT is a varchar(8) column in my analytic view. If I create a calculated column using the syntax from either side of the operator it parses. int(leftstr("BUDAT",4)) returns the numeric year and int(leftstr( now(), 4)) returns the current year.

      All I am trying to do is restrict the data so that only records from the current year (or less) are included. I also want it to be dynamic so I don't have to edit the calc view every year. There is budget and forcat data in the AV for future dates, but I don't want it in this calc view.

      Author's profile photo Former Member
      Former Member

      Hi Rich,

      Thanks for a very good example. I have refined it further and used in HANA Calc view with pasing a dynamic variable. 

      Author's profile photo Former Member
      Former Member

      Hi Rich,

      Thank you for your sharing new feature, I have 2 questions for this feature.

      Q1, The filter dataset cannot be set analytic view, right? "The first parameter is the dataset in which you want to apply the filter.  This dataset can be a database table, database view, HANA attribute or calculation view, or even an intermediate table variable."

      Q2, whether we can use this feature at anywhere not only procedure? such as XS engine, BO tools and so on.

      Thanks.

      Regards,

      Jerry

      Author's profile photo Rich Heilman
      Rich Heilman
      Blog Post Author

      1) Yes, I think that is why I specifically left out analytic view from that list. 

      2) This is a SQLScript feature, so you can only use it within a SQLscript procedure. Of course you can call a SQLScript procedure(which is using this feature) from for example an xsjs service.

      Cheers,

      Rich

      Author's profile photo shishupalreddy ramreddy
      shishupalreddy ramreddy

      Hi Rich,

      Very use full information which I have been trying as part of the requirement . As this feature is made available from SPS 6 , waiting for the upgrade to SPS 6 to explore more on this front.

      Thank you Very Much ......

      Cheers,

      Shishupal.

      Author's profile photo Former Member
      Former Member

      Hi Rich

        How do we apply filter for more than 1 field ?

      Author's profile photo Rich Heilman
      Rich Heilman
      Blog Post Author

      You are simply providing a dynamic WHERE clause, so basically you put those additional fields into your filter string, separated by AND or OR.

      Cheers,

      Rich

      Author's profile photo Nitish Puttur
      Nitish Puttur

      Hi Rich,

      Can this select be more dynamic? Like we able to specify the table name at run time?

      Author's profile photo Siva rama Krishna Pabbraju
      Siva rama Krishna Pabbraju

      Hi Rich,

      This statement accepts two parameters...................................The second parameter is of course the filter condition itself.

                 I tried a lot understand your example but unable to grasp anything. I have read your above statements plenty of times and could able to figure out the hidden secret and could achieve what I want.  Thanks Rich.

      🙂

      Thanks and regards,

      Siva.

      Author's profile photo Hyuk Joo Lee
      Hyuk Joo Lee

      Hi Rich

      Thank you for your beautiful post~.

      I'm struggling with 'SELECT-OPTION" in ADMP side, and your post is very helpful to me.

      The first parameter is for DataSet and it seems that only single table (in case of table..) is possible for the 1st parameter...

      What if more than 2 table with joins... are used for the 1st parameter..?

      Is it possible..? (I think I can make it work if I create an attribute view... but only tables are available.. unfortunately..)

      Do you have any guideline for this case...?

      Thank you in advance,

      Regards,

        Hyukjoo

      Author's profile photo Former Member
      Former Member

      Hi Rich, thanks for the article. If I may one question.

      I need to create dynamic filtering scenario where depending on the user ID that accesses the Calculation view ( from SAP Design Studio) I need to bring for him only certain set of values for lets say "Cost Centre Groupings" ( not totally restricting like in an analytical privileges, but just default filtering)

      I have a mapping table with user  ID to Cost Centre Groupings relationship. The issue is how to pass the current user/ session user parameter in the filter value section of the Input Parameter that I have created  in my Calculation View?

      I know that for example statement "currentdate()" in the  filter section of the input parameter filter value, will give me the current date and I can filter on that, but is there a statement to get  the value of the current user ?

      Is there any reference guide where all these statements accepted by SAP HANA could be found ?

      And the second question is is there any way where procedures could be linked to the input parameters in SAP HANA?

      Is there any way we could write SQL logic somewhere within input parameter so we can deal with the complex scenarios?

      Author's profile photo Rich Heilman
      Rich Heilman
      Blog Post Author
      Author's profile photo Former Member
      Former Member

      Hi Rich, I tried it but it doesn't work the same way as now()

      Any other suggestion would be appreciated

      Author's profile photo Former Member
      Former Member

      Hi Rich,

      One question, considering the following statements:

      VAR_WHERE := '  DOCNUM = ''000000001'' ';

      VAR_A = SELECT * FROM VIEW;
      VAR_FILTERED = APPLY_FILTER(:VAR_A,:VAR_WHERE);

      How does HANA will work on this case ? It will fetch all register from VIEW and then apply the filter over the internal variable, or it will apply the filter directly on the select statement ?

      Thanks !!

      Author's profile photo Rich Heilman
      Rich Heilman
      Blog Post Author

      In the end, the statements will be inlined and executed as...

      SELECT * FROM ( SELECT * FROM VIEW ) WHERE DOCNUM = ‘000000001’



      Cheers,

      RIch



      Author's profile photo Suhas Karnik
      Suhas Karnik

      This solution may not have performance problems, but I would imagine the security concern would still be the same as with dynamic SQL. For instance, I could make this procedure return all records by concatenating an ' Or 1=1' snippet at the end of the parameter.


      This can be risky if the parameter would be populated by a front-end application such as javascript or even XSJS based on user input because it essentially outsources the task of preventing injection to the front end.

      Author's profile photo Rich Heilman
      Rich Heilman
      Blog Post Author

      Yes, because we are passing the entire filter string as a parameter, you could just add anything to that string.  But in other scenarios, we actually do provide a level of protection against SQL injection when using APPLY_FILTER.

      Cheers,

      Rich

      Author's profile photo Former Member
      Former Member

      Very nice ! Thanks !

      Author's profile photo Robert Shandley
      Robert Shandley

      Thanks for that explanation Rich. I was looking for a way to use apply_filter on a parameterized CDS view, which doesn't work. So I had to go about it the way Lucas wrote above and I had the same concerns about wasting resources.

      Author's profile photo Former Member
      Former Member

      Hi Rich,

          Thanks for the great post.
      Would like to know the internal implementation of APPLY_FILTER (including time complexity).

      Can you Pl. suggest any resources for the same.

      Regards,

      Alekhya

      Author's profile photo Former Member
      Former Member

      Good article

      Author's profile photo Former Member
      Former Member

      Its a good article.

      Author's profile photo Naresh Gadamsetti
      Naresh Gadamsetti

      Hi Rich,

      From your example , if I use calc view instead of table and if the calc view takes mandatory input parameters, how can we pass those input parameters when using the apply_filter as I believe this apply_filter takes only 2 params.

      ex_products =

      APPLY_FILTER(“SAP_HANA_EPM_DEMO”.“sap.hana.democontent.epm.data::products”,

      :im_filter_string) ;

       

      Can you pl. clarify

       

      Thanks,

      Author's profile photo Former Member
      Former Member

      APPLY_FILTER performance issue.

       

      Hi Rich, I came to a scenario which is realy performance critical and APPLY_FILTER seem to work not as wished in the query plan:

      I have to build a completely dynamic filter on a huge data set provided by a calc view. Therefore I use a scripted calc view. A user can select different combinations of selections through sapui5, which is captured and persisted in HANA - I can access these dynamic combination through a view.

      My first approach was to join calc view with my selection view. The query has a bad performance, as you can immagine.

      When I could use a dynamic projection, the performance would dramaticaly increase.

      The problem with APPLY_FILTER is, that it behaves even worse than the join criteria.

      The only solution left, to build a dynamic SQL statement would be EXECUTE IMMEDIATE, which needs to persist the resault set, which is not possible in the read only scripted calc view.

      Any idea?

      Thx a lot!

      Alex