Get Involved. Join the Conversation.


    Jash Joshi
    Has anyone used @CalcMgrExcelSUMPRODUCT in business rule or...
    Topic posted August 1, 2019 by Jash JoshiRed Ribbon: 250+ Points, tagged Financial Planning, Help Center, PBCS, Planning, Projects, Public Sector, Reports, SmartView, Strategic Modeling, Tip, Workforce 
    79 Views, 6 Comments
    Has anyone used @CalcMgrExcelSUMPRODUCT in business rule or member formula?

    We have a requirement to calculate the sum product in the formula and it should be dynamic based on the month selected by user.

    Following is the syntax documented by Oracle:



    Returns the sum of the products of corresponding array components


    Java Class: com.hyperion.calcmgr.common.excel.cdf.ExcelMathFunctions.SUMPRODUCT(double[],double[])

    CDF Spec: @CalcMgrExcelSUMPRODUCT(values1, values2)

    I have applied the same in the member formula as below:


    Currently I am trying to get output using the static formula but this is not giving any result in the system.

    Any help on this will be great.

    Thank You.


    Jash Joshi



    • Mark Rinaldi

      In thinking mode... i.e. asking smarter people than me for help!

    • David Ambler



      Yes have used, modifying it a bit to your logic would read...

      @CalcMgrExcelSUMPRODUCT(@LIST(@DESCENDANTS(YearTotal)->AKPI_DayMonth"), @LIST(@DESCENDANTS(YearTotal)->AKPI_TechAvailPctPower))



      • Peter Nitschke

        Wait a minute here (and hey Dave!)

        Does that let us calculate weighted averages and driver costs dynamically? Interesting....could be an interesting outcome for a fully hybrid essbase cube...


        • David Ambler

          Hi Pete


          I haven't used it in that manner, but you should be able to... as an example I use it to calculate the income for months based on a generic profile, so if you look at the example Jan->Income is the result of the sumproduct of (Mth1:Mth6)->CC_NA->Base->Profile and (Mth1:Mth6)->Transactions;

          ie for Jan, 140 = 3*20 + 2*40, Feb, 200 = 1*10+3*30+2*50






            Profile Transactions Transactions Transactions Income Income Income
            2019/2020 2019/2020 2019/2020 2019/2020 2019/2020 2019/2020 2019/2020
            Base Jan Feb Mar Jan Feb Mar
          Total         140 200 260
           Mth1 10   1        
           Mth2 20 3   1      
           Mth3 30   3        
           Mth4 40 2   3      
           Mth5 50   2        
           Mth6 60     2      

          BTW well done on Best Speaker at KScope

          • Peter Nitschke

            Thanks Dave!

            Yeah - that's interesting. It feels like you could create a truly dynamic driver tree recalculating the weighted average (not flat) as it rolled up 

            Would work in a Hybrid cube, without needing to tweak the solve order for the other dimensions to consolidate later.

            Something to have a play with sometime. 




          • HL1

            Hey Brains Trust :), I am trying to get the correct number in the red highlighted cell below (132). In Excel l get this with =SUMPRODUCT(D4:D11,G4:G11) but I can't find a way to get the second dimension (Employee Type) into the formula below, any suggestions please?

            "FTE x Rate" = @CalcMgrExcelSUMPRODUCT(@LIST(@DESCENDANTS("All Jobs")->"Employee Type"->"FTE"),@LIST(@DESCENDANTS("All Jobs")->"Employee Type"->"Base_Rate"->"BegBalance"));

                Assumptions Assumptions Assumptions

                Pay Frequency Base Rate ($) Shift Loading (%) FTE x Rate FTE
            Manager - Day Permanent Weekly 53 #Missing #Missing 2
            Supervisor - Day Permanent Fortnightly 26 #Missing #Missing 1
            Operator - Day Permanent Weekly 26 #Missing #Missing #Missing
            Operator - Day Casual Weekly 36 #Missing #Missing #Missing
            Maintenance - Day Permanent Monthly 40 #Missing #Missing #Missing
            Apprentice - Day Permanent Weekly 32 #Missing #Missing #Missing
            Operator - Afternoon Permanent Weekly 53 15% #Missing #Missing
            Supervisor - Night Permanent Fortnightly 26 30% #Missing #Missing
            All Jobs Employee Type #Missing 292 #Missing #Missing 3
            No Job No Employee Type #Missing #Missing #Missing   132 #Missing