- SAP Managed Tags:
This example is referenced in my other post about input parameters. Please have a look at the other post to get a better understanding of the context for this example
The goal is to calculate the sum of amount for all records from beginning of the current year up to a certain date that can be entered at runtime. If no value is provided the current date minus one day will be used. As of this writing current date was 2018-08-03.
This goal will be achieved by using restricted columns. We will explain later, why we use restricted columns this time and not simply another filter.
We will use year("date") = year(current_date) AND "date" < to_date($$IP_1$$) as the restriction.
The first term selects all records of the current year and the second term all records with column date before the entered date. [As a side note: when using the restricted column on a large number of records you probably would like to avoid applying year() at query execution time to the date values for filtering. For performance reasons you could store the year information separately in a column already when loading the table.]
In addition, we will set the default value of IP_1 to add_days(to_date(current_date),-1)
The default value will therefore take the current date and subtract one day from it. Given this default parameter definition IP_1 needs evaluation and should not be treated as a string.
To achieve this behavior:
a) Remove the filter from Example 4
b) Create restricted column RES_1 that uses expression:
year("date") = year(current_date) AND "date" < to_date($$IP_1$$)
c) Change the default value of IP_1 to: add_days(to_date(current_date),-1)
d) Save, build, and preview the Calculation View. If you keep the default value for the input parameter and don't enter an own value you will see the data of this year up till yesterday in column RES_1:
You only see a value in column RES_1 if the expression in the restricted column is fulfilled for this record. If the expression is not fulfilled NULL is listed in column RES_1.
You can also modify the value assigned to the input parameter:
SELECT TOP 1000
"date",
"product",
SUM("productRating") AS "productRating",
SUM("amount") AS "amount",
SUM("RES_1") AS "RES_1"
FROM "INPUTPARAMETERS_HDI_DB_1"."inputParameters.db::example5"
(placeholder."$$IP_1$$"=>'add_days(to_date(current_date),-95)')
GROUP BY "date", "product";
This statement will select all days in 2018 before 2018-04-30 for restricted column RES_1 (assuming 2018-08-03 as the current date):
You can also enter a specific date. However, because in the expression IP_1 is not enclosed in apostrophes (the earlier examples required IP_1 to be evaluated) you need to enclose the value itself in apostrophes to ensure that it is treated as a string:
SELECT TOP 1000
"date",
"product",
SUM("productRating") AS "productRating",
SUM("amount") AS "amount",
SUM("RES_1") AS "RES_1"
FROM "INPUTPARAMETERS_HDI_DB_1"."inputParameters.db::example5"
(placeholder."$$IP_1$$"=>' ''2018-04-01'' ')
GROUP BY "date", "product";
This will show all records before 2018-04-01 but still in year 2018 in restricted column RES_1.
Finally, you probably would like to sum up amount per unique entry in column product according to the filter in the restricted column. To do so you could use the following query:
SELECT
"product",
SUM("amount") AS "amount",
SUM("RES_1") AS "RES_1"
FROM "INPUTPARAMETERS_HDI_DB_1"."inputParameters.db::example5"
(placeholder."$$IP_1$$"=>' ''2018-04-01'' ')
GROUP BY "product";
Column RES_1 displays the summed-up values of records that fulfill the restricted column expression.
In this specific case you could have achieved the same with filters and without restricted columns. The reason why restricted columns are used in this example is that typically you want to have different evaluation periods next to each other in your output. This could be easily achieved with an additional restricted column that filters, e.g., for the values of the previous year up to a year before the input parameter value, e.g.,
year("date") = year(current_date)-1 AND "date" < add_years(to_date($$IP_1$$),-1))
If you added the following records to your table:
insert into "IPExample" values ('2017-08-02','apples' ,1,10);
insert into "IPExample" values ('2017-08-01','apples' ,1,10);
depending on your current date you should only see the second entry with date 2017-08-01 in your sum of your second restricted column LastYearToDatePreviousYear:
If you wanted to achieve this output you could enter the fixed value 2018-08-02 when executing the query.
Click here to navigate back to the context in which this example is discussed. You will also find further examples there.
