SAP WEB calculation view - Dynamic input parameter / variable

[petterhuseby]

Hi

Im working on a Date dimension calculation view in SAP HANA 2.0 Web IDE rev(35) I have create a view that contain all the dates i have in my table. I now need to create some dynamic dates likt YTD, current week and MTD. My Idea was to create a table function that gives me one date line(current date) and use values from the different colum to filter the main tabel, so I only see the dates I want.

For example

Table function contain the following columns

Day_ID YEAR

12.03.2019 2019

Input varialbes

VAR_DAY = 12.03.2019

VAR_Year = 2019

Calulation dimenstion (YTD)

select *

from DIM_DATES

where VAR_DAY < date_id and VAR_year = year.

When I try to use user_input or variables Im prompted to enter a value in web IDE. How can I force a filter based on input_variable or parameters?

Best regard

Petter Huseby

[pfefferf]

Please can you describe more detailed what the issue is? When you have a calc. view with parameters or a table function of course you need to supply the parameters. So what do you mean with "how can I force a filter based on input_variable or parameters?". The general concept of parameters, filter expressions, parameter mapping etc should be clear I think, or not?

[petterhuseby]

Hi Florian, and thanks for your attention. I will try to explain my goal, and maybe you have some suggestion on how to solve it:

I want to create some dimension that represents time(date). I will create a base dimension that has all the dates in my time dimension table. I will create hierarchy and prepare the right labels and so on... When this is done, I will copy the dimension model and apply a filter, so I can create a dynamic date range, for example Year_to_date. To do this I need to apply a filter to my time dimension. This filter will be something like this: day>curret date and year = current year. I like to create some parameter or user_input that apply the filter with dynamic values. I like to apply the filet so it will be applied in the where statement for the sql. I hope this made my goal more clear 🙂

Br

Petter

[pfefferf]

Still not 100% clear to me what you wanna reach in detail. But it sound like you are searching for the APPLY_FILTER function which allows you to evaluate dynamic where conditions on a data source.

[petterhuseby]

Hi Florian

First, thanks for trying to understand my problem. I will try to create a short list of my idea.

1) Create a table with all the dates and the associated attributes, like date,day name, month name...

2) Create a time dimension with the date table created in step 1. as a calculation view.

3) Create a new query that get one row from the table with the current date filter(today) with associated attributes .

4) Create a new time dimenstion calculation view , duplicate the calculation view created in step 2 and apply a filter that contain the column values from step 3

When I do this im propted to enter a filter value, and I just like to create the parameter or user_input with date values, and use the as a filter, without any user prompting.

I hope this was more clear 🙂

[pfefferf]

Wondering why you create your own time dimension table when HANA provides that already. Regarding step 4: For what is the parameter to the for whatever necessary cloned view necessary? Why not restricting that just via a join? Passing rows as input parameters to another calc. view is not possible. Something in that direction is just that you have a table function with a tabular input which receives the rows from step 3 which than can be used in a SQLScript logic to filter your data of step 4.