Read Hyperion Planning webform XML with VBScript

280 views Asked by At

I exported all the webforms from our Hyperion Planning system and I'm trying to build a database, listing the key details of each webform. The webform is exported into XML, that looks like this:

<?xml version="1.0" encoding="UTF-8" ?>
<form xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" name="6.4 AC Planning" dir="Forms/PnL Inputs" enableOffline="false" planType="2" hide="false" enableCubeView="false" readOnly="false" >
<instruction>Use this form to view detailed account information, and input base level adjustments.</instruction>
<query>
    <columns width="100" >
        <segment>
            <dimension displayName="true" displayAlias="false" displayMemberFormula="false" displayConsolidationOperators="false" name="Period" hide="false" expand="true" >
                <function include="true" name="Descendants" >
                    <member name="YearTotal" />
                </function>
            </dimension>
        </segment>
    </columns>
    <rows height="22" >
        <segment readOnly="true" >
            <dimension displayName="false" displayAlias="true" displayMemberFormula="false" displayConsolidationOperators="false" name="Account" hide="false" expand="true" >
                <member name="Advertising - % of Net Revenue SP" />
            </dimension>
        </segment>
        <segment showSeparator="true" >
            <dimension displayName="false" displayAlias="true" displayMemberFormula="false" displayConsolidationOperators="false" name="Account" hide="false" expand="true" >
                <member name="PL214040" />
                <member name="PL214096" />
                <member name="PL214011_01" />
                <member name="PL214011_03" />
                <member name="PL214011_02" />
            </dimension>
        </segment>
        <segment>
            <dimension displayName="false" displayAlias="true" displayMemberFormula="false" displayConsolidationOperators="false" name="Account" hide="false" expand="true" >
                <member name="PL214021_01" />
                <member name="PL214021_03" />
                <member name="PL214021_02" />
                <member name="PL214020" />
            </dimension>
        </segment>
    </rows>
    <pages>
        <segment>
            <dimension displayName="false" displayAlias="true" displayMemberFormula="false" displayConsolidationOperators="false" name="Entity" hide="false" >
                <function include="true" name="Descendants" >
                    <member name="&amp;Root_Entity" />
                </function>
            </dimension>
            <dimension displayName="false" displayAlias="true" displayMemberFormula="false" displayConsolidationOperators="false" name="Product" hide="false" >
                <function include="true" name="Descendants" >
                    <member name="&amp;Root_Product" />
                </function>
            </dimension>
            <dimension displayName="false" displayAlias="true" displayMemberFormula="false" displayConsolidationOperators="false" name="Channel" hide="false" >
                <member name="CH02" />
                <member name="CH03" />
            </dimension>
            <dimension displayName="false" displayAlias="false" displayMemberFormula="false" displayConsolidationOperators="false" name="Scenario" hide="false" >
                <function name="Descendants" >
                    <member name="Planning Scenarios" />
                </function>
            </dimension>
            <dimension displayName="false" displayAlias="false" displayMemberFormula="false" displayConsolidationOperators="false" name="Version" hide="false" >
                <function name="Descendants" >
                    <member name="Planning Versions" />
                </function>
            </dimension>
            <dimension displayName="false" displayAlias="false" displayMemberFormula="false" displayConsolidationOperators="false" name="Year" hide="false" >
                <member name="&amp;CurrFormYear1" />
                <member name="&amp;CurrFormYear2" />
            </dimension>
            <dimension displayName="false" displayAlias="false" displayMemberFormula="false" displayConsolidationOperators="false" name="Currency" hide="false" >
                <member name="LCL" />
                <member name="LCL (000s)" />
            </dimension>
        </segment>
    </pages>
    <pov>
        <dimension displayName="false" displayAlias="false" displayMemberFormula="false" displayConsolidationOperators="false" name="EVA" hide="false" >
            <member name="No_EVA" visible="true" />
        </dimension>
        <dimension displayName="false" displayAlias="false" displayMemberFormula="false" displayConsolidationOperators="false" name="ICP" hide="false" >
            <member name="ZERO_ICP" visible="true" />
        </dimension>
        <dimension displayName="false" displayAlias="false" displayMemberFormula="false" displayConsolidationOperators="false" name="RptLevel" hide="false" >
            <member name="RL0045" visible="true" />
        </dimension>
    </pov>
