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="&Root_Entity" />
</function>
</dimension>
<dimension displayName="false" displayAlias="true" displayMemberFormula="false" displayConsolidationOperators="false" name="Product" hide="false" >
<function include="true" name="Descendants" >
<member name="&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="&CurrFormYear1" />
<member name="&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.