This one was fun!!!
Well, we all are familiar with Hyperion Financial Reporting aka. HFR and I bet most of you have used formula rows/columns while creating HFR reports.
Someone was asking how can he check what formulas are there on the report. Now that is a really interesting question.
I recently posted an article on Styling the LCM artifact files, and I was thinking of using same XSLT technique.
Now you might be thinking LCM files are XML files, how can you use XSLT on Financial Reporting. Well if you export HFR Reports and open the .res files in editing tool you’ll find that it is an XML file.
Now when I posted about LCM artifact styling, many people asked me why didn’t you use EXCEL to do it. Well I’ve used EXCEL earlier and I kind of didn’t like the way it presented the table.
Still that is a good option, so I’m going to show you how to do this in EXCEL and then we’ll move to the stylized version of this 😉
You can export HFR reports the regular way. Select the reports for exporting, now if you want to select all reports under a folder, you can just select the folder and export the folder itself.
Once the report is exported, you can open them in EXCEL. When you try to open it, EXCEL will present a warning window. You can click “Yes” (because we know this is a HFR report.)
Once you move past that warning, you’ll get the “Open XML” prompt (now excel understands that it is an XML file, which is named with a .res extension!!!)
Choose to open it as an XML table.
You can click OK for this message and you’ll be presented with an XML table.
That is our HFR report presented as an XML table, pretty neat stuff isn’t it?
Now the ones which we are interested are VALUE columns (now this is where I don’t like EXCEL XML tables)
As you know you can have 3 types of formulas in HFR
- Formula Rows
- Formula Columns
- Cell formulas
Now this one is the row and column formula on the report (Value9)
There is a better option in EXCEL itself, while opening the XML, you are presented with 3 options. We chose the first one above, How about the last one “Use the XML Source Task Pane”
Now if you go this route, you need to know which elements are needed. (which we don’t know in this case)
You’ll be presented with all elements of the XML in a tree structure and you can drag and drop the elements to the worksheet.
I know that the cell level formulas are presented under GRIDCELL and Row and Column ones are under GRIDAXIS
So dragging and dropping the required elements we end up with this sheet.
You can then refresh the resultant XML table
and there you go, all formulas used in that HFR reports are presented to you. It is kind of ugly and needs some clean up. While you are cleaning up this report, let me show you the XSLT trick.
Once again you’ll need to create an XSLT file. You can use the one given below for this purpose.
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<html>
<body>
<h2>
FR Formulas</h2>
<table border="1">
<tr bgcolor="#9acd32">
<th>Axis</th>
<th>Row</th>
<th>Column</th>
<th>ID</th>
<th>Heading</th>
<th>Value</th>
</tr>
<!-- Cell level formula -->
<xsl:for-each select="EXPORT/DESIGN/LAYOUT/GRIDOBJECT/GRIDMATRIX/GRIDCELL/GRIDSEGMENT">
<xsl:if test="DERIVEDEXPRESSION">
<tr>
<td></td>
<td><xsl:value-of select="../@ROW"/></td>
<td><xsl:value-of select="../@COLUMN"/></td>
<td></td>
<td></td>
<td><xsl:value-of select="DERIVEDEXPRESSION/@VALUE" disable-output-escaping="yes" /></td>
</tr>
</xsl:if>
</xsl:for-each>
<!-- Row and column level formula -->
<xsl:for-each select="EXPORT/DESIGN/LAYOUT/GRIDOBJECT/GRIDAXIS/GRIDSEGMENT">
<xsl:if test="DERIVEDEXPRESSION">
<tr>
<td><xsl:value-of select="../@AXISNAME"/></td>
<td></td>
<td></td>
<td><xsl:value-of select="@ID"/></td>
<td><xsl:value-of select="DERIVEDEXPRESSION/@HEADING"/></td>
<td><xsl:value-of select="DERIVEDEXPRESSION/@VALUE" disable-output-escaping="yes" /></td>
</tr>
</xsl:if>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet>
What I’m doing here is to loop through all GRIDSEGMENTs under GRIDCELL to get the cell formulas.
The second loop is for Row and Columns formulas and that is coming from the GRIDSEGMENT under GRIDAXIS
Once I created the file, I went to XML tool plugin of Notepad ++ and asked it to convert the XML file.
Choose the xsl file and Transform.
It’ll create a new html file based on your XSL
You can save it as an html file and use any browser to open it.
Excellent article! Thank you for sharing.
Thumbs Up !!!
Excellent Work as Always !!