Finding the formulas used in HFR reports


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.

 image-1

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.)

image-2

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!!!)

image-3

Choose to open it as an XML table.

image-4

You can click OK for this message and you’ll be presented with an XML table.

image-5

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)

image-6

As you know you can have 3 types of formulas in HFR

  1. Formula Rows
  2. Formula Columns
  3. Cell formulas
image-8
The first one as you see on screen is the Cell formula
image-7

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)

xml-source

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.

xml-source-choosing-elements

You can then refresh the resultant XML table

refresh-xml-source

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.

xsl-tran

 

xsl-setting

Choose the xsl file and Transform.

It’ll create a new html file based on your XSL

new

You can save it as an html file and use any browser to open it.

image-9
There we go, all formulas used in an HFR report presented to you in a neat table format 🙂
The first one which shows a row and column is a cell formula and is in B1.
Advertisements

About Celvin Kattookaran

I’m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase.

Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I’m an Independent Consultant with “Intekgrate Corporation” based out of Aurora office.

I’m from God’s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). I’m core gamer :) and an avid reader.

I was awarded Oracle ACE Director for my contributions towards EPM community.


Leave a Reply

0 thoughts on “Finding the formulas used in HFR reports