SAP HANA Input Parameter – Derived from Table – Use Expression in Filter Value
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.
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
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
Hi HyunDoo,
Can you please tell me your version of HANA? Also, what happens when you enter currentdate()?
Please provide some screenshots.
Cheers
Angad
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.
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
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
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
Hi Angad,
Situations are same with now() and curdate() as well.
Thanks for your checking and following it up.
Regards
HyunDoo.
Same issue for me. I want to use current user in filter. Any solution ???
Regards
Lavanya
this expression currentdate() doesn't work for me.
any further solution found by anyone?