sum of amounts based on period range in FAGLFLEXT

[Former Member]

Dear Experts,

I have a requirement to add the amounts based on period range selected through selection screen. For examples in the the screen selection user selects Fiscal year , Fiscal Period_From and Period_To as

year ;2015

period_from: 001

period_to  : 008

then logic required is to sum the amounts HSL01 - till HSL08.

if user selects different range like period_from = 004 and period_to= 009 then sum (HSL04 till HSL09)

This logic is required to sum the amounts dynamically as user can select between 000 till 011. Please let me know how can this be done using graphical calculation view

Thanks in advance

pradyumna

[Former Member]

Hi Anil

This solution may not work when I have HSL values like mentioned below. .. Pradyumna//

[Former Member]

Hello Experts

Does anyone have a better solution to achieve this with in graphical view or any SAP HANA VDM which has similar functionality. Thanks//

[Former Member]

Hi Pradyumna,

This can be easily done using graphical calculation view and exposed to front end using xsodata to accept the input parameters required.

Steps followed :

1. Created a sample Data :

create column table test_sum(year integer, fiscal_period NVARCHAR(100), value integer);

insert into test_sum values (2015, 'HSL01', 10);

insert into test_sum values (2015, 'HSL02', 20);

insert into test_sum values (2015, 'HSL03', 30);

insert into test_sum values (2015, 'HSL04', 40);

insert into test_sum values (2015, 'HSL05', 60);

insert into test_sum values (2015, 'HSL06', 90);

insert into test_sum values (2015, 'HSL07', 1

insert into test_sum values (2015, 'HSL08', 90);

insert into test_sum values (2014, 'HSL01', 10);

insert into test_sum values (2014, 'HSL02', 20);

insert into test_sum values (2014, 'HSL03', 10);

insert into test_sum values (2014, 'HSL04', 10);

insert into test_sum values (2014, 'HSL05', 90);

insert into test_sum values (2014, 'HSL06', 10);

insert into test_sum values (2014, 'HSL07', 10);

insert into test_sum values (2014, 'HSL08', 10);

2. Created a graphical calculation view with following steps :

     (1) Added the projection node and dragged the above table into this projection node

     (2) Created a calculated column and 3 input parameters, one for year, one for period start and           one for period end.

     (3) Calculated column would contain

         

     (4) Input parameters would contain

         

     (5) Filter Expression of this projector node would look like , It would use all the calculated           column and input parameters to filter out the required data

         

     (6) Pass this to aggregation node and add value as aggregated column

         

     (7) Validate and Activate the view, When data previewed, pass on the input parameters to get

          get required output.

Expose this view to front end using xsodata service to accept the inputs from front end and provide the required output

-Regards

Anil

[Former Member]

Hi Anil , where did you used the HSL values to get the sum of periods? //Pradyumna

[Former Member]

Hi Pradyumna,

1. If you see that CC_CALC calculated column, converting the NVARCHAR fiscal period HSL values into interger kind.

          Eg: HSL01 is converted to 01, HSL10 is converted to 10 and stored in this calculated column.

2. Now if you see the Filter expression which is :

("YEAR" =$$IP_YEAR$$) and ("CC_CALC" >= '$$IP_P_START$$' and "CC_CALC" <='$$IP_P_END$$')

which solves both year filter coming from input and the HSL values filter coming from other 2 inputs as well and the resultant records from these filter are summed to give the value.

Please let me know if you have any doubts. I have attached more screen shots for your understanding.

-Taking Inputs

-Summed values for the provided year and HSL values.

The HSL values stored in FISCAL_PERIOD column are passed to calculated column to provide values without HSL i.e only integer values and the incoming filter values are used to apply this between condition.

Regards

-Anil

[Former Member]

Anil,

Thankyou. Is VALUE a calculated column? then what is the formula mentioned there?

[Former Member]

Hi,

The value is not a calculated column, if u see I'm loading a sample data for year and fiscal period and value after creating a sample table.

The formula mentioned is a filter expression which filters out the record required to be summed.

for eg: If this is the input : Year = 2015 , period_start = 001 and period end = 005

The formula is a filter expression as shown in the screen shot :

("YEAR" =$$IP_YEAR$$) and ("CC_CALC" >= '$$IP_P_START$$' and "CC_CALC" <='$$IP_P_END$$').

-> First part would filter 2015 records ("YEAR" =$$IP_YEAR$$) from table.

Second part would filter the HSL lines 001 to 005 ("CC_CALC" >= '$$IP_P_START$$' and "CC_CALC" <='$$IP_P_END$$').

-> CC_CALC being calculated column  would contain values such as 1, 2 , 3 of corresponding HSL01, HSL02, HSL03 as it is being rightstr().

-> resulting records are passed to aggregation box which does sum of values and gives the required output.

If you follow the steps mentioned I believe you would arrive at the same solution.

Regards

-Anil

[Former Member]

Hi Pradyumna, Do you use any report tools on HANA Views like business object Webi or Lumira? Webi or lumira aggregate the value automatically based on your input parameter. Thanks, Shakthi Raj Natarajan.

[Former Member]

Hi Shankthi, we are using webi as frontend tool. Does it handle the sum between the periods dynamically as i mentioned in the examples? user may select periods 2 to 8 or 3 to 9 from selection screen / / Thanks pradyumna