Automating HFM form updates

We have lots of different forms in our HFM applications.  Many of these are used for reporting and analysis on various aspects of the income statement, balance sheet, and cash flow statements.  We also have a variety of supplemental forms for tax, human resources, and other business purposes.

http://xkcd.com/1566/

For these forms the year on the point of view should be the current fiscal year.  Although this can be adjusted by the users it is more convenient if the default POV is the current year.

We have over 100 such forms.  After we complete year-end close and roll forward to the next fiscal year all of these forms need to be adjusted to update the default POV for the new year.  It is a tedious task to do this by hand.

Strategy

There is a graphical interface for designing and editing forms.  This gives a friendly way of representing the script that is used to define the dimensions, members, and format of the form.  From the editor there is a button on the Actions bar that lets you edit the script directly.

Notice the line that starts with BackgroundPOV=.  This defines the default POV for the form.  So while one option is to use the GUI tools to update the POV, it is often quicker to edit the script and save the update.

Because the form is defined in a script, the script is what gets saved when we do LCM exports.  The relative path is ..\resource\Forms\Web Forms\.  Beneath that are the folders and forms as defined in workspace.  Each form is saved in an .xml file, but the content of the .xml file is primarily the script that we see in workspace.

Our strategy is to export the forms, edit the .xml files to update the year in the BackgroundPOV, then use LCM import the updated forms.

Solution

The key is generating a script to automate the change.  Powershell makes it easy to traverse a folder and subfolders for documents.  In this case the filter is easy because the forms all reside beneath a single parent folder and are all defined in .xml files.

We open each file in turn and look for the BackgroundPOV line.  The POV is defined using the standard HFM scripting style of <dimension>#<value> with periods separating the dimension definitions.  So we search for the string .Y# then use a simple substring to find the year value and replace it with the new value.

The sample script is:

# PoSh script to update POV in the web forms in HFM application with the new year
#    We use the LCM export of the forms for the application.  The exports include the
#    report script in the .xml files.  We search for the BackgroundPOV line and replace
#    the Y# value with the new year.
#
#    I'm assuming that the current year is correct and so we increment the year.
#    We could use a similar techinique and replace the year with a static value
#

# All of the folders that have the web data forms that have to be updated
[array]$Dirs = #Folder 1, `
            #Folder 2, `
               # ...      `
            #Last folder

foreach ($D in $Dirs) {

    # Get all of the .xml files in the current web form folder
    #
    $Files = Get-ChildItem -path $D -filter '*.xml'
 
    foreach ($FN in $Files) {
        $FN

        # Read and loop through the .xml file
        #
        $Script = Get-Content $FN.FullName
        for ($i=0; $i -le $Script.Length; $i++) {

            # Found the line we need to change
            #
            if ($Script[$i] -match 'BackgroundPOV') {
                $L = $Script[$i]

                # Get the 4 digit year.  POV is standard nomencalture so search for .Y#
                #
                $Year = $L.Substring($L.IndexOf('.Y#')+3,4)

                # Increment the year.  Note we convert to integer then back to string
                #
                [string]$New = [int]$Year + 1

                # Replace the year in the script line
                #
                $Script[$i] = $L.Replace($Year,$New)

                # There is only one line per file so exit the for loop
                #
                $i = $Script.length
            }
        }

        # Overwrite the old file with the new
        #
        $Script | out-file $FN.FullName
    }
}

Here are some things to note about the script.  I force the list of folders into an array even if I need just one folder; this gives the script flexibility to use any number of folders.  I hard code the path for the forms but that could be replaced with a prompt or command line parameter.  I assume that the current year in the form is correct and increment the value, but you can replace that with a static string if you need to updated forms with a specific value.  Also, I did not use the -Recurse option in the Get-ChildItem commandlet, but this can be used to traverse all subfolders in the -Path to process all of the .xml files in all directories.  And it is good to have a backup, so before you run the script either do two LCM exports or work from a copy so you can return to a known good state if things go belly up.

I assume you know how to export and import using LCM so I don't describe that process.  My goal is detailing the strategy for doing the mass update and showing the sample script that you can customize for your needs.  The text files generated by LCM lend themselves to scripting techniques like this if you have similar issues with other products.  And while I'm personally a fan of PowerShell for scripted solutions, there are other scripting tools available if your comforts lie elsewhere.