11 Replies Latest reply on Jan 10, 2019 8:09 AM by 3603385

    Error in Final consolidation scripts

    3603385

      Hi everyone

      Will you please share your knowledge to me…. in my application , HFM 11.1.2.3, consolidation is not getting final consolidated values in entity parent level.

      I wrote the following sample script:

      pic1.png

       

      pic2.png

      After run calculation scripts I didn’t get values in Group level.

      pic3.png

        • 1. Re: Error in Final consolidation scripts
          user6692921

          First of all check that you have consolidation rules turned on in Application Settings because the code you have has a missing Next statement and HFM will produce a VBScript error when it executes.

           

          Your code should look something like this (this does not eliminate ICP balances):

           

          Sub Consolidate()

              Set DataUnit = HS.OpenDataUnit("")

              'percentage consolidation

              dPCon = HS.Node.PCon("")

              dPOwn = HS.Node.POwn("")

              'minority ownership

              dPMin = dPCon - dPOwn

              'Count records

              NumItems = DataUnit.GetNumItems

              'Loop through records

              For i = 0 To NumItems - 1

                  Set DURecord = DataUnit.Item(i)

                  strAccount = DURecord.Account

                  strICP = DURecord.ICP

                  dData = DURecord.Data

           

                  If dData = 0 Then

                      'Skip record

                  Else

                      'Proportionalise this account

                      Call HS.Con("", dPCon, "")

                  End If

              Next

              Set DataUnit = Nothing

          End Sub

          1 person found this helpful
          • 2. Re: Error in Final consolidation scripts
            3603385

            Thanks for your response!!!!!!

             

            ya,  I got 100% consolidation, if i want to divide one account member (1002)data to parent account and minority account based on "manage ownership" module. will u pls help to write the rule, i tried below rule, but i am not getting the values in parent (1004) and minority accounts(1006).

             

             

            if HS.Account.ISConsolidated(VAcc) and VData <> 0 then

            HS.Con "",1,""

             

             

            Else

             

             

            HS.con "S#Actual.Y#2017.W#Periodic.V#<Entity Currency>.I#[ICP None].Custom1#All Custom.BSFlow#Movement",HS.Node.POWN("S#Actual.Y#2017.P#December"),""

             

             

            End if

             

             

            Select Case Rule

             

             

            Case "1002"

             

             

            If Method<>"Equity" Then

             

             

            If Method = "Equity" Then

             

             

            HS.Con "A#1004.S#Actual.Y#2017.W#Periodic.V#<Entity Currency>.I#[ICP None].Custom1#All Custom.BSFlow#Movement",HS.Node.POWN("S#Actual.Y#2017.P#December")*(1),""

            HS.Con "A#1006.S#Actual.Y#2017.W#Periodic.V#<Entity Currency>.I#[ICP None].Custom1#All Custom.BSFlow#Movement",HS.Node.PMIN("S#Actual.Y#2017.P#December")*(1),""

             

             

            Else

            HS.Con "A#1002.S#Actual.Y#2017.W#Periodic.V#<Entity Currency>.I#[ICP None].Custom1#All Custom.BSFlow#Movement",HS.Node.PCON("S#Actual.Y#2017.P#December")*(1),""

                                           

            End If

            End If

             

             

            End sub

            1 person found this helpful
            • 3. Re: Error in Final consolidation scripts
              user6692921

              Something like this will probably point you in the right direction (you will need to review the other custom dimension members):

               

              strMethod = HS.Node.Method("")

               

              If UCase(strMethod) = "EQUITY" Then

                  Select Case strAccount

                  Case "1002"

                      HS.Con "A#1004.BSFlow#Movement.I#[ICP None].V#[Proportion]", dPOwn, ""

                  Case Else

                  End Select

              End If

               

              Judging by the code you have already written you either need to go on a course for writing Rules or get help from your consulting partner.

              1 person found this helpful
              • 4. Re: Error in Final consolidation scripts
                CBarbieri

                You have multiple errors in the HS.Con function. The function reads one record from [Parent Total] and writes to [Proportion], or [Elimination] of the current entity, or [Elimination] of a sibling. There are no other options for the value dimension.

                 

                The View dimension can only be Periodic if the Consolidate YTD option is unchecked for this scenario. In fact, you should not specify View, Value, Scenario, Year, Period, or Entity (unless you are writing to [Elimination] of a sibling entity).

                 

                You also can only write to base members in the Account, ICP, and Custom dimensions. I see you have "All Custom" which sounds like it's a parent member. Please check this.

                 

                I don't believe you can embed the function HS.Node.Pown within the third parameter, but honestly I've never tried this.

                 

                Lastly, it is unnecessary to use "If HS.Account.IsConsolidated..." because the only accounts that are available to Sub Consolidate are those which are flagged IsConsolidated = Y. This is redundant though it is not a source for an error.

                 

                - Chris

                1 person found this helpful
                • 5. Re: Error in Final consolidation scripts
                  3603385

                  Hi Mr. CBarbieri,

                   

                  Thanks for ur response!!!!

                   

                  will you please help me by solving my problem.. Actually am new to HFM...

                   

                  Am not able  to do custom consolidation.... with following script. values are not reflecting at parent level. May i know where i made mistake

                   

                  Script:

                   

                  Sub Consolidate()

                  'Dimension Variables

                   

                  VAcc = "HS.Account.Member("")"

                  VICP = "HS.ICP.Member("")"

                  VCst1 = "HS.BSFlow.Member("")"

                   

                  'Consolidation Variables

                  ''''''''''''''''''''''''

                  Set DataUnit = HS.Opendataunit("")

                  Numitems = VDataUnit.GetNumItems

                  for i = 0 to Numitems-1

                   

                  set DURecord = DataUnit.item(i))

                  VAcc = DURecord.Account

                  VICP = DURecord.ICP

                  VCst1 = DURecord.BSFlow

                  VData = DURecord.Data

                   

                  call &DataUnit.Getitem(i,VAcc,VICP,VCst1,VData)

                   

                          if HS.Account.ISConsolidated(VAcc) and VData <> 0 then

                          HS.Con "",1,""

                         

                          End if

                   

                  Next

                  End sub

                  1 person found this helpful
                  • 6. Re: Error in Final consolidation scripts
                    CBarbieri

                    You are providing too little information here. "Values are not reflecting at parent level" is unclear. Since nearly every dimension has parent members, which dimension are you asking about? Sub Consolidate only considers the Value dimension. It reads data from [Parent Total] and writes to [Proportion] and [Elimination], as I shared earlier. Do you see data in [Proportion] after the rule runs? Your screen shot shows entities with <Entity Currency> but nothing of [Parent Total], [Proportion], or [Elimination] which is what you should be looking at. Focus first on the value dimension, not the Entity dimension. The reason is that Sub Calculate can run in <Entity Currency> in the parent entity, obscuring the results of Sub Consolidate. Therefore, it is unclear where the real issue lies.

                     

                    Here is a presentation that shows you how to debug HFM rules: https://www.slideshare.net/finitsolutions/where-did-that-come-from-techniques-for-debugging-hfm

                     

                    Here are basic custom consolidation rules, along with comments:

                     

                    Sub Consolidate()
                    sParent = HS.Parent.Member 'Get the parent entity of the parent-child being consolidated
                        dPCon = HS.Node.PCon("") 'Get the ownership from the System Account '[PCon]'. By default this is equal to 1 (100%).
                        'Get all of the records from [Parent Total]
                    Set dParentTotalRecords = HS.OpenDataUnit("")
                        iNumItems = dParentTotalRecords.GetNumItems 'Get the number of data points in this data unit.
                    'Cycle through each record, one at a time
                        For i = 0 To iNumItems - 1
                      'Name the fields
                            Call dParentTotalRecords.GetItem(i, sAccount, sICP, sCustom1, sCustom2, sCustom3, sCustom4, dData)
                      Call HS.Con("", dPCon, "") 'Apply the PCon ownership to the data and write the result to '[Proportion]'
                      'If the intercompany member is something other than [ICP None], see if the record should be eliminated
                      If sICP <> "[ICP None]" Then
                       Call Eliminate(sParent, sAccount, sICP, sCustom1, sCustom2, sCustom3, sCustom4, nData, dPCon)
                      End If 'sICP
                        Next 'i
                    End Sub 'Consolidate

                    Sub Eliminate(sParent, sAccount, sICP, sCustom1, sCustom2, sCustom3, sCustom4, dData, dPCon)
                    'Is my intercompany partner a descendant of my immediate parent?
                        If HS.Entity.IsDescendant(sParent, sICP) Then
                            'Is a plug account provided?
                      If HS.Account.PlugAcct(sAccount) <> "" Then
                       'Eliminate this
                       Call HS.Con("V#[Elimination]", -1 * dPCon, "") 'Multiply the amount by -1, and write that to '[Elimination]'
                       Call HS.Con("V#[Elimination].A#" & sPlug, dPCon, "") 'Write the amount to the plug account, to '[Elimination]'
                      End If 'Plug account
                    End If 'first common parent entity
                    End Sub 'Eliminate sub

                    1 person found this helpful
                    • 7. Re: Error in Final consolidation scripts
                      3603385

                      Hi Mr. CBarbieri

                       

                      Thanks a lot for your response!!

                       

                      Please do not think am taking over advantage.. it is too need to me

                       

                      No am not able to see the data in [Proportion] and i pasted your rule in my calculation script but the values are not reflecting to [proportion].

                      and the following are the images which i tried.

                       

                      1.PNG

                      2 rule.PNG

                      3 own.PNG

                      1 person found this helpful
                      • 8. Re: Error in Final consolidation scripts
                        CBarbieri

                        From your grid it appears that nothing is being written to proportion (or, after it's written by Sub Consolidate, something in Sub Calculate clears it out, which is always a possibility). Assuming you do not have anything in Sub Calculate to clear out the amount, it seems that Sub Consolidate is not running - at least not for this account.

                         

                        Is the account IsConsolidated = Y? If not, this could be the cause.

                         

                        Is your application set to use OrgByPeriod? If so, what is the Default Value for Active?

                         

                        Try changing the application settings ConsolidationRules = N, and re-consolidate "all with data". If the data then appears in [Proportion], we are certain the issue lies with the rules.

                         

                        Extract the rules from the application to confirm they are indeed loaded.

                         

                        I just realized too: are you using script objects in Calc Manager? If so, this is unnecessary. In my opinion, you would be better off not using calc manager if all your rules for HFM are in script mode. I suggest either using classic HFM rules, or using graphical objects in calc manager. Since Calc Manager can introduce problems (too numerous to list here), you should avoid it unless you can benefit from graphical mode. It is much easier to update and troubleshoot rules in classic HFM rules mode. As you saw in my eample, be sure to provide extensive commentary too, so you and others can fully understand how and why the rules are written the way they are.

                         

                        Since you are using Calc Manager, please confirm that you have deployed the rules to HFM without error. Again, you can confirm this by extracting the rules from inside HFM (extract application elements), and view the resulting rules file in a text editor.

                        1 person found this helpful
                        • 9. Re: Error in Final consolidation scripts
                          3603385

                          Ya Mr. Barbieri, i set the account members IsConsolidated = Y, i enabled OrgByPeriod and the default value for Active is 1 and as you suggested i changed ConsolidationRules = N in Application settings and i re-consolidate "all with data", then am getting values in [Proportion].

                           

                          Yes am using scripts in Calc Manager, actually am using HFM 11.1.2.3, and i don't have HFM rule editor, so am writing in calc manager. After deploying the scripts am not getting any errors. But when i extract the rule (extract application elements) i did not get any rule in text editor.

                           

                          Again i loaded your rules with .rle extension, then i am getting errors in log like below

                          Capture.PNG

                           

                          And one thing i want to tell you i did not write any sub calculate rules.

                          1 person found this helpful
                          • 10. Re: Error in Final consolidation scripts
                            CBarbieri

                            You can use any text editor to create and modify classic HFM rules. Most people use NotePad++.

                             

                            When you load rules, or deploy from calc manager, you can ignore messages that begin with "Information: validation was not performed". These messages arise for any rule that either uses a variable, which many do, or a vbscript line continuation, which is less common. The HFM syntax checker can't handle line continuations or variables, so it skips these lines.

                            1 person found this helpful
                            • 11. Re: Error in Final consolidation scripts
                              3603385

                              Thank you Mr. Barbieri and User6692921

                               

                              Its working!!!!!!!

                              Many many thanks to both of you.... Based on your suggestions 100 % consolidation is performing now through your rules