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

    Error in Final consolidation scripts


      Hi everyone

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

      I wrote the following sample script:




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


        • 1. Re: Error in Final consolidation scripts

          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


                      'Proportionalise this account

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

                  End If


              Set DataUnit = Nothing

          End Sub

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

            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,""






            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),""




            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

              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

                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

                  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




                  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



                  End sub

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

                    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

                      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.



                      2 rule.PNG

                      3 own.PNG

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

                        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

                          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, 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



                          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

                            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

                              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