I work with Hana 1.x

I have a calculation view with assets, attributes and measures over years (dataset 1) :

and another with different rates over years (dataset 2) :

Requirements :

Here is wanted output :

- Measure 2 has an associated year of value

- Measure 2 has to be multiply by effective rate

- Effective rate is the product of all rates between year of measure2+1 AND year of output :

Knowing I'm then reporting on this with Webi, what is the best way to achieve my requirements ?

Would it be possible to make this calculation directly in the view ?

Would I need to have a new calcultation view with cartesian product between both datasets for the choosen year and then use runningProduct function in webi for the measure 2 and max function on other measures and attributes ?

I tried to build a procedure but I'm stucked :(

the proedure compiles correctly like this :

create procedure effective_rate(IN inval_start integer,inval_end integer,step integer, out val decimal)
-- start is the year of measure to be rated
-- end is the year of output
-- step will always be 1
-- out will give the product of all rates since start+1 to end
language sqlscript
as
begin
declare v_index1 integer;
declare valdec, rate decimal;
v_index1:=inval_start;
valdec:=1;
WHILE :v_index1 <= :inval_end DO
v_index1:=:v_index1+:step; -- first loop = start+1
-- retrieve rate for the year
SELECT 1 FROM "_SYS_BIC"."Analytics.Private.PM/CA_RATES"
WHERE J_1AINDX = 'CA01' and YEAR = v_index1;
-- apply new rates to product of previous rates
valdec := valdec*rate;
END WHILE;
val:=valdec;
end;

BUT I need now to update rate variable with the returned rate of my SELECT statement...

I tried these 2 codes without success (compilation error...) :

-- retrieve rate for the year
SELECT 1 FROM "_SYS_BIC"."Analytics.Private.PM/CA_RATES"
WHERE J_1AINDX = 'CA01' and YEAR = v_index1 := rate;
-- retrieve rate for the year
rate := SELECT 1 FROM "_SYS_BIC"."Analytics.Private.PM/CA_RATES"
WHERE J_1AINDX = 'CA01' and YEAR = v_index1;

any help would be appreciate

## Add comment