</query>
<businessRules>
    <businessRule calcType="0" name="AandC_Base" planType="2" runOnSave="true" hidePrompt="true" useMRU="true" />
</businessRules>
<displayOptions>
    <precision useCurrencyPrecision="false" nonCurrencyMax="0" nonCurrencyMin="0" currencyMax="2" percentageMax="2" currencyMin="0" percentageMin="0" />
    <display poundMissing="true" enableAttributeDisplay="false" enableCellAttach="true" enableAdHoc="false" multiCurrency="false" enableMassAllocation="false" accountAnnotation="false" enableGridSpread="true" />
    <printOptions>
        <pageSettings pageSize="A4" orientation="0" font="" fontSize="8" rowHeaderPercentage="35" numDataCols="10" />
        <format supportingDetails="false" applyPrecision="false" repeatHeaders="true" formatData="true" />
        <printDisplay currency="true" shading="true" cellNotes="false" attribute="true" accountAnnotation="true" />
    </printOptions>
    <smartViewOptions enableForSmartSlice="false" enableOfflineUsage="false" />
</displayOptions>
<dataValidationRules>
    <dataValidationRulesOptions>
        <RunValidationsAsLoggedInUser>false</RunValidationsAsLoggedInUser>
                    <ValidateOnlyForPagesWithBlocks>false</ValidateOnlyForPagesWithBlocks>
                    <ValidateOnlyForUsersWithAccessToForm>false</ValidateOnlyForUsersWithAccessToForm>
        </dataValidationRulesOptions>
    </dataValidationRules>
</form>

So I'm looking to build from this something that says:

Dimension | Location | Display name | Display alias | Members
______________________________________________________________________
Account   | Row      | False        | True          | PL214040; PL214096; PL214011_01; etc
Period    | Column   | True         | False         | Descendants(YearTotal)

I'm using vbscript and I tried to solve it with RegEx searches, but while they worked fine for most details, but the member list.

That's when I switched to XMLDOM, but I can't get ahead with it. I researched it here a lot, found a lot of good materials, but I was unable to adopt them to pull out the above.

Keep in mind that some forms will have account in the columns, some in pov, etc. I have a list of possible dimensions, so I know what to look for, but everything else is different from one XML to the next.

Any help would be much appreciated!

a little later: based on some of the comments (thank you!) and some more fidgeting with XMLDOM, I'm finally able to retrieve out the first 3 columns of my desired end result, now I "only" have trouble getting out all the members included with each dimension.

Below is the code, I hope this clarifies further my query and somebody will be able to help with how to collect the members and where used functions for each dimension.

Set xmlDoc=CreateObject("Microsoft.XMLDOM")
xmlDoc.async="false"
xmlDoc.load("c:\Users\maxlamer\Documents\6.4 AC Planning.xml")
arrMyDimensions=Array("Account","Period","Currency/Value","Year","Entity","Scenario","ICP","Channel","Version","RptLevel","EVA","Product")

Set nodes = xmlDoc.selectNodes("//*")    
For i = 0 to nodes.length - 1
    bolFoundOne = False
    m=0
    For n=LBound(arrMyDimensions) to UBound(arrMyDimensions)
        If nodes(i).getAttribute("name")=arrMyDimensions(n) Then 
            bolFoundOne = True
            m=n
        End if
    Next
    If bolFoundOne Then
        If nodes(i).parentNode.nodeName="pov" Then
            WScript.Echo arrMyDimensions(m) & " is in:" & nodes(i).parentNode.nodeName
        Else
            WScript.Echo arrMyDimensions(m) & " is in:" & nodes(i).parentNode.parentNode.nodeName
        End if
        Set pn_attribs = nodes(i).attributes
        strMyTempString = arrMyDimensions(m) & " attributes :"
        For Each pnAttr in pn_attribs
            strMyTempString = strMyTempString & pnAttr.name & " = " & pnAttr.value & vbTab
        Next
        WScript.Echo strMyTempString & vbCrlf
        arrMyDimensions(m)="XX_Located_XX"
    End if
Next

I know the above is rather crude and I still need to build a proper wrapper to make the output nicely formatted, but should give you a good idea of what I'm driving at. Now I just need the last column.

0

There are 0 answers