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

[petterhuseby · Accepted solution]

After some testing I found a good solution:

I have imported my time/date dimension in a table. The build in HANA time/date dimension is in my eyes not good. It is only numbers representing month, weeks and days. I like to have day names and so on.

I have create a Table function that returns that dates i want. And I create multiple calculation views that use the table functions I have created.

Here is an example to get DATE_YTD

FUNCTION "test_db.db::DATE_YTD"( )

RETURNS TABLE ("DAY_ID" NVARCHAR(10), "YEAR_ID" NVARCHAR(4) )

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER AS

BEGIN RETURN

SELECT "DAY_ID","YEAR_ID" FROM "test_db.db::dates.Entity_LOAD_DATE" WHERE DAY_ID <= current_date AND YEAR_ID = TO_NVARCHAR(CURRENT_DATE,'YYYY');

END;

[petterhuseby · Accepted solution]

Here is the complete Table function with all my columns:

FUNCTION "test_db.db::DATE_YTD"( ) RETURNS TABLE ("DAY_ID" DATE, "UNIX_TIME" NVARCHAR(10), "DAY_CAPTION" NVARCHAR(10), "DAY_OF_CAL_YEAR" NVARCHAR(3), "DAY_OF_CAL_WEEK" NVARCHAR(1), "DAY_OF_WEEK" NVARCHAR(1), "DAY_NAME_ENG" NVARCHAR(9), "DAY_NAME_SHORT_ENG" NVARCHAR(3), "DAY_NAME_NOR" NVARCHAR(7), "DAY_NAME_SHORT_NO" NVARCHAR(3), "DAY_FIRST_DAY_WEEK" DATE, "DAY_LAST_DAY_WEEK" DATE, "DAY_JULIAN_DATE" NVARCHAR(7), "WORKDAY_NOR" NVARCHAR(1), "WORKDAY_ENG" NVARCHAR(1), "WORKDAY_FREE_1"NVARCHAR(1), "WORKDAY_FREE_2"NVARCHAR(1), "DAY_TYPE_NOR" NVARCHAR(20), "DAY_COMPARABLE_LASTYEAR" DATE, "WEEK_ID" NVARCHAR(6), "WEEK_CAPTION" NVARCHAR(8), "WEEK_NUM_ISO" NVARCHAR(3), "WEEK_NUM" NVARCHAR(2), "MONTH_ID" NVARCHAR(6), "MONTH_CAPTION" NVARCHAR(9), "DAY_OF_CAL_MONTH" NVARCHAR(2), "MONT_OF_YEAR" INTEGER, "MONTH_OF_QUARTER" NVARCHAR(1), "CALENDAR_MONTH_NAME" NVARCHAR(9), "MONTH_NAME_SHORT_NO" NVARCHAR(4), "MONTH_DAYS_IN_MONTH" NVARCHAR(2), "MONTH_START_DATE" DATE, "MONTH_END_DATE" DATE, "QUARTER_ID" NVARCHAR(5), "QUARTER_CAPTION" NVARCHAR(7), "DAY_OF_CAL_QUARTER" NVARCHAR(2), "QUARTER_NUM" NVARCHAR(1), "QUARTER_START_DATE" DATE, "QUARTER_END_DATE" DATE, "QUARTER_DESCRIPTION" NVARCHAR(14), "QUARTER_SHORT_NAME" NVARCHAR(2), "QUARTER_TIME_SPAN" NVARCHAR(2), "YEAR_ID" NVARCHAR(4), "YEAR_CAPTION" NVARCHAR(4), "YEAR_START_DATE" DATE, "YEAR_END_DATE" DATE, "YEAR_TIME_SPAN"NVARCHAR(10) ) LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS BEGIN RETURN SELECT "DAY_ID", "UNIX_TIME", "DAY_CAPTION", "DAY_OF_CAL_YEAR", "DAY_OF_CAL_WEEK", "DAY_OF_WEEK", "DAY_NAME_ENG", "DAY_NAME_SHORT_ENG", "DAY_NAME_NOR", "DAY_NAME_SHORT_NO", "DAY_FIRST_DAY_WEEK", "DAY_LAST_DAY_WEEK", "DAY_JULIAN_DATE", "WORKDAY_NOR", "WORKDAY_ENG", "WORKDAY_FREE_1", "WORKDAY_FREE_2", "DAY_TYPE_NOR", "DAY_COMPARABLE_LASTYEAR", "WEEK_ID", "WEEK_CAPTION" , "WEEK_NUM_ISO" , "WEEK_NUM", "MONTH_ID", "MONTH_CAPTION", "DAY_OF_CAL_MONTH", "MONT_OF_YEAR", "MONTH_OF_QUARTER", "CALENDAR_MONTH_NAME", "MONTH_NAME_SHORT_NO", "MONTH_DAYS_IN_MONTH", "MONTH_START_DATE", "MONTH_END_DATE", "QUARTER_ID", "QUARTER_CAPTION", "DAY_OF_CAL_QUARTER", "QUARTER_NUM", "QUARTER_START_DATE", "QUARTER_END_DATE", "QUARTER_DESCRIPTION", "QUARTER_SHORT_NAME", "QUARTER_TIME_SPAN", "YEAR_ID", "YEAR_CAPTION", "YEAR_START_DATE", "YEAR_END_DATE", "YEAR_TIME_SPAN" FROM "test_db.db::test.Entity_LOAD_DATE" WHERE DAY_ID <= current_date AND YEAR_ID = TO_NVARCHAR(CURRENT_DATE,'YYYY'); END;

[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.