Skip to Content

Recently, I have experienced something while modeling HANA information view that I want to share with everyone.


Tested with HANA Revision – SPS 08 80/82


Case:

Dynamic Filter for projection with current Fiscal Year and Fiscal Period.


Method:

In order to filter it dynamically I decided to use input parameter. There are different parameter types that we can use to do this. I decided to use following “Derived from table” type as shown below:



Challenge:

When filtering column then it displays list of values from the same HANA information view and we can select the date. Now, as the requirement is current Fiscal Year and Period so how do we filter the column to dynamically select current date in “Filter Value” section?


Solution:

Like everyone, I expected it to understand the expression. So I tried following and it worked. I used the expression


currentdate()




I was aware that I could use expression in the “Default Value” section (as explained in SAP HANA Developer Guide) but didn’t expect it to

work in “Filter Value”. I was able to filter the projection using this input parameter. To test the filtering, I removed the input parameter and the projection displayed all the years rather than just current year. Hopefully, when I get an explanation for this then I will share it here. For now, you can try different expressions.

To report this post you need to login first.

10 Comments

You must be Logged on to comment or reply to a post.

  1. Phil Prentiss

    Hi Angad,  thank you for posting your finding!  Unfortunately I have not been able to repeat your success with using an expression in the Filter Value.  Has anyone been able to provide an explanation as to whether or not this is supported functionality?  Have you found any further documentation?

    Regards,

    Phil

    (0) 
  2. HyunDoo Sung

    Hi Angad,

    I’m also having the same question for the solution.

    For the filter column, it is not working with the ‘currentdate()’ but a specific value is working like 20150605 etc..

    Can you please provide a solution if you know.

    Regards

    HyunDoo

    (0) 
    1. Angad Singh Post author

      Hi HyunDoo,

      Can you please tell me your version of HANA? Also, what happens when you enter currentdate()?

      Please provide some screenshots.

      Cheers

      Angad

      (0) 
  3. HyunDoo Sung

    Hi Angad,

    We are SPS9 and Revision94.

    I have created Input parameter delivered from table which is time table.

    If I assign physical date like 20150608 for the filter value then expected year is being used in the filter.

    And if I enter a date which is not in the time table then all data are filtered out. So no data is coming. I think it is reasonable as there is no entry in the time table.

    But if I type “currentdate()” in the Filter value column, filter is not working and all data are coming.

    Regards

    HyunDoo.

    (0) 
    1. Angad Singh Post author

      Hi Hyun,

      May I know two things?

      Which column (and data type) is your return column (see my image) for input parameter?

      Which column is your filter column (and data type) in data model (where you are applying this filter)?

      If you have notice my example, I am using “Fiscal Year” as return column. This means for current date I will be provided with Fiscal Year from table and then this value is used to filter field “FiscalYear” in data model.

      FiscalYear     Revenue

      2012              33212390

      2013              873090203

      2015              1234234892392

      After filter is applied,

      FiscalYear          Revenue

      2015                   1234234892392

      Regards

      Angad

      (0) 
  4. HyunDoo Sung

    Hi Angad,

    The table consist of C_DATE(date format), C_YEAR, C_YYMM and C_MM.

    And  Return column is C_YEAR and Filter column is C_DATE in the Parameter definition.

    In here, when I type a specific date then the C_YEAR is returned correctly but if I type currentdate() in the Filter value column then filter is not working.

    Regards

    HyunDoo

    (0) 
    1. Angad Singh Post author

      HyunDoo,

      I tested with our HANA system which is on same revision as yours. It is taking null value from currentdate() expression. That’s why you see all the records in result (similar behaviour as you do filtering in data preview). This is happening to new model that I created for test.

      However, the model that I created in Rev80 is still working with expression of currentdate() in input parameters.

      Can you please try with now() and see it it helps? I will keep you udpated. I am also going to update blog to have HANA revision.

      Cheers

      Angad

      (0) 

Leave a Reply