Forum Discussion

Marco's avatar
Marco
Contributor II
3 days ago

How to get the sum of multiple entities in a GetDataCell or in a Calculate

Hi Everyone.

I want to get the sum of multiple entities, like in the example below, but for now I'm only getting errors or values with 0

'First option
Dim strList As String = String.Join(", ", (From l In lstMI Select "" & l.Member.Name & "").ToArray())

Dim lstMI As List(Of MemberInfo) = BRapi.Finance.Members.GetMembersUsingFilter(si, api.Pov.EntityDim.DimPk, "E#Kazakhstan.Base.Where(Text1 = 'M_S_KAZAKHSTAN')", True)

api.Data.GetDataCell("A#411000:E#Root.list([" & strList &"]):C#Local:O#Top:U1#ALL_DEPARTMENTS:U2#INNER:U3#COUNTRY_RPT:U4#ALL_PROJECTS").CellAmount

'Second Option
api.Data.GetDataCell("A#411000:E#Kazakhstan.Base.Where(Text1 = 'M_S_KAZAKHSTAN'):C#Local:O#Top:U1#ALL_DEPARTMENTS:U2#INNER:U3#COUNTRY_RPT:U4#ALL_PROJECTS").CellAmount

What I ultimately want to do is to get those values so I can change the following, so that the entity includes all those base entities that meet the filter, and I can get the correct value and use it in my Calculate.

Dim DBICtry As DataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(A#RETBUYBACK:E#" & strEntTx3 & ":C#Local:O#Top:U1#ALL_DEPARTMENTS:U2#INNER:U3#COUNTRY_RPT:U4#ALL_PROJECTS + A#423100:E#" & strEntTx3 & ":C#Local:O#Top:U1#ALL_DEPARTMENTS:U2#INNER:U3#COUNTRY_RPT:U4#ALL_PROJECTS + A#411000:E#" & strEntTx3 & ":C#Local:O#Top:U1#ALL_DEPARTMENTS:U2#INNER:U3#COUNTRY_RPT:U4#ALL_PROJECTS)")
api.Data.FormulaVariables.SetDataBufferVariable("DBICtry",DBICtry,False)
api.Data.Calculate("A#P_EBIPrct:O#Import:U1#D0000:U2#P0000:U3#LOCAL_DATA:U4#PJ00000 =  Divide($DBICtry, A#411000:E#" & strEntTx3 & ":C#Local:O#Top:U1#ALL_DEPARTMENTS:U2#INNER:U3#COUNTRY_RPT:U4#ALL_PROJECTS)")

Thanks for your help and I appreciate the help.

Cubeview
Finance

4 Replies

  • rhankey's avatar
    rhankey
    Contributor III
    3 days ago

    There is a whole lot going wrong in your first code fragment.  The most glaring of which is that you are attempting to include a member expansion within an intersection.  If you want to use GetDataCell(), you will need to loop through the applicable entities getting the desired value for each, or loop through the entities building a combined string of intersections you desire to be issued in a single GetDataCell().  Perhaps it might be safe in your particular situation, but generally it is not safe to assume all the entities share a common currency - you could be summing up what I refer to as unidollars.  And for entities not sharing desired currency, then you need to figure out how you want to translate it to the desired entity.

    It is not clear whether you are trying to sum the entities before or after the division, or wether the change applies to the numerator, denominator or both, so I won't comment in how you might use a GetDataCells() proof of concept into the existing Calculate().  But be aware that Data Buffers are for a single Data Unit, so performing Calculate() logic on data that spans multiple Data Units requires a bit more, umm, finesse.

    • Marco's avatar
      Marco
      Contributor II
      3 days ago

      So what would you recommend I do to perform that operation in the DataBuffer? I want to calculate a percentage using those values, which is why I'm using a divide, but I haven't found an optimized way to do it.

      • rhankey's avatar
        rhankey
        Contributor III
        3 days ago

        I would loop through the applicable entities, and either

        • perform two GetDataCell()'s/entity, accumulating to a grand total Numerator & Denominator, or
        • get a single filtered DataBuffer/entity, from which you could loop through to accumulate to the grand total Numerator & Denominator.  This way is a few more lines of code, but reads only a single buffer from the cube/entity, and thus a bit more efficient.

        The write back the division of the two grand totals.

        Btw, using C#Local is risky.  Better to use C#[EntityCurrency].  It's been too long since I used C#[EntityCurrency] to recall what C#Local returns if reading a Data Buffer for a different entity than is currently being computed.  Also, if vertical extensibility is being used, and these entities are or could be in different cubes, you will need to specify the Cb# too